8

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
braX
  • 11,506
  • 5
  • 20
  • 33
R. J.
  • 81
  • 1
  • 3
  • 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. – omegastripes Mar 17 '16 at 17:28
  • 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. – R. J. Mar 17 '16 at 18:07
  • I am not trying to change the name of the property. I just wanted t – R. J. Mar 17 '16 at 18:07
  • 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. – R. J. Mar 17 '16 at 18:09
  • It wasn't quite clear at the first sight, now I've got what you need - check my answer. – omegastripes Mar 17 '16 at 19:11

3 Answers3

7

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.

omegastripes
  • 12,351
  • 4
  • 45
  • 96
  • 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. – John Coleman Mar 17 '16 at 19:14
  • @JohnColeman `objHtmlfile` variable declared static, so the object will be created only once. – omegastripes Mar 17 '16 at 19:16
  • so it is -- I missed the `static` modifier – John Coleman Mar 17 '16 at 19:18
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")

John Coleman
  • 51,337
  • 7
  • 54
  • 119
  • That is exactly what i needed. Thank you!! – R. J. Mar 17 '16 at 18:45
  • @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. – John Coleman Feb 09 '19 at 14:42
  • 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. – Dan Feb 09 '19 at 14:47
  • @Dan There is a fairly interesting Stack Overflow question about [multithreading in VBA](https://stackoverflow.com/q/5721564/4996248) – John Coleman Feb 09 '19 at 14:51
  • I’ve seen it. There’s other discussions and threads too and it all fascinated me. – Dan Feb 10 '19 at 23:16
2

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.

David
  • 1,192
  • 5
  • 13
  • 30
Drummond Richardson
  • 21
  • 1
  • 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.) – Bill Gillingham Jan 05 '21 at 14:58