| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I have a cell value define a dynamic named range? | Excel Worksheet Functions | |||
| Define Dynamic Name | Excel Discussion (Misc queries) | |||
| dynamic search in the Define Name window | Excel Discussion (Misc queries) | |||
| Define a variable for dynamic chart | Excel Programming | |||
| Define a Dynamic Range Based on an Index | Excel Programming | |||
Linear Mode
