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

apaivas

New Member
Joined
Nov 18, 2013
Messages
5
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:
Code:
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
Code:
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!!
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,779
Redim Preserve can only change the highest index of the last dimension.

Code:
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

New Member
Joined
Nov 18, 2013
Messages
5
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:
Code:
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,779
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

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

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

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,503
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

New Member
Joined
Nov 18, 2013
Messages
5
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).
Code:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,503
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

MrExcel MVP
Joined
May 14, 2003
Messages
4,920
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,503
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Jan 24, 2012
Messages
2,708
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,004
Messages
5,526,223
Members
409,688
Latest member
Mc Junior

This Week's Hot Topics

Top