CODEWITHSUNDEEP
vbaexcel
trackmeifUcan
trackmeifUcan

Reputation: 103

Loop through properties of a class in vba

I have created this class,

Interval.cls
public x as new collection
public y as new collection
public z as string

I wish to loop through the properties because I want the user to choose input x,y,z in the form so at my sub

sub test()

dim inter as new Intervals

inter.x.add "a"
inter.x.add "a"
inter.x.add "b"
inter.x.add "b"
inter.x.add "b"

userString1 = x
userString2 = a

' I want to make it dynamic so that whatever the user wants i can provide the results. 'i just want to make it possible to compare the userString to my properties

for each i in inter

'so i wish for i in this loop to be my properties x,y,z so i can make the if statement if ( i = userString1) then

end if
next i
end sub

I know i can maybe make a tweek in the class to make it iterable, i don't know how to do it

any help is appreciated

Upvotes: 1

Views: 5972

Answers (3)

JosephD
JosephD

Reputation: 119

I know this is an old post, but I wanted to share a solution I came up with when I had a similar issue.

VBA doesn't seem to offer any abstraction that allows you to refer to class members without naming them directly.

However, there is a way to loop through the members of a class in code. It’s kind of clunky, and it adds overhead, but makes it possible to address some or all of the members of your class from one or more loops without having to hard-code the property names every time. It might be useful if you have to step through the class from multiple places in your code.

Say you have a class with three properties (string A, integer B and double C), defined as usual with Property Let/Get statements, and private backing fields. Now, add a function to your class definition that uses a named argument to return each of the values from the class itself.

Public Function MyValue (MyName As String) As Variant
    Select Case MyName
        Case “ValueA”
            MyValue = Me.A
        Case “ValueB”
            MyValue = Me.B
        Case “ValueC”
            MyValue = Me.C
    End Select
End Function

From your main code, you can now just pass in the string argument to get the desired value from the class. By creating a variant array of the desired name arguments in your code, you can loop through the array and retrieve the values from any instance of your class.

Dim VarList as Variant
Dim IntCounter as Integer
VarList = Array(“ValueB”, “ValueC”, “ValueA”)
For IntCounter = 0 to UBound(VarList)
    Debug.Print MyClassInstance.MyValue(Cstr(VarList(IntCounter)))
    Debug.Print MySecondClassInstance.MyValue(Cstr(VarList(IntCounter)))
Next IntCounter

This allows you to loop through as many or as few of the values from your class as you need, in any order, by just changing the order of the arguments in the array. (You would need to create a similar class function if you wanted to assign incoming values to the instance fields.) As I said, it’s not perfect: it adds overhead, and unless all of the properties in your class happen to be the same type, it requires the function to return variant values, possibly forcing the caller to cast the returned values. But if you have to deal with numerous property values in multiple places in your code, it can be considerably less complicated than hard-coded direct assignments for each property name.

Upvotes: 0

Graham Anderson
Graham Anderson

Reputation: 1239

Is this the sort of thing you are after?

'in class
Public x As New Collection
Public y As New Collection
Public z As String
Public Property Get Item(i As Integer) As Variant

   Select Case i
      Case 1: Item = Me.x
      Case 2: Item = Me.y
      Case 3: Item = Me.z
   End Select

End Property

Sub try()

Dim userString2 As String
Dim userString1 As String
Dim inter As Interval
Dim i As Integer

Set inter = New Interval

inter.x.Add "a"
inter.x.Add "a"
inter.x.Add "b"
inter.x.Add "b"
inter.x.Add "b"

userString2 = "aabbb"

For i = 1 To inter.x.Count

       userString1 = userString1 & inter.x.Item(i)
Next i

 If userString1 = userString2 Then
 '<do whatever>
 End If
End Sub

OK how about forgetting the class and just using an array? The array of strings can be of any length and you can dynamically control it's size by the calling procedure.

Sub tryWithArray(ByRef StringArray() As String)

Dim userString2 As String
Dim i As Integer

userString2 = "b"


For i = 1 To UBound(StringArray())
    If userString2 = StringArray(i) Then
        'do something
    End If
Next i

End Sub

Upvotes: 0

Mr.Monshaw
Mr.Monshaw

Reputation: 450

'in class
Public Property Get Item(i As Integer) As Variant

   Select Case ndx
      Case 1: Item = Me.x
      Case 2: Item = Me.y
      Case 3: Item = Me.z
   End Select

End Property

'in sub
Dim c as Collection
Dim s as String

For i = 1 to 3
   if i < 3 then 
      set c = inter.Item(i)
      'iterate through collection
   else
      s = inter.Item(i)
   end if
next i

something like this is probably the easiest way to go, i didnt test it but hopefully it at least gets you started

Upvotes: 2

Related Questions