Variant-type object array element is deallocated by With statement

[pstraton]

If an object-array is declared as a Variant type (in order to easily check whether it is initialized using the IsEmpty function) then, if the subsequently defined array's elements are referenced as the object-expression of a With statement (e.g. "With VariantObjArray(i) ...") then that object-variable array element will be erroneously deallocated (although the With statement's implicit copy of the object variable will function correctly for the single subsequent execution-pass through the scope of the With statement).
Furthermore, the erroneous deallocation of the array-element object variable may be a memory leak given that it occurs immediately upon the execution of the With expression, not as the result of any standard deallocation mechanism such as exiting the With statement scope or returning from the subroutine or being explicitly set to Nothing.

Sub DemoVariantObjArrayBug()
    Dim i As Integer
    Dim NextWkSh As Worksheet
    Static VariantObjArray As Variant

    If IsEmpty(VariantObjArray) Then 'Check to avoid unnecessary re-allocation of static or global array variable
        ReDim VariantObjArray(1 To ThisWorkbook.Worksheets.Count)
        For Each NextWkSh In ThisWorkbook.Worksheets
            i = i + 1: Set VariantObjArray(i) = ThisWorkbook.Worksheets(i)
        Next NextWkSh
    End If
    
Stop 'and, to observe the bug, open the Locals window, expand its view of VariantObjArray, single step through
     'the following code and observe each VariantObjArray element being deallocated with each cycle's execution
     'of the With statement:
      
    For i = LBound(VariantObjArray) To UBound(VariantObjArray)
           With VariantObjArray(i) 'The bug workaround is to, instead of this, do something like the following...
'            Dim SomeWkSh As Object: Set SomeWkSh = VariantObjArray(i)
'            With SomeWkSh
                Debug.Print """" & .Name & """: CodeName = " & .CodeName & ", Index = " & .Index
            End With
    Next i
End Sub

A workaround is to explicitly use an intermediary object variable as illustrated by the alternate (initially commented) code, above.
My questions are:

1. I have been unable to find any web chatter regarding this bug, so is it truly a bug that no one has run into until now?
2. Or is it a new bug, introduced recently, including my current version of Excel, Microsoft 365 MSO (16.0.14326.21052) 64-bit?
3. Is it peculiar to 64-bit Office?
4. Is it actually an allocated-object memory leak or is it just an object-pointer loss?

 

[Domenic]

I have 32-bit Office, and get the exact same behaviour.

I too tried searching Google, but couldn't find anything regarding this issue.

Before the With/End With statement, Debug.Pring ObjPtr(VariantObjArray(1) ) returns a pointer to the object. Afterwards, a Run-time error '424': Object Required occurs.

It seems to me that you have a good workaround.

The alternative would be to declare VariantObjArray as a dynamic array, instead of a Variant. And then re-allocate accordingly. Then use the boolean function IsArrayAllocated to determine whether the array has been allocated, as described in this article.

Sub test()
    Dim i As Integer
    Dim NextWkSh As Worksheet
    Static arr() As Worksheet

    If Not IsArrayAllocated(arr) Then 'Check to avoid unnecessary re-allocation of static or global array variable
        ReDim arr(1 To ThisWorkbook.Worksheets.Count)
        For Each NextWkSh In ThisWorkbook.Worksheets
            i = i + 1: Set arr(i) = ThisWorkbook.Worksheets(i)
        Next NextWkSh
    End If
    
    For i = LBound(arr) To UBound(arr)
           With arr(i)
                Debug.Print """" & .Name & """: CodeName = " & .CodeName & ", Index = " & .Index
            End With
    Next i
End Sub

Function IsArrayAllocated(arr As Variant) As Boolean
        On Error Resume Next
        IsArrayAllocated = IsArray(arr) And _
                           Not IsError(LBound(arr, 1)) And _
                           LBound(arr, 1) <= UBound(arr, 1)
End Function

Hope this helps!

 

[NdNoviceHlp]

pstraton please review the forum etiquette and post links to all other sites where U have posted this query. Thanks. Dave