ERASEd Object Array

After I Erase an array of objects, how can I test a node to see if still in its erased state? IsEmpty, IsNull and Is Nothing all return False.

Or is there a better way to clear the array than Erase?

I'm sure I've seen similar questions asked before but search isn't finding an answer.

Thanks...

What is a node?

When one Erases an array it resets the array to have no elements and no dimensions.

IsEmpty applies only to cells
IsNull applies only to values
Is Nothing applies only to objects. And arrays are not objects.

IsEmpty applies to variant variables.


The Erase help says this about array of objects.

Array of objects, Sets each element to the special value Nothing.

Originally Posted by Andy Pope

IsEmpty applies to variant variables.


The Erase help says this about array of objects.

So then shouldn't this print True?

Public Sub Test9()

Dim oArray(5) As New Class1

Erase oArray

Debug.Print oArray(3) Is Nothing

End Sub

Actually I see now that even if I explicitly SET oArray(3) = Nothing, the expression oArray(3) Is Nothing is still false.

Oh well, I guess I can just use a property to indicate that the element is connected to an object instance. Seems like there should be a better way...

As a follow on, I ran this

Sub test3()
    Dim oArray(1 To 5) As New Class1
    
    MsgBox oArray(3) Is Nothing: ' false
    
    oArray(3).Name = "smith"
    MsgBox oArray(3).Name: ' smith
    
    Erase oArray
    
    MsgBox oArray(3).Name: ' vbNullString
    oArray(3).Name = "jones"
    MsgBox oArray(3).Name: ' jones
    
    MsgBox oArray(3) Is Nothing: 'false
End Sub

That shows that when one use the New keyword when declaring an array of objects, Erase "passes along" the New.

The OP quesition is no more puzzling than these results

Sub trial()
    Dim xObj As Class1
    
   MsgBox xObj Is Nothing: ' true
    
    Set xObj = New Class1
    
    MsgBox xObj Is Nothing:' false
End Sub

It seems to be the New keyword.
I set up a class with a .Name property.
And ran this test code with these results

Sub test1()
    Dim oArray(1 To 5) As New Class1
    
    MsgBox oArray(3) Is Nothing: ' false

    oArray(3).Name = "smith"
    MsgBox oArray(3).Name: ' smith

    Erase oArray

    MsgBox oArray(3) Is Nothing: 'false
End Sub

And then ran this test sub with these results

Sub test2()
    Dim oArray(1 To 5) As Class1
    
    MsgBox oArray(3) Is Nothing: ' true
    oArray(3).Name = "smith": ' error 91 object variable not set error
    
    MsgBox oArray(3).Name
    
    Erase oArray
    MsgBox oArray(3) Is Nothing
End Sub

My guess is that when the array declaration statement includes the New keyword, then oArray(3) is not Nothing, it is an instance of a Class1 object. Hence the False results from the test.

Originally Posted by mikerickson

It seems to be the New keyword.
I set up a class with a .Name property.
And ran this test code with these results

Sub test1()
    Dim oArray(1 To 5) As New Class1
    
    MsgBox oArray(3) Is Nothing: ' false

    oArray(3).Name = "smith"
    MsgBox oArray(3).Name: ' smith

    Erase oArray

    MsgBox oArray(3) Is Nothing: 'false
End Sub

And then ran this test sub with these results

Sub test2()
    Dim oArray(1 To 5) As Class1
    
    MsgBox oArray(3) Is Nothing: ' true
    oArray(3).Name = "smith": ' error 91 object variable not set error
    
    MsgBox oArray(3).Name
    
    Erase oArray
    MsgBox oArray(3) Is Nothing
End Sub

My guess is that when the array declaration statement includes the New keyword, then oArray(3) is not Nothing, it is an instance of a Class1 object. Hence the False results from the test.

NEW definitely creates the instance (though not until 1st reference I think) but without NEW (in either the DIM or each SET) there would be no actual instances of the objects, and no reason to erase the array.

I realize now that my confusion is more basic than arrays and the Erase statement - this simple fragment prints False:

Dim o1 As New cTest

Set o1 = Nothing

Debug.Print o1 Is Nothing

Edit> Duh! o1 Is Nothing IS a reference that causes re-instantiation. Erase probably does set the elements to Nothing, but there's no way to tell without referencing. That rules out using Class_Initialize to set a IsSomething flag, since looking at the flag would force it to True. I think Set as New should make it workable, and I can also try dim as object, then setting to a "cNull" class (instead of Nothing) so even if the instance persists I can use If TypeOf oVar Is cNull

You can count how many objects have been instansized by putting code like this in a class module

' in code module for Class1

Public Name As String

Private Sub Class_Initialize()
    With Range("A1")
        .Value = Val(.Value) + 1
    End With
End Sub

Private Sub Class_Terminate()
    With Range("A1")
        .Value = Val(.Value) - 1
    End With
End Sub

Then when you run this code, I see that when an array of objects is delcared with the New keyword the elements of that array aren't instansized until the particular element is referenced.

Sub test()
    Dim oArray(1 To 5) As New Class1
    
    Range("A1").Value = 0: Rem intialize instance counter
    
    Debug.Print Range("A1").Value & " start"
    
    Debug.Print oArray(1) Is Nothing
    
    Debug.Print Range("A1").Value & " a"
    
    Erase oArray
    
    Debug.Print Range("A1").Value & "b"
    
    Debug.Print oArray(1) Is Nothing
    
    Debug.Print Range("A1").Value & " c"
End Sub

This confusion is probably why it is considered best practice to not use New in declaration statements, but to explicitly instansize each object. In this case each element of the array.

Well when I posted this this morning I thought I had figured out enough about objects to ask a semi-intelligent question, but now I think I need to put it on hold until I can spend another couple of hours experimenting. For one thing, I see now that once DIM as NEW is used, ANY reference to the object creates an instance - even a simple Get reference after Set = Nothing recreates an instance. As I get a better understanding of the behavior I think I'll be able to avoid the pitfalls. Thanks for the help...

Still not sure exactly what you are trying to achieve. Why would you not know if Erase had been used? And why would you care.

Perhaps rather than an array you could use a collection of objects?

Originally Posted by Andy Pope

Still not sure exactly what you are trying to achieve. Why would you not know if Erase had been used? And why would you care.

Perhaps rather than an array you could use a collection of objects?

The array contains "min/max" objects in various categories for multiple data sets. I erase it to start each new set, then have to compare each new object to ones already in the array, possibly replacing the array element. If the array element is some equivalent of "nothing" the normal compare won't work - I need to detect and handle that separately.

A dictionary would work - I could devise a key for the min and max in each category, dict.removeall, then if not dict.exists add it, otherwise compare and replace. Knowing what I know now about object arrays, that does seem cleaner...