CODEWITHSUNDEEP
vbaexcelobject
T6J2E5
T6J2E5

Reputation: 171

How to dynamically reference an object property in VBA

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?

Upvotes: 4

Views: 6628

Answers (2)

Gary E. Didio
Gary E. Didio

Reputation: 13

I have a similar problem and get a Type mismatch for SCHED_ANAL, which is a public array used to store numerous values for project schedule quality calculations. I have a set of functions that compute quality based on several parameters. Basic code is as follows:

' ' Declared in a module ' Public SCHED_ANAL(NoAnalItems) As SA_Array ' SA_Array is custom with 35 items

Function qsM (stype) ' stype is the type of calculation to perform (e.g., "SAT")

dim qa as single

qa=CallByName(SCHED_ANAL(PMDurn),stype,VbGet) ... End Function

Upvotes: 0

Sorceri
Sorceri

Reputation: 8043

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

Upvotes: 10

Related Questions