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?

Accepted Answer

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
```

Alternatives to Public Variables in VBA

Solution

Problem

Related problems