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

Sennacherib

IMO what you intend to achieve is just, let's say, an excessive code flexibility. You can put constant lengths since the property names of the class are actually also hardcoded and coudn't be changed runtime (unlike a recordset you mentioned). Then if you change the class properties, you have to change the length data. As an alternative - in the case the properties need to be changed dynamically, better solution is to use dictionary object instead of the class, it has .Keys() and .Items() properties, that return name of keys and item values in arrays respectively. – Jankowski

Omegastripes.. I just want to clarify.. In my case i have a collection of employees with a name property. I just want to find the longest name out of all the employees. I would also need to find the longest office name. – Sennacherib

I am not trying to change the name of the property. I just wanted t – Sennacherib

Get the value. But the only way i could figure it.. Was to write a separate method for the name and separate method for the office. The code is an exact duplicate except for the name of the property id be searching.. I hope this makes sense. – Sennacherib

It wasn't quite clear at the first sight, now I've got what you need - check my answer. – Jankowski

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.

Jankowski

Clever.When I saw OP's question I remember thinking that something like this is possible in JavaScript but didn't think that you could use JavaScript from VBA like that. What sort of overhead does this entail? I haven't tested the code but fear that using the CreateObject in a loop like that would be expensive. Perhaps a global htmlfile object could be initialized in one sub and then called from another sub. – Chary

@JohnColeman objHtmlfile variable declared static, so the object will be created only once. – Jankowski

so it is -- I missed the static modifier – Chary

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")

Chary

That is exactly what i needed. Thank you!! – Sennacherib

@Dan The above function needs to be modified for whatever class or classes that you want to use it on. It is essentially a dispatcher. You have to modify the cases to tell it what to dispatch to. The sample code has three properties hard-wired in. There is no default case, so nothing will be printed unless you pass it an object with a property whose name corresponds to one of the cases. It would be nice if VBA had the sort of reflection abilities that JavaScript or Python has, but it doesn't. The function was meant to be just a low-tech kludge. – Chary

That’s what I’m beginning to realize about VBA. You can call scripts from elsewhere. Example: VBA doesn’t multi-thread but from what I’ve gathered (I haven’t tried it yet) if you needed a multi thread, you can put a script in the same folder that is called by vba that does what you need it to do on other cores. So in the end you don’t have to be limited by the parts of vba that stop you but can force it to call what you need the machine to do without writing entire programs in other languages. This is very interesting to me. – Farthingale

@Farthingale There is a fairly interesting Stack Overflow question about multithreading in VBA – Chary

I’ve seen it. There’s other discussions and threads too and it all fascinated me. – Farthingale

Answer 3

The other answers didn't work for me.

I successfully used the Eval() statement, eg:

Debug.Print Eval("objSample." & Prop1NamesArray(i))

Where Prop1NamesArray(i) is a string array of the names of the properties to be returned.

High

Obvious - but eval has always been avoided, I forgot it was an option. Perfect for my situation - the simplest solution I think. (For me, I have a function I am allowing a dictionary to be passed to - I don't care what objects are in the dictionary, just tell me the property names for 'value' and 'display' - and I use eval("myObj." & valueProperty), etc. to render something.) – Moody