VBA: Global Constant Strings

Question

I'm working with special characters for mathematics in Excel.
(Exponents⁴, GRΣΣK, and other misc Unicode characters.)

I'd like to store commonly-used Unicode strings as global constants, since that would probably be significantly faster (and less bug-prone) than calling CStr() each time I need one.

For example, I have these declarations outside all functions:

Public Const STRPOWER0        As String = CStr(ChrW(&H2070)) '0th power
Public Const STRPOWER1        As String = "¹" 'CStr(ChrW(&HB9)) 1st power
Public Const STRPOWER2        As String = "²" 'CStr(ChrW(&HB2)) 2nd power
Public Const STRPOWER3        As String = "³" 'CStr(ChrW(&HB3)) 3rd power
Public Const STRPOWER4        As String = CStr(ChrW(&H2074)) '4th power
Public Const STRPOWER5        As String = CStr(ChrW(&H2075)) '5th power
Public Const STRPOWER6        As String = CStr(ChrW(&H2076)) '6th power
Public Const STRPOWER7        As String = CStr(ChrW(&H2077)) '7th power
Public Const STRPOWER8        As String = CStr(ChrW(&H2078)) '8th power
Public Const STRPOWER9        As String = CStr(ChrW(&H2079)) '9th power
Public Const STRPOWERNEGATIVE As String = CStr(ChrW(&H207B)) 'Superscript negative sign

The problem is, whenever I try to reference one of these strings, the value is Empty
(If I turn on Option Explicit I get an error for Variable not defined)

What am I doing wrong?
Does VBA not support global constants as strings?

Answer 1

Unfortunately, you cannot use functions to set values to constants. The purpose of a Const is that the value of the constant is set prior to runtime, and functions can only return values during runtime.

Your issue appears that your constants weren't declared in the declarations section of the module, which is why you do not get a compile error when running any code.

There are some alternatives, however.

You can create a function for each of your variables:

Function STRPOWER0() As String
    STRPOWER0 = CStr(ChrW(&H2070))
End Function

Sub Main()
    Msgbox STRPOWER0
End Sub

You made a point that you were concerned running the CStr() and ChrW() functions every time you called STRPOWER0. While you would likely only take a very minimal performance hit - even when calling many times - you can use Static variables to only do the conversions on the first time during runtime.

Function STRPOWER0() As String
    Static RetVal As String
    If RetVal = "" Then RetVal = CStr(ChrW(&H2070))
    STRPOWER0 = RetVal
End Function

Sub Main()
    Msgbox STRPOWER0
End Sub

Static allows the variable RetVal to keep it's data, even when the function falls out of scope, for the entire duration of the runtime.

---

Another alternative is to use a sub to set all your variables at the beginning of runtime:

'This line in declarations section of module
Public STRPOWER0$, STRPOWER1$, STRPOWER2$ ', etc

Private Sub SetPubVars

    STRPOWER0 = CStr(ChrW(&H2070))
    STRPOWER1 = CStr(ChrW(&HB9))
    STRPOWER2 = CStr(ChrW(&HB2))

    ' So on

End Sub

Sub MAIN()

    ' Set the values
    SetPubVars

    MsgBox STRPOWER0

End Sub