Array Dimensioning

[BillCPA]

Is there a limit on how many dimensions an array can have? VB Help seems to
indicate it is only limited by memory (I have a 4GB machine), and even then
actually has no limit because data is written to and read from disc as needed.

I cannot get an array of larger than four dimensions to work (I want seven).
I have it defined as dynamic. The code will initially ReDim the array
without showing any error. It will allow me to assign values to it. But if
I am monitoring the code and look at the array after a value has been
assigned, it indicates 'subscript out of range', even though the index values
for each dimension are well within the values at the time of the ReDim.

I reduced the number of dimensions down by one from 7, and when I got to
four, it worked.

Any thoughts?

 

[GeoffG]

Is there a limit on how many dimensions an array can have?

You can have 7 dimensions.

The following code demos 7 dimensions.
The 7th dimension is redimmed and the data is preserved.

Suggest you use Debug.Print with the LBound() and UBound()
functions (as in following demo) to determine what subscripts are
valid in each dimension just before your subscript-out-of-range
error occurs.


Private Sub TestArray()

Dim S() As String

' Redimension array:
ReDim S(1, 2, 3, 4, 5, 6, 7)

' Show lower and upper bounds of 7th dimension:
Debug.Print "7th dimension LBound:", LBound(S, 7)
Debug.Print "7th dimension UBound:", UBound(S, 7)

' Store A:
S(1, 2, 3, 4, 5, 6, 7) = "A"

' Make final dimension bigger.
' Preserve only works when changing last dimension:
ReDim Preserve S(1, 2, 3, 4, 5, 6, 8)

' Show lower and upper bounds of 7th dimension:
Debug.Print "7th dimension LBound:", LBound(S, 7)
Debug.Print "7th dimension UBound:", UBound(S, 7)

' Store B:
S(1, 2, 3, 4, 5, 6, 8) = "B"

' Output A, B to Immediate window:
Debug.Print S(1, 2, 3, 4, 5, 6, 7)
Debug.Print S(1, 2, 3, 4, 5, 6, 8)

End Sub


Regards
Geoff

 

[GeoffG]

Is there a limit on how many dimensions an array can have?

The limit is 60 dimensions.

I am monitoring the code and look at the array after a value
has been assigned, it indicates 'subscript out of range', even
though the index values for each dimension are well within the
values at the time of the ReDim.

I reduced the number of dimensions down by one from 7, and when
I got to four, it worked.

Odd. Can you post your example?


A FEW OTHER THOUGHTS

1. It seems you may be indending to use a very large
multi-dimensional array. As you probably know, VBA will allocate
memory to the full size of the array regardless of whether you
use all array elements. As a result, a large multi-dimensional
array could prove very memory hungry. This is especially
significant if you declare an array of variants to store
different data types in each dimension.

2. Would it be possible to store the data in Microsoft Access?
Then, the above memory considerations won't apply; table
relationships can efficiently specify linked data; and fields can
be indexed and sorted easily and quickly. DAO can be used for
adding data to an Access database programmatically.

Geoff