4

I'm trying to write a VBA function that counts the objects in a collection based on the value of one of the object's properties. I need the examined object property to be dynamic, supplied by the function parameters. I could use an if then statement, but that would have many, many elseif clauses, each with identical procedures, except the property name.

I'd like to avoid repeating my code over and over for each property name. Here's what I have so far.

Private Function getTicketCount(c As Collection, f As String, s As String) _
 As Long
' @param c: collection of Ticket objects.
' @param f: property to filter.
' @param s: filter string.
'
' Function returns number of tickets that pass the filter.

Dim x As Long
Dim t As Ticket

x = 0

For Each t In c
    If t.f = s Then x = x + 1 ' Compiler throws "Method or data member not found."
Next t

getTicketCount = x
End Function

The issue I'm having is that the compiler is looking for the "f" property of t instead of the value-of-f property of t. The exact error is commented in the code block above. How do I use the value of f instead of "f" to reference the object property?

T6J2E5
  • 171
  • 3
  • 15
  • How many properties does `Ticket` have? It's not elegant, but you could do `Select Case f` and have the cases be the different properties. – Degustaf Sep 05 '14 at 18:29
  • What exactly is the error that you are getting? – Ross McConeghy Sep 05 '14 at 18:35
  • Question edited to clarify. The compiler throws an error, because it's looking for property "f" when it _should_ be looking for property value-of-f. The passed property string does exist. The object itself has a few dozen properties, and coding them all under separate case clauses is precisely what I want to avoid. – T6J2E5 Sep 05 '14 at 18:51

1 Answers1

10

I believe you want to use the CallByName method CallByName MSDN Link

Private Function getTicketCount(c As Collection, f As String, s As String) _
 As Long
' @param c: collection of Ticket objects.
' @param f: property to filter.
' @param s: filter string.
'
' Function returns number of tickets that pass the filter.

Dim x As Long
Dim t As Ticket

x = 0

For Each t In c
    If CallByName(t, f, VbGet) = s Then x = x + 1 ' Compiler throws "Method or data member not found."
Next t

getTicketCount = x
End Function
Sorceri
  • 7,870
  • 1
  • 29
  • 38
  • Yes! I was looking for just such a function on MSDN, but couldn't find it. Thank you! – T6J2E5 Sep 05 '14 at 21:00
  • This answer also shows how to set the property with CallByName. The documentation isn't entirely clear on that, particularly on when to use vbSet vs vbLet. http://stackoverflow.com/questions/5706791/how-do-i-use-variables-to-set-properties-in-vba-excel – Chris Strickland Apr 01 '17 at 15:48