How to Define and Use a Dynamic Array

[Chaplain Doug]

Excel 2003 Profession. In my code I have the following:

Dim ProjPC() as Single

Later in the code I try to assign:

ProjNo=1
ProjPC(ProjNo)=15

I get the error:

Subscript out of range

WHAT am I doing wrong?

 

[Pranav Vaidya]

Hi Dr. Doug Pruiett,

You code is partially correct.
You are declaring the array correctly but not defining the size. You can do
it in 2 ways

method 1:

Dim ProjPC(1) as Single

Or
Redim Preserve ProjPC(1)
ProjPC(ProjNo)=15

You are getting this error as the array is declared but Excel dosen't know
the size of the array.
For more help you can check MS help in Excel on Declaring arrays.

HTH,

 

[Chaplain Doug]

Ixsnay. I truly want the array to be dynamic (the index could be from 1 to
any integer theoretically). Dim MyArray(1) does not accomplish that at all.
The help on declaring arrays says MyArray() defines a dynamic array. What
does Redim Preserve accomplish??

 

[Pranav Vaidya]

Hi,

Redim Preseve helps you to dynamically change the only Upper bound of your
array at runtime. In short it helps you in the scenario that you are looking
for. Whenever you think that you want to resize the array to hold more
elements you use 'Redim' statement. 'Preseve' ensures that there is no data
loss of the existing elements of the array, otherwise the whole array is
resized as if newly declared.

If you try to change the lower bound while using 'Preserve' you will get an
error.

HTH,

 

[Chaplain Doug]

Thank you.

 

[Alan Beban]

Dim ProjPC() As Single
ReDim ProjPC(1)
ProjNo = 1
ProjPC(ProjNo) = 15
ReDim Preserve ProjPC(2)
ProjNo = 2
ProjPC(ProjNo) = 30
etc.

Or

Dim ProjPC() As Single
Redim ProjPC(1 to 10)
ProjNo = 1
ProjPC(ProjNo) = 15
ProjNo = 2
ProjPC(ProjNo) = 30
Redim Preserve ProjPC(1 to 2)

Alan Beban