Redim Preserve Mystery - Excel Can't Redim Preserve array with 1

[apaivas]

Team,
After this last upgrade Microsoft pushed this past weekend, many of my workbooks stopped working when I go from Redim vArray(0 to 0) to Redim Preserve vArray(1 to 1) - Apparently 1 is skipped.
Simple Code 1 - Not working:

Private Sub ArrayTest1()
    Dim vArray As Variant
    ReDim vArray(0 To 0)
    ReDim Preserve vArray(1 To 1)
    MsgBox (UBound(vArray)) ' Returns 0?????
    ReDim Preserve vArray(1 To 2)
    MsgBox (UBound(vArray)) ' Returns 2
    ReDim Preserve vArray(1 To 3)
    MsgBox (UBound(vArray)) ' Returns 3
 End Sub

------
Simple Code 2 - Ugly Workaround that works

Option Explicit

Private Sub ArrayTest2()
    Dim vArray As Variant
    ReDim vArray(-1 To 0) 'Crazy but it works!!!!
    ReDim Preserve vArray(1 To 1)
    MsgBox (UBound(vArray)) ' Returns 1 - Check!
    ReDim Preserve vArray(1 To 2)
    MsgBox (UBound(vArray)) ' Returns 2 - Check!
    ReDim Preserve vArray(1 To 3)
    MsgBox (UBound(vArray)) ' Returns 3 - Check!
 End Sub

Can anybody tell what happened, I can spend a ton of time implementing the workaround, but what did Microsoft do? Why??
Thanks!!

 

[apaivas]

Team,

Apparently what I found "ugly" is the best practice for creating an empty (Zero-Length) array.

Thank to all for the replies, below is the link explaining, from the moment I read this article I decided that my workaround Dim myArray(-1 to 0) was no longer a work around.

Arrays in Visual Basic

 

[RoryA]

Just FYI, that article relates to .Net languages and some of it won't apply to VBA.

 

[mikerickson]

I also use Redim myArray(-1 To -1) as a "empty array" flag (similar to the return from Split).

But when I fill my first element, I Redim myArray(0 to 0) and Redim Preserve only on filled arrays.

Another approach that I sometimes use invokes a Pointer variable. Testing the Pointer variable to see if the array is empty.

Dim myArray() as String
Dim Pointer as Long
Dim myArray(0 to 0)

Pointer = 0

'...
If Ubound(myArray) < Pointer Then
    Redim Preserve myArray(0 to 2 * Pointer)
End If

myArray(Pointer) = "new value"
Pointer = Pointer + 1


'....
If 0 < Pointer then
    Redim Preserve myArray(0 to Pointer -1)
End If

 

[apaivas]

Mike - This is cool! Thanks for the hint!!!

Rory - You are correct, that article relates to .NET, but I had to use something to alleviate my bad feeling when I used the Dim myArray(-1 to 0) and that article did the job I am no longer shamefaced.

Thanks again,

Suny