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

[Paul_Hossler]

This seems to work, but I stay away from Static variables

Could you use a module level variable instead?

Option Explicit


Sub DemoVariantObjArrayBug()


    Dim i As Integer
    Dim NextWkSh As Worksheet


    Static VariantObjArray() As Object  '   <<<<


    If UBound(VariantObjArray) = -1 Then    '   <<<<
        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 


    For i = LBound(VariantObjArray) To UBound(VariantObjArray)
        With VariantObjArray(i) 
            Debug.Print """" & .Name & """: CodeName = " & .CodeName & ", Index = " & .Index
        End With
    Next i


End Sub

[arnelgp]

what is the purpose of the Exercise?
you are just "bloating" your memory by assigning all Excel worksheets to a static variable.
when in fact, you can just refer to the Worksheets collection without the extra code of
adding it a variable.

[snb]

Problem ?, Bug ? : no problem, no bug; only overcomplicated code.

Sub M_snb()
  ReDim sn(Sheets.Count - 1)
   
  For Each it In Sheets
    Set sn(n) = it
    n = n + 1
  Next
   
  For Each it In sn
    MsgBox Join(Array(it.Name, it.CodeName, it.Index), vbLf)
  Next
End Sub

[Paul_Hossler]

Still doesn't explain why the 'With' apparently deallocated the WS object in the array

[Aflatoon]

It does look like a bug to me. It also happens in my O2016 32 bit (16.0.5332.1000).

FWIW, using a For Each loop would also avoid it.

[pike]

Could explain the Nothing.
"If the New keyword isn't used when declaring an object variable, the variable that refers to the object must be assigned an existing object by using the Set statement before it can be used. Until it is assigned an object, the declared object variable has the special value Nothing, which indicates that it doesn't refer to any particular instance of an object. When you use the New keyword in the declaration, an instance of the object is created on the first reference to the object."
Static statement (VBA) | Microsoft Docs