VBA array (ReDim Preserve & Indexing)

Question

As a beginner of VBA, I am confused with the following issue:

Dim DynArray() As Double 
ReDim DynArray(0 To 0)
DynArray(0) = 100
ReDim Preserve DynArray(5 To 5)  'subscript out of range
DynArray(5) = 100

why it doesn't work? and how to change the upper bound and/or lower bound of an array in VBA while retaining the values of the original array? Thank you.

Given what @Leviathan already explained, maybe you can explain your goal since from your code it doesn't seem necessary to change the lower bound — user3598756
– user3598756, Commented May 8, 2016 at 9:54
As far as I know the lower bound or any VBA array can only be 0 or 1: msdn.microsoft.com/en-us/library/aa266179(v=vs.60).aspx — Ralph
– Ralph, Commented May 8, 2016 at 11:24
@Ralph: That's only true for the Option Base statement that globally (for that module) defines the default lower bound of an array so you can declare an array with Dim a(5) with the 5 being the upper bound and the lower bound being 0 or whatever you set Option Base to. To whatever that is set, you can always declare your array explicitly with another lower bound (as the OP did), arbitrarily high. — tyg
– tyg, Commented May 8, 2016 at 13:53

tyg · Accepted Answer · 2016-05-08 09:04:06Z

3

The documentation states that

when you use Preserve, you can change the size of the array only by changing the upper bound; changing the lower bound causes an error.

Meaning: you can only do

ReDim Preserve DynArray(0 To 5)

answered May 8, 2016 at 9:04

tyg

20.8k5 gold badges46 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ani Menon Over a year ago

Good short answer. I have added another answer just to add a few details.

angelbj55 Over a year ago

Thank you very much! It's helpful

Ani Menon · Accepted Answer · 2016-05-08 09:54:52Z

The above answer is correct but I just wanted to add a few details.

If you use the Preserve keyword, you can resize only the last array dimension and you can't change the number of dimensions at all. For example, if your array has only one dimension, you can resize that dimension because it is the last and only dimension. However, if your array has two or more dimensions, you can change the size of only the last dimension and still preserve the contents of the array. The following example shows how you can increase the size of the last dimension of a dynamic array without erasing any existing data contained in the array.

From the documentation :

This example uses the ReDim statement to allocate and reallocate storage space for dynamic-array variables. It assumes the Option Base is 1.

Dim MyArray() As Integer ' Declare dynamic array. 
Redim MyArray(5) ' Allocate 5 elements. 
For I = 1 To 5 ' Loop 5 times. 
 MyArray(I) = I ' Initialize array. 
Next I

The next statement resizes the array and erases the elements.

Redim MyArray(10) ' Resize to 10 elements. 
For I = 1 To 10 ' Loop 10 times. 
 MyArray(I) = I ' Initialize array. 
Next I

The following statement resizes the array but does not erase elements.

                  Redim Preserve MyArray(15) ' Resize to 15 elements.

So your mistake : ReDim Preserve DynArray(5 To 5) where you changed the lower bound.

Collectives™ on Stack Overflow

VBA array (ReDim Preserve & Indexing)

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related