Variant-type object array element is deallocated by With statement

Question

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 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?

Answer 1

A much cleaner workaround for the bug is provided by Cristian Buse (see comments above). Here's my demo code with his workaround:

Sub DemoVariantObjArrayBug()
    Dim i As Integer
    Dim NextWkSh As Worksheet
    Static VariantObjArray As Variant 'NOTE: can't declare it as "Static VariantObjArray() As Variant"!

    If IsEmpty(VariantObjArray) Then 'Check to avoid unnecessary re-allocation of static or global array variable
    
        '*** The bug workaround is to, instead of this naive ReDim that sets the stage for the bug...
        ReDim VariantObjArray(1 To ThisWorkbook.Worksheets.Count)
        
        '*** ...use Cristian Buse's workaround which explicitly defines the array elements' type via the ReDim:
'        ReDim VariantObjArray(1 To ThisWorkbook.Worksheets.Count) As Worksheet

        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 naively re-dimensioning the array as
                                   'an (implicit) Variant, above, re-dimension it as a specific type (Worksheet).
                                   
                Debug.Print """" & .Name & """: CodeName = " & .CodeName & ", Index = " & .Index
            End With
    Next i
End Sub

And a subtle detail is that, in order to compile, the initial declaration of the array must be as a single Variant:

Static VariantObjArray As Variant

Not a variant array:

Static VariantObjArray() As Variant