Alternatives to Public Variables in VBA

Question

I have a number of public variables that are referenced across several modules. I know if you debug or hit stop the variable gets cleared out. I have been writing these variables to a spreadsheet so I have them in case they get cleared out, but this is messy. I'd rather have it all stored it code. Is there any alternative to a public variable that never gets cleared?

Answer 1

Here is an example of the CustomDocumentProperties, which I recently started using to store some meta-information (easier than dealing with the CustomXMLParts).

The examples below store only string data, but you can also use date, number and Yes/No (which with some finagling you could sub as a Boolean). You are limited to 255 characters for string data.

   Sub Test()
   '## Assign a CDP
   SetCustomProperty "myProperty", "some value I want to store"

   End Sub

You can view the CPD's from the Backstage | Info | Properties | Advanced Properties | Custom:

In the event that you End run-time, you can restore the values from the CDP, you can query the property value by:

myVar = ActiveWorkbook.CustomDocumentProperties("myProperty").Value

You can use functions like these to set properties in the CustomDocumentProperties collection:

Sub SetCustomProperty(property$, val$)
    Dim cdp As Variant
    Dim hasProperty As Boolean
    If HasCustomProperty(property) Then
        ActiveWorkbook.CustomDocumentProperties(property).Value = val
    Else
        ActiveWorkbook.CustomDocumentProperties.Add property, False, msoPropertyTypeString, val

    End If
End Sub
Private Function HasCustomProperty(property$) As Boolean
Dim cdp As Variant
Dim boo As Boolean
For Each cdp In ActiveWorkbook.CustomDocumentProperties
    If cdp.name = property Then
        boo = True
        Exit For
    End If
Next
HasCustomProperty = boo
End Function

Answer 2

A simple solution would be to store your variables in the registry, and just read/write them as necessary. This has the added benefit of preserving values over multiple Excel sessions (and even after a computer reboot, or a crash - assuming your registry survived it!).

EDIT: Also see John Walkenbach's book for more information on this.

EDIT: See below comment by Ioannis for an important consideration.

---

Boilerplate warning: Here be dragons, Twiddle with the Windows registry at your peril, etc etc.

---

The above warning notwithstanding, realize that almost every program on your Windows computer does something with the registry, and it is not inherently dangerous to do so. Just make sure your code only changes/deletes registry keys which were created by your Excel application.

---

Example procedures using Windows Scripting (I didn't write these; from a quick search):

Reading from the Registry:

'reads the value for the registry key i_RegKey
'if the key cannot be found, the return value is ""
Function RegKeyRead(i_RegKey As String) As String
Dim myWS As Object

  On Error Resume Next
  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'read key from registry
  RegKeyRead = myWS.RegRead(i_RegKey)
End Function

Checking if a Registry key exists:

'returns True if the registry key i_RegKey was found
'and False if not
Function RegKeyExists(i_RegKey As String) As Boolean
Dim myWS As Object

  On Error GoTo ErrorHandler
  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'try to read the registry key
  myWS.RegRead i_RegKey
  'key was found
  RegKeyExists = True
  Exit Function

ErrorHandler:
  'key was not found
  RegKeyExists = False
End Function

Saving a Registry key:

'sets the registry key i_RegKey to the
'value i_Value with type i_Type
'if i_Type is omitted, the value will be saved as string
'if i_RegKey wasn't found, a new registry key will be created
Sub RegKeySave(i_RegKey As String, _
               i_Value As String, _
      Optional i_Type As String = "REG_SZ")
Dim myWS As Object

  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'write registry key
  myWS.RegWrite i_RegKey, i_Value, i_Type

End Sub

Deleting a key from the Registry:

'deletes i_RegKey from the registry
'returns True if the deletion was successful,
'and False if not (the key couldn't be found)
Function RegKeyDelete(i_RegKey As String) As Boolean
Dim myWS As Object

  On Error GoTo ErrorHandler
  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'delete registry key
  myWS.RegDelete i_RegKey
  'deletion was successful
  RegKeyDelete = True
  Exit Function

ErrorHandler:
  'deletion wasn't successful
  RegKeyDelete = False
End Function