Assign an object to an array within a loop in VBA

user_1542011 0
Viewed 880

I try to fill an array with objects that are created within a loop as follows. The problem is that all cells seem to have the same object in the end. The explanation might be that obj is not a local variable with respect to the loop.

Sub foo()
    Dim Arr(1 To 3) As Class1
    Dim i As Integer
    For i = 1 To 3
        Dim obj As New Class1
        obj.name = i
        Set Arr(i) = obj
    Next
    For i = 1 To 3
        Debug.Print Arr(i).name 
    Next
End Sub

Surprisingly, the output is 

3
3
3

I have also tried to remove the Set and instead have Arr(i) = obj. That results in Object variable or with block variable not set.

vba excel
4 Answers

Your issue is the declaration of your object.

Dim foo as New bar

That is called a self-assigned declaration what makes setting a new object optional. If you call an objects member and it is not allready set it get's created (implicitSet foo = New bar). But as you allready created an instance (on first call toobj.name). that one is reused and the same reference is stored three times for the same objects-instance. That's why all elements in array return the same value as they are the same objects-instance, not three different ones.

So don't useNewin declarations, then you always need aSet fooand can check the object instance onNothing.

A second issue with your code is that assigninig an object to an array is that deleting elements from an array is error prone and not deleted references lead to not disposed, but unused objects.

The prefered storage for object(-references) is aCollection.

Sub foo()
    Dim ObjCollection as Collection
    Set ObjCollection = New Collection
    Dim i As Integer
    For i = 1 To 3
        Dim obj As bar
        Set obj = New bar
        obj.name = i
        ObjCollection.Add obj
    Next
    For i = 1 To 3
        Debug.Print ObjCollection(i).name 
    Next
End Sub
user_94393300

This is the way:-

Sub foo()
    Dim Arr(1 To 3) As Variant
    Dim i As Integer

    For i = 1 To 3
        Set Arr(i) = Worksheets(i)
    Next
    For i = 1 To 3
        Debug.Print Arr(i).Name
    Next
End Sub
user_76754370

You have 2 ways to do this:

Notes: obj has not been recreated, so when you call for the next time obj in Arr (1) is still affected by the subsequent call.

First:

Sub foo()
    Dim Arr(2) As Variant

    Dim i As Integer
    Dim obj As New Class1
    For i = 0 To 2
        Set obj = New Class1 '<<<-----
        obj.name = i
        Set Arr(i) = obj
    Next
    For i = 0 To 2
        Debug.Print Arr(i).name
    Next
End Sub

Second:

Sub foo()
    Dim Arr(2) As Variant
    Dim i As Integer
    For i = 0 To 2
        Dim obj As New Class1
        obj.name = i
        Set Arr(i) = obj
        Set obj = Nothing  <<<-----
    Next
    For i = 0 To 2
        Debug.Print Arr(i).name
    Next
End Sub
user_126917010

Try this, it'll save you a lot of headaches. Cheers!

Option Explicit

Sub foo()    
    Dim Arr(1 To 3) As New Class1 ' < good to know this version
    Dim i As Long
    For i = 1 To 3
       With Arr(i) ' < saves you some typing
          .Name = i
          Debug.Print .Name
       End With
    Next
End Sub
user_125754330
Related