Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maximum number of variables allowed in Excel VBA
I am receiving a "Run-time error '9': Subscript out of range" after declaring
and using a block of array variables. This recently started when I expanded from 1508 to 1593 "Public" array variables with a total of around 2000 total variables (arrays and other types) in the operations model. I tried to change the "Double" variables to "Single" thinking it was a memory issue, no change. Tried on multiple machines and in Excel 2000 and 2003 with the same results. The variable that the error refers back to is the first array variable to be set and has worked without a problem until now. What is the maximum number of arrays allowed within VBA? Is there any setting within Excel that can be changed to fix this? Does 2007 allow for more variables? Thanks, James |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maximum number of variables allowed in Excel VBA
Subscript out of range would normally indicate that VBA cannot find the
object your code is telling it to look for. The array storage is based on your system's memory capability. "j17stang66" wrote: I am receiving a "Run-time error '9': Subscript out of range" after declaring and using a block of array variables. This recently started when I expanded from 1508 to 1593 "Public" array variables with a total of around 2000 total variables (arrays and other types) in the operations model. I tried to change the "Double" variables to "Single" thinking it was a memory issue, no change. Tried on multiple machines and in Excel 2000 and 2003 with the same results. The variable that the error refers back to is the first array variable to be set and has worked without a problem until now. What is the maximum number of arrays allowed within VBA? Is there any setting within Excel that can be changed to fix this? Does 2007 allow for more variables? Thanks, James |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maximum number of variables allowed in Excel VBA
I was running the VBA program on a machine with 1 GB of memory running Excel
2000. Tried to run it on a machine with 4 GB of memory running Excel 2000 with the same result. I also tried on a machine with 2 GB of memory running Excel 2003 and got the same error. The array that is being flagged as causing the error is not refereneced by the subroutines that were added related to the additional arrays. Any other thoughts? Any idea would be appreciated. "JLGWhiz" wrote: Subscript out of range would normally indicate that VBA cannot find the object your code is telling it to look for. The array storage is based on your system's memory capability. "j17stang66" wrote: I am receiving a "Run-time error '9': Subscript out of range" after declaring and using a block of array variables. This recently started when I expanded from 1508 to 1593 "Public" array variables with a total of around 2000 total variables (arrays and other types) in the operations model. I tried to change the "Double" variables to "Single" thinking it was a memory issue, no change. Tried on multiple machines and in Excel 2000 and 2003 with the same results. The variable that the error refers back to is the first array variable to be set and has worked without a problem until now. What is the maximum number of arrays allowed within VBA? Is there any setting within Excel that can be changed to fix this? Does 2007 allow for more variables? Thanks, James |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maximum number of variables allowed in Excel VBA
I commented out a similar sized block of array variables for a different
series of operations. The model ran fine with the new block of variables. This further leads me to believe that there is some numeric limitation within Excel's VBA construct. Any ideas for a work around? "j17stang66" wrote: I was running the VBA program on a machine with 1 GB of memory running Excel 2000. Tried to run it on a machine with 4 GB of memory running Excel 2000 with the same result. I also tried on a machine with 2 GB of memory running Excel 2003 and got the same error. The array that is being flagged as causing the error is not refereneced by the subroutines that were added related to the additional arrays. Any other thoughts? Any idea would be appreciated. "JLGWhiz" wrote: Subscript out of range would normally indicate that VBA cannot find the object your code is telling it to look for. The array storage is based on your system's memory capability. "j17stang66" wrote: I am receiving a "Run-time error '9': Subscript out of range" after declaring and using a block of array variables. This recently started when I expanded from 1508 to 1593 "Public" array variables with a total of around 2000 total variables (arrays and other types) in the operations model. I tried to change the "Double" variables to "Single" thinking it was a memory issue, no change. Tried on multiple machines and in Excel 2000 and 2003 with the same results. The variable that the error refers back to is the first array variable to be set and has worked without a problem until now. What is the maximum number of arrays allowed within VBA? Is there any setting within Excel that can be changed to fix this? Does 2007 allow for more variables? Thanks, James |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maximum number of variables allowed in Excel VBA
j17stang66, Esq.
"Subscript out of range ......" ; it may mean that for an array sized 100, a reference has been attempted outside that range of 100. Please re-examine your code that causes the exception. Regards. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maximum number of variables allowed in Excel VBA
I don't think that the error is caused by a wrong reference. The array size
is a 1x12 (1 to 12) representing months and is filled with For loops with an integer count mnI from 1 to 12. I think the error message is a symptom of something else, please see initial message. " wrote: j17stang66, Esq. "Subscript out of range ......" ; it may mean that for an array sized 100, a reference has been attempted outside that range of 100. Please re-examine your code that causes the exception. Regards. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maximum number of variables allowed in Excel VBA
I have also tried copying the workbook information and macros into a new
workbook incase the original workbook was corrupted. This didn't help. Also, tried copying the macro text into a text file and bringing them back into a new workbook. Same results. Any other suggestions to try? I would prefer to keep the code construct as is and am willing to try other suggested fixes. "j17stang66" wrote: I don't think that the error is caused by a wrong reference. The array size is a 1x12 (1 to 12) representing months and is filled with For loops with an integer count mnI from 1 to 12. I think the error message is a symptom of something else, please see initial message. " wrote: j17stang66, Esq. "Subscript out of range ......" ; it may mean that for an array sized 100, a reference has been attempted outside that range of 100. Please re-examine your code that causes the exception. Regards. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maximum number of variables allowed in Excel VBA
Playing around with the sizes of my arrays I got an error message that made
sense. I was defining all of my Public variables in one module, which VBA didn't like because it can only handle 64k worth of declarations in any given module. So, I split up my declarations into 4 modules and things seem to be working. "Dana DeLouis" wrote: I don't think that the error is caused by a wrong reference. The array size is a 1x12 (1 to 12) representing months and is filled with For loops with an integer count mnI from 1 to 12. If you are having a problem with a size 12 array, one technique is to step thru your code (hit F8) and pull up your "Locals Window" to view your variables and Arrays. This is one debugging technique. Perhaps post a short segment of your code. -- Dana DeLouis "j17stang66" wrote in message ... I don't think that the error is caused by a wrong reference. The array size is a 1x12 (1 to 12) representing months and is filled with For loops with an integer count mnI from 1 to 12. I think the error message is a symptom of something else, please see initial message. " wrote: j17stang66, Esq. "Subscript out of range ......" ; it may mean that for an array sized 100, a reference has been attempted outside that range of 100. Please re-examine your code that causes the exception. Regards. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maximum number of variables allowed in an Excel Spreadsheet? | Excel Discussion (Misc queries) | |||
changing maximum characters allowed in excel cells | Excel Discussion (Misc queries) | |||
What is the maximum allowed number of data elements in a data array? | Excel Discussion (Misc queries) | |||
LINEST maximum number of predictor variables | Excel Worksheet Functions | |||
Is there a maximum number of Add-ins allowed? - strange Excel behaviour | Excel Programming |