Better way to iterate over custom-object class Excel VBA

Question

I'm trying to figure out a way to iterate over custom-objects contained in (a dictionary) other custom objects without having to use 'set' every time.

my goal would be able to write straight:

For Each Person In Family.GetMembers
    test= Person.Age
Next Person

but the above gives me a object required error. However, I want to avoid something like

for each name in family.keys
   set Person = Family.GetMember(name) 
   test= Person.Age
next name

class object cperson

Option Explicit
Private pName As String
Private pAge As Integer
Public Property Let name(name As String):
    pName = name
End Property
Public Property Get name() As String
    name = pName
End Property
Public Property Let Age(Age As Integer):
    pAge = Age
End Property
Public Property Get Age() As Integer
    Age = pAge
End Property

class object cfamily

Option Explicit
Private pFamily As Object
Private pName As String
Private Sub Class_Initialize()
    Set pFamily = CreateObject("Scripting.Dictionary")
End Sub
Private Sub Class_Terminate()
    Set pFamily = Nothing
End Sub
Public Sub Add(Person As CPerson)
    If pFamily.exists(Person.name) Then
        pFamily.Remove Person.name
        pFamily.Add Person.name, Person
    Else
        pFamily.Add Person.name, Person
    End If
End Sub
Public Property Get GetMember(name As Variant) As CPerson:
    Set GetMember = pFamily(name)
End Property
Public Property Let name(name As String):
    pName = name
End Property
Public Property Get name() As String
    name = pName
End Property
Public Property Get GetMembers() As Variant
    Dim tmpArray() As Variant, Person As CPerson, i As Integer, name As Variant
    ReDim tmpArray(pFamily.Count)
    i = 1
    For Each name In pFamily.keys
        Set Person = pFamily(name)
        Set tmpArray(i) = Person
        i=i+1
    Next name
    GetMembers = tmpArray
End Property

Dmitry Pavliv · Accepted Answer · 2014-01-14T13:10:45.013

I've slightly modified your code (Property Get GetMembers() of cFamily class):

Public Property Get GetMembers() As cPerson()
    Dim tmpArray() As cPerson, i As Integer
    ReDim tmpArray(pFamily.Count - 1)

    For i = 0 To pFamily.Count - 1
        Set tmpArray(i) = pFamily.Items()(i)
    Next
    GetMembers = tmpArray
End Property

and now you can use it like follows:

Sub Test()
    Dim family As New cFamily

    Dim p1 As New cPerson
    Dim p2 As New cPerson
    Dim p3 As New cPerson

    Dim p as Variant

    p1.name = "name1"
    p2.name = "name2"
    p3.name = "name3"

    family.Add p1
    family.Add p2
    family.Add p3

    For Each p In family.GetMembers
        MsgBox p.name
    Next
End Sub

Your Property Get GetMembers() was wrong, because:

1) you've defined wrong dimmension of array: ReDim tmpArray(pFamily.Count) means that tmpArray has dimmension 0 To pFamily.Count. You need to use i.e. ReDim tmpArray(1 To pFamily.Count)

2) you haven't increment i - it's always equals 1.

Sweet... confess was not expecting this approach would work at all (maybe something more like this http://stackoverflow.com/questions/20194919/how-to-implement-custom-iterable-class-in-vba). But that is just what I wanted! — , Jan 14 '14 at 19:12

Better way to iterate over custom-object class Excel VBA

1 Answers1