2

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.

Improve this question
1
  • Dim Arr(1 To 3) As Class1 The variable must be of Variant data type. Dim obj As New Class1 You can't set a Dim statement in a loop. I don't know why it doesn't crash. Anyway, you name the same obj as i three times. The last I sticks. Of course, Set Arr(i) = obj` does assign this same 0bj to 3 elements of Arr() and, you are right, it's the same object assigned to 3 elements of Arr(). Commented Mar 19, 2020 at 10:40

4 Answers 4

Reset to default
3

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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

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
Improve this answer

3 Comments

Worksheets are existing objects. The goal is to create new objects inside the loop. It can be done like this: Set Arr(i) = New Class1 and Arr(i).name = i
Set Arr(i) = Worksheets.Add would create a new object and assign it to the variable. Follow the rules for creating the kind of object you have in mind. Treat Arr(i) as you would treat any other variable to which you assign an object.
I think if you could point out what is going wrong in my code, your answer is perfect.
2

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
Improve this answer

Comments

0

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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.