Refer to a class property using a variable?

Question

I have a collection (employees) of employee that contain name, id, office, and officeto fields. I need to printout this information in equally spaced columns. So i need to find the length of the longest string name, office, officeto...and add spaces to make the columns equally spaced.
I know how to do this using a recordset sending the field names into a function. So my question is... Is it possible to refer to a class property (name, office, officeto) by using a variable (similar to rst! [fieldname]). I tried setting it up like it was a recordset loop on a field, but it doesnt compile. The error is class.property not defined.

Public Function PropertyLen(ByVal Property As String, ByRef Employees As colEmployees) As Integer

'This function uses a passed in class property, and returns the len of the longest class property in collection

On Error GoTo ErrorHandler:

Dim Emp As clsEmployee
Dim intLen As Integer 
Dim lngCount As Long

For lngCount = 1 To Employees.Count

       Set Emp = Employees.Item(lngCount)

       If Len(Trim(Emp.Property)) > intLen Then
            intLen = Len(Trim(Emp.Property))
       End If

       Set Emp = Nothing  
Next

    FieldLen = intLen

ExitFunc:
'clean up
    Set Emp = Nothing
    Exit Function

ErrorHandler:
    modErrorHandler.DisplayUnexpectedError Err.Number, Err.Description
    Resume ExitFunc

End Function

Answer 1

There is a sample Class Module clsSample used for the test:

Public Prop1
Public Prop2
Public Prop3
Public Prop4

You may use native VBA function CallByName() to get property value by name:

Sub TestGetProperty()

    Set objSample = New clsSample
    objSample.Prop1 = "TEST"
    Debug.Print CallByName(objSample, "Prop1", VbGet) ' TEST

End Sub

If you do not want to use CallByName() then you may resort to jscript syntax object[property]:

Sub TestGetProperty()

    Set objSample = New clsSample
    objSample.Prop1 = "TEST"
    Debug.Print GetProperty(objSample, "Prop1") ' TEST

End Sub

Function GetProperty(objSample, strName)

    Static objHtmlfile As Object

    If objHtmlfile Is Nothing Then
        Set objHtmlfile = CreateObject("htmlfile")
        objHtmlfile.parentWindow.execScript "function GetProperty(sample, name) {return sample[name]}", "jscript"
    End If
    GetProperty = objHtmlfile.parentWindow.GetProperty(objSample, strName)

End Function

BTW there are another similar solutions allowing to evaluate a string into an object and to create a new class instance by the class name.

Answer 2

You could create a wrapper function which takes an object and a string name of a property and returns the object's property with that name. Something like this:

Function GetProperty(O As Object, property As String) As String
    Dim s As String
    property = LCase(property)
    Select Case property
        Case "name"
            s = O.Name
        Case "id"
            s = O.ID
        Case "office"
            s = O.Office
        Case "officeto"
            s = O.officeto
    End Select
    GetProperty = s
End Function

This is mostly untested (since I didn't feel like instantiating a member of your class) but it is able to e.g. return the name of Sheet1 when I evaluate GetProperty(Sheets(1), "name")