Variant-type object array element is deallocated by With statement

[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