Maximum number of variables allowed in Excel VBA

[j17stang66]

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

 

[JLGWhiz]

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]

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.

 

[j17stang66]

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?

 

[tkt_tang]

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.

 

[j17stang66]

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.

 

[j17stang66]

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]

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.