How to Define and Use a Dynamic Array
 

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?
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

How to Define and Use a Dynamic Array
 

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,
--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"Chaplain Doug" wrote:

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?
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

How to Define and Use a Dynamic Array
 

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??
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org


"Pranav Vaidya" wrote:

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,
--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"Chaplain Doug" wrote:

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?
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

How to Define and Use a Dynamic Array
 

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,
--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"Chaplain Doug" wrote:

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??
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org


"Pranav Vaidya" wrote:

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,
--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"Chaplain Doug" wrote:

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?
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

How to Define and Use a Dynamic Array
 

Thank you.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org


"Pranav Vaidya" wrote:

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,
--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"Chaplain Doug" wrote:

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??
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org


"Pranav Vaidya" wrote:

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,
--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"Chaplain Doug" wrote:

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?
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

How to Define and Use a Dynamic Array
 

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


Chaplain Doug wrote:
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?