How to dynamically reference an object property in VBA

Question

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?

Answer 1

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