CODEWITHSUNDEEP
excelvba
Christian
Christian

Reputation: 1460

Assign an object to an array within a loop in VBA

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.

Upvotes: 2

Views: 2594

Answers (4)

BKK_St
BKK_St

Reputation: 1

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

Upvotes: 0

ComputerVersteher
ComputerVersteher

Reputation: 2696

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

Upvotes: 3

Dang D. Khanh
Dang D. Khanh

Reputation: 1471

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

Upvotes: 2

Variatus
Variatus

Reputation: 14383

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

Upvotes: 2

Related Questions