How do I assign a value to a property where the property name is supplied at runtime in VBA?

Question

I'm trying to create a worksheet that creates a list of values that will be used to initialize the values of an instantiated class.

For example, I might have the following in my initialization worksheet:

Property Name              Value
StartingCol                A
StartingRow                11

I'd then create a class that would parse this worksheet and provide me with an enumberable that I could use to initialize the properties of an instantiated object. However, I'm not sure how I might be able to specify the property value at runtime using a string rather than specifying it explicitly in code. You can get an idea of what I'm trying to accomplish in the code below:

Sub test_PropertyAssignment()
Dim sp As SheetParser
Dim strFieldName As String
Dim strFieldNameValue As String
Set sp = New SheetParser

'The property name is supplied explicitly'
sp.StartingCol = "B"

strFieldName = "StartingCol"
strFieldNameValue = "B"

sp.[how can I supply strFieldName to specify the property?] = strFieldNameValue 'Will not Work'

End Sub

Is there a way to use a string at runtime to specify the property name rather than specifying explicitly in code?

Answer 1

Look up the CallByName function in the VBA help. You should be able to do something like:

Call CallByName(sp,strFieldName,vbLet,strFieldNameValue)