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?

Are your global constants located in a normal module (not a code, userform, worksheet, or workbook module)? Are you using `Option Private Module` then attempting to use these constants in a different module? Are these located in the declaration area at the very top of the module, just under `Option Explicit`? — K.Dᴀᴠɪs, Apr 24 '19 at 22:11
To build on the above, I have a feeling that you are not placing in the declarations area of the module, because you can't use a function to set a value to a constant. `CStr()` and `ChrW()` are both functions. — K.Dᴀᴠɪs, Apr 24 '19 at 22:13
@K.Dᴀᴠɪs `... located in a normal module?` Yes. `... using Option Private Module?` I don't know what that is. I haven't used it that I know of, but maybe I can if needed? `... attempting to use these constants in a different module` No, just using them in the same module. `... located at the very top` No, there are some other type declarations and a couple other functions above these. `... can't use a function to set a value to a constant.` OHH, yeah, that's probably a red flag. I guess that means I'll have to let them be "variables" then, and just be careful to never overwrite them? — Giffyguy, Apr 24 '19 at 22:15
Since they are declared as `Const`, you can't overwrite them — BankBuilder, Apr 24 '19 at 22:29

score 5 · Accepted Answer · answered Apr 24 '19 at 22:32

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

I followed your advice, and it's all working great now! Statics won't work in my case, because there are multiple functions that need to use these string values (and I'm not using classes, sticking with procedural programming). So I used your final alternative, and declared non-const private global variables, and wrote a sub to initialize them, then invoked the init sub at the beginning of every function that needs access to the globals. I added a global boolean variable called `GlobalsInitialized` so my initialization sub will skip the assignments if invoked multiple times. — Giffyguy, Apr 25 '19 at 00:17

VBA: Global Constant Strings

1 Answers1