Character Codes in VBA

[bkanne]

Can someone please provide some guidance on how to write/insert character codes into VBA text strings?

For context, I am trying to set up a "footnote cycle" for the following number formats:

• #,##0.0 ⁽¹⁾_);(#,##0.0) ⁽¹⁾;"–" ⁽¹⁾_);@ ⁽¹⁾_)
  
• #,##0.0 ⁽²⁾_);(#,##0.0) ⁽²⁾;"–" ⁽²⁾_);@ ⁽²⁾_)
  
• #,##0.0 ⁽³⁾_);(#,##0.0) ⁽³⁾;"–" ⁽³⁾_);@ ⁽³⁾_)
  
• #,##0.0 ⁽⁴⁾_);(#,##0.0) ⁽⁴⁾;"–" ⁽⁴⁾_);@ ⁽⁴⁾_)
  
• #,##0.0 ⁽⁵⁾_);(#,##0.0) ⁽⁵⁾;"–" ⁽⁵⁾_);@ ⁽⁵⁾_)
  
• #,##0.0 ⁽⁶⁾_);(#,##0.0) ⁽⁶⁾;"–" ⁽⁶⁾_);@ ⁽⁶⁾_)
  
• #,##0.0 ⁽⁷⁾_);(#,##0.0) ⁽⁷⁾;"–" ⁽¹⁾_);@ ⁽⁷⁾_)
  
• #,##0.0 ⁽⁸⁾_);(#,##0.0) ⁽⁸⁾;"–" ⁽⁸⁾_);@ ⁽⁸⁾_)
  
• #,##0.0 ⁽⁹⁾_);(#,##0.0) ⁽⁹⁾;"–" ⁽⁹⁾_);@ ⁽⁹⁾_)
  
• #,##0.0 ⁽¹⁰⁾_);(#,##0.0) ⁽¹⁰⁾;"–" ⁽¹⁰⁾_);@ ⁽¹⁰⁾_)

However, when I enter many of the superscript symbols into the VBA editor, a "?" is returned.

The cycle code looks like this:

Sub FormatCycle()

Dim NumberFormat1 As Long, NumberFormat2 As Long
Dim NumberFormat3 As Long, NumberFormat4 As Long
Dim NumberFormat5 As Long, NumberFormat6 As Long
Dim NumberFormat7 As Long, NumberFormat8 As Long
Dim NumberFormat9 As Long, NumberFormat10 As Long

NumberFormat1 = "#,##0.0 ?¹?_);(#,##0.0) ?¹?;"–" ?¹?_);@ ?¹?_)"
NumberFormat2 = "#,##0.0 ?²?_);(#,##0.0) ?²?;"–" ?²?_);@ ?²?_)"
NumberFormat3 = "#,##0.0 ?³?_);(#,##0.0) ?³?;"–" ?³?_);@ ?³?_)"
NumberFormat4 = "#,##0.0 ???_);(#,##0.0) ???;"–" ???_);@ ???_)"
NumberFormat5 = "#,##0.0 ???_);(#,##0.0) ???;"–" ???_);@ ???_)"
NumberFormat6 = "#,##0.0 ???_);(#,##0.0) ???;"–" ???_);@ ???_)"
NumberFormat7 = "#,##0.0 ???_);(#,##0.0) ???;"–" ?¹?_);@ ???_)"
NumberFormat8 = "#,##0.0 ???_);(#,##0.0) ???;"–" ???_);@ ???_)"
NumberFormat9 = "#,##0.0 ???_);(#,##0.0) ???;"–" ???_);@ ???_)"
NumberFormat10 = "#,##0.0 ?¹??_);(#,##0.0) ?¹??;"–" ?¹??_);@ ?¹??_)"


With Selection.NumberFormat
   
    If .NumberFormat = NumberFormat1 Then
        .NumberFormat = NumberFormat2
    ElseIf .NumberFormat = NumberFormat2 Then
        .NumberFormat = NumberFormat3
    ElseIf .NumberFormat = NumberFormat3 Then
        .NumberFormat = NumberFormat4
    ElseIf .NumberFormat = NumberFormat4 Then
        .NumberFormat = NumberFormat5
    ElseIf .NumberFormat = NumberFormat5 Then
        .NumberFormat = NumberFormat6
    ElseIf .NumberFormat = NumberFormat6 Then
        .NumberFormat = NumberFormat7
    ElseIf .NumberFormat = NumberFormat7 Then
        .NumberFormat = NumberFormat8
    ElseIf .NumberFormat = NumberFormat8 Then
        .NumberFormat = NumberFormat9
    ElseIf .NumberFormat = NumberFormat9 Then
        .NumberFormat = NumberFormat10
    ElseIf .NumberFormat = NumberFormat10 Then
        .NumberFormat = NumberFormat1
    Else
        .NumberFormat = NumberFormat1
    End If

End With

End Sub

I believe you can use a different syntax to insert the character codes, something like ".Value = ChrW(&H207D)". Can someone please help me set this up for the formats I've described above?

The corresponding Unicode Hex character codes for the relevant symbols are as follows:

• ⁽ = 207D
  
• ⁾ = 207E
  
• ⁰ = 2070
  
• ¹ = 00B9
  
• ² = 00B2
  
• ³ = 00B3
  
• ⁴ = 2074
  
• ⁵ = 2075
  
• ⁶ = 2076
  
• ⁷ = 2077
  
• ⁸ = 2078
  
• ⁹ = 2079

Thank you for any help! Always much appreciated.

 

[NARAYANK991]

Hi ,

Try this :

Sub FormatCycle()
    Const NumberOfFormats = 11
    Const FormatString = "#,##0.0 %%%%_);(#,##0.0) %%%%;""–"" %%%%_);@ %%%%_)"
    Dim FormatsArray(0 To NumberOfFormats - 1) As String
   
    For i = 0 To 10
        Select Case i
              Case 0
                    FormatsArray(i) = Application.WorksheetFunction.Substitute(FormatString, "%%%%", ChrW(&H207D) & ChrW(&H2070) & ChrW(&H207E))
              Case 1
                    FormatsArray(i) = Application.WorksheetFunction.Substitute(FormatString, "%%%%", ChrW(&H207D) & ChrW(&HB9) & ChrW(&H207E))
              Case 2
                    FormatsArray(i) = Application.WorksheetFunction.Substitute(FormatString, "%%%%", ChrW(&H207D) & ChrW(&HB2) & ChrW(&H207E))
              Case 3
                    FormatsArray(i) = Application.WorksheetFunction.Substitute(FormatString, "%%%%", ChrW(&H207D) & ChrW(&HB3) & ChrW(&H207E))
              Case 4
                    FormatsArray(i) = Application.WorksheetFunction.Substitute(FormatString, "%%%%", ChrW(&H207D) & ChrW(&H2074) & ChrW(&H207E))
              Case 5
                    FormatsArray(i) = Application.WorksheetFunction.Substitute(FormatString, "%%%%", ChrW(&H207D) & ChrW(&H2075) & ChrW(&H207E))
              Case 6
                    FormatsArray(i) = Application.WorksheetFunction.Substitute(FormatString, "%%%%", ChrW(&H207D) & ChrW(&H2076) & ChrW(&H207E))
              Case 7
                    FormatsArray(i) = Application.WorksheetFunction.Substitute(FormatString, "%%%%", ChrW(&H207D) & ChrW(&H2077) & ChrW(&H207E))
              Case 8
                    FormatsArray(i) = Application.WorksheetFunction.Substitute(FormatString, "%%%%", ChrW(&H207D) & ChrW(&H2078) & ChrW(&H207E))
              Case 9
                    FormatsArray(i) = Application.WorksheetFunction.Substitute(FormatString, "%%%%", ChrW(&H207D) & ChrW(&H2079) & ChrW(&H207E))
              Case 10
                    FormatsArray(i) = Application.WorksheetFunction.Substitute(FormatString, "%%%%", ChrW(&H207D) & ChrW(&HB9) & ChrW(&H2070) & ChrW(&H207E))
        End Select
    Next
   
    With Selection
        currformat = .NumberFormat
        For i = 0 To 10
            If currformat = FormatsArray(i) Then
                j = (i + 1) Mod NumberOfFormats
                Exit For
            End If
        Next
        .NumberFormat = FormatsArray(j)
    End With
End Sub

Narayan

 

[bkanne]

Pure genius. Thank you so much!!