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

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

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

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

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.

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.