Dynamic property names in VBA

Question

I have a custom class module in VBA (Access) that is supposed to handle a large amount of external data. Currently I have two functions Read(name) and Write(name, value) that allows to read and set dynamic properties.

Is there a way to define a more syntactic way to read and write those data? I know that some objects in VBA have a special way of accessing data, for example the RecordSet, which allows to read and set data using myRS!property_name. Is there a way to do exactly the same for custom class modules?

Answer 1

The exclamation mark syntax is used to access members of a Scripting.Dictionary instance(you'll need to add a reference to Microsoft Scripting Runtime through Tools > References first). To use this syntaxyou'll need to be storing the information internally in a dictionary.

The quickest way to use it in a class is to give your class an object variable of type Scripting.Dictionary and set it up as follows:

Option Explicit

Dim d As Scripting.Dictionary

Private Sub Class_Initialize()
    Set d = New Scripting.Dictionary
End Sub

Private Sub Class_Terminate()
    Set d = Nothing
End Sub

Public Property Get IntData() As Scripting.Dictionary
    Set IntData = d
End Property

Now you can access properties using myinstance.IntData!MyProperty = 1... but to get to where you want to be you need to use Charlie Pearson's technique for making IntData the default member for your class.

Once that's done, you can use the following syntax:

Dim m As MyClass
Set m = New MyClass

Debug.Print "Age = " & m!Age ' prints: Age = 
m!Age = 27
Debug.Print "Age = " & m!Age ' prints: Age = 27
Set m = Nothing