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