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

 

[mikerickson]

Redim Preserve can only change the highest index of the last dimension.

Dim myArray()

ReDim myArray(1 to 10)
ReDim Preserve myArray(1 to 100) : Rem ok
ReDim Preserve myArray(2 to 10) : Rem no good

Redim myArray(1 to 10, 1 to 10): Rem ok (no preserve)
ReDim Preserve myArray(1 to 10, 1 to 100): Rem ok
ReDim Preserve myArray(1 to 20, 1 to 100): Rem no go

If you are going to use a 0 indexed element in an array, dimension it that what from the start.
You don't have to use that index, but its available for those situations where you need it.

 

[apaivas]

Thanks Mike, I am still confused on why everything was working until this past upgrade, and if something changed what would be the reason.

My arrays usually are dimensioned with 0 (0 to 0) than increment the arrays based upon whatever logic I am building.

So, I can understand your logic but why now I can't do a simple one dimension array code like this:

Dim myArray()
ReDim myArray(0 to 0)
ReDim Preserve myArray(1 to 1) : Rem not ok Ubound(myArray) returns Zero - before Microsoft upgrade was returning One
ReDim Preserve myArray(1 to 2) : Rem ok Ubound(myArray) returns 2

Thanks again for the quick turn around

 

[mikerickson]

Your Preserve statements are changing the LBound index.
The first Redim Preserve (if it worked), from 0to0 to 1to1 would do the same as Redim (1 to 1) with no Preserve.

I dont have time to test, but have you looked at what

ReDim myArray(0 to 10)
ReDim Preserve(2 to 10)

does? (Keep the new LBound < the old UBound)

 

[RoryA]

You are correct that something changed in a recent update. Although, as Mike says, your original code shouldn't have worked, strictly speaking, it did prior to the last round of updates. It has been reported.

 

[apaivas]

Rory, thanks for the input - I am using the ReDim myArray(-1 to 0) as an ugly workaround.. If you don't mind me asking, why you and Mike think the original code shouldn't have worked? I am spinning my wheels to understand if I "miss-used" arrays and what Microsoft did a "correction" that won't allow my "miss-use" anymore.
Below is a "quick and dirty" sample of how I have been using he (0 to 0).

Option Explicit

Private Sub ArrayTest1()
   Dim myArraySource As Variant
   Dim myArrayTarget As Variant
   Dim mySourceCounter1 As Integer
   Dim mySourceCounter2 As Integer
   Dim myTargetCounter1 As Integer
   
   myArraySource = Array(10, 20, 30, 40, 50)
   
   ReDim myArrayTarget(0 To 0) 'It wil work if changed to myArrayTarget(-1 To 0)

   mySourceCounter2 = UBound(myArraySource)
   
   For mySourceCounter1 = 1 To mySourceCounter2
       If myArraySource(mySourceCounter1) > 20 Then
          myTargetCounter1 = UBound(myArrayTarget) + 1
          ReDim Preserve myArrayTarget(1 To myTargetCounter1)
          myArrayTarget(myTargetCounter1) = myArraySource(mySourceCounter1)
       End If
   Next
   MsgBox UBound(myArrayTarget) & " Records added!"
End Sub

 

[RoryA]

According to the VBA specification, as Mike said, if you use Preserve when redimming an array, you can only change the upper boundary of the last dimension of the array. If you attempt to change the lower boundary of any dimension, or the upper boundary of any dimension besides the last one, you should receive an error.

In fact, up until very recently, you could still change the lower boundary of the last dimension once. After that, it wouldn't change but you also wouldn't get an actual error until you tried to access an element of the array that didn't exist.

 

[Jon Peltier]

Apaivas -

Your first line said
ReDim vArray(0 To 0)

Later in your code you always use 1 as the lower bound. So you should initially use
Dim vArray(1 To 1)

This way you are never needing to change the lower bound. Using (-1 to 0) is a funny way to try to get around this issue.

- - - - -

Rory: Until how recently could you Redim Preserve an array only once? I've been using it multiple times since at least Excel 2000, and probably Excel 97.

 

[RoryA]

Jon,

I misspoke - in too much of a rush.

I actually meant that up until recently you could change the lower boundary, whereas now you can only do it once - unless you change the number of items at the same time (as Kevin pointed out).

 

[Kyle123]

I am spinning my wheels to understand if I "miss-used" arrays and what Microsoft did a "correction" that won't allow my "miss-use" anymore.

In that example, yes you are, you should be using a collection. Redim Preserve is nasty at the best of times