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?

Wein

2, 3: No, reproduced on as far as Excel 2003 32-bit. – Exchangeable

Your repro can be simplified greatly, too. You don't need the static, or the array to hold objects. ReDim VariantObjArray(1 To 3), set a number to each element, touch an element with With without having anything inside of the with, and it becomes Empty instead of the number. pastebin.com/0SJFELZk – Exchangeable

Does not happen if VariantObjArray wraps a strongly typed object array (VariantObjArray = some_other_non_variant_array). Only happens when dealing with native Variant arrays that started as such. Yes, looks like a (yet another) compiler bug where the source Variant is incorrectly released by the With block, rather than the captured copy (which may be because the capturing is erroneously skipped in the first place). – Exchangeable

I'd warmly recommend systematically extracting loop bodies into their own procedure scope (and passing parameters ByVal) - pretty sure it fixes this (can't test right now) – Ridotto

@MathieuGuindon It surely does, just like assigning to a bogus SomeWkSh As Object which the OP is already mentioning as a workaround. – Exchangeable

Yeah.. IMO that (loop bodies into their own scope) should be considered a best practice anyway, compiler bug or not! "Rule of thumb, it’s always good idea to pull the body of a loop into its own parameterized procedure scope. Arrow-shaped code gets flattened, line count gets lower, and procedures become more specialized and have fewer reasons to fail that way." (from my blog) – Ridotto

@Exchangeable (#2): Yeah, I kept those details as a vestige of the real code, which actually uses a persistent global variable for the array. I use the Variant-type declaration and the IsEmpty function as a simple way to avoid re-initializing an already initialized array, without having to resort to error trapping on a test of LBound, etc. Kept those details in my example code in order to avoid suggestions to use strong typing (as Worksheet object) instead of the Variant. But you're right, of course. None of that is necessary to actually demo the bug. – Wein

@Mathieu Guindon (#1,2): for some algorithms, your suggestion makes perfect sense but for others, not so much. A high degree of algorithm fragmentation, by pushing details into sub-procedures can interfere with an understanding of the overall algorithm's purpose and mechanism, and can actually increase the likelihood of inducing bugs through future naive modifications. My experience is that this sort of question is one of the ways in which good programming becomes something of an art, not so much a prescriptive algorithm itself. – Wein

Aye, hence "rule of thumb" ;-) – Ridotto

ReDim VariantObjArray(1 To ThisWorkbook.Worksheets.Count) As Object – Rizika

@Cristian Buse: yes, that approach works around the bug but induces exactly the problem that my original design avoids: that there is no clean, simple way to determine whether a non-Variant array is already initialized without resorting to an error trap while testing its LBound value. Easy, simple techniques are one of the main points of the Variant data type and its corresponding IsEmpty function. This bug muddies what would otherwise be an easy, elegant solution. – Wein

@Wein Have you tested? The IsEmpty still works properly. You still keep Static VariantObjArray As Variant while you just update the ReDim like I mentioned. No error trap needed. – Rizika

@Cristian Buse: Right you are! Fabulous. Apologizes for making bad assumptions without actually testing your workaround. I owe you a beer next time I'm in London. However, the underlying issue is still a bug, and one for which your clean workaround isn't obvious. Are you listening Microsoft? (Yeah, that's a decades-old fantasy.) – Wein

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

Wein