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?

score 7 · Answer 1 · edited May 08 '18 at 21:48

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

Okay, thanks so far.. but is there any way to use this without having an underlying Dictionary/Collection? My data comes from an XML file and the index key I want to use is partially based on the path. — poke, Jan 26 '11 at 18:03
If your XML library exposes the contents of the relevant nodes as a dictionary, you could define a property which returns that dictionary and make that property the default member; if it is a bit more complicated you could try creating a dictionary mapping properties to the XPath to particular instances. This technique relies quite strongly on a dictionary or collection. — KyleNZ, Jan 27 '11 at 13:02

score 6 · Accepted Answer · answered Jan 27 '11 at 16:54

Okay, thanks to Alain and KyleNZ I have now found a working way to do this, without having a collection or enumerable object below.

Basically, thanks to the name of the ! operator, I found out, that access via the bang/pling operator is equivalent to accessing the default member of an object. If the property Value is the default member of my class module, then there are three equivalent statements to access that property:

obj.Value("param")
obj("param")
obj!param

So to make a short syntax working for a custom class module, all one has to do is to define a default member. For example, I now used the following Value property:

Property Get Value(name As String) As String
    Value = SomeLookupInMyXMLDocument(name)
End Property

Property Let Value(name As String, val As String) As String
    SetSomeNodeValueInMyXMLDocument(name, val)
End Property

Normally, you could now access that like this:

obj.Value("foo") = "New value"
MsgBox obj.Value("foo")

Now to make that property the default member, you have to add a line to the Property definition:

Attribute Value.VB_UserMemId = 0

So, I end up with this:

Property Get Value(name As String) As String
Attribute Value.VB_UserMemId = 0
    Value = SomeLookupInMyXMLDocument(name)
End Property

Property Let Value(name As String, val As String) As String
Attribute Value.VB_UserMemId = 0
    SetSomeNodeValueInMyXMLDocument(name, val)
End Property

And after that, this works and equivalent to the code shown above:

obj("foo") = "New value"
MsgBox obj("foo")

' As well as
obj!foo = "New value"
MsgBox obj!foo

' Or for more complex `name` entries (i.e. with invalid identifier symbols)
obj![foo] = "New value"
MsgBox obj![foo]

Note that you have to add the Attribute Value.VB_UserMemId = 0 in some other editor than the VBA editor that ships with Microsoft Office, as that one hides Attribute directives for some reason.. You can easily export the module, open it in notepad, add the directives, and import it back in the VBA editor. As long as you don't change too much with the default member, the directive should not be removed (just make sure you check from time to time in an external editor).

score 3 · Answer 3 · edited May 23 '17 at 11:51

See this other question: Bang Notation and Dot Notation in VBA and MS-Access

The bang operator (!) is shorthand for accessing members of a Collection or other enumerable object

If you make your class extend the Collection class in VBA then you should be able to take advantage of those operators. In the following question is an example of a user who extended the collection class: Extend Collections Class VBA

Dynamic property names in VBA

3 Answers3