I posted earlier that you might be interested in Harlan Grove's array
resizing code, "aresize", which he has described as follows:
'General Array Sizing and Resizing Procedures
'Copyright (C) 2003, Harlan Grove
'This is free software. It's use in derivative works is covered
'under the terms of the Free Software Foundation's GPL. See
'http://www.gnu.org/copyleft/gpl.html
Function aresize(A As Variant, ParamArray D() As Variant) As Long
aresize returns an exit status: 0 means successful completion in
which case its array argument is modified resized) as a side-effect;
other return values indicate error conditions left to the calling
procedure to handle; 'impossible' conditions throw exceptions, also
left to the calling procedure to trap and
handle user may pass a variable number of new array dimensions as
subsequent arguments following the array argument; missing dimensions
indicate no change in that dimension; extra dimensions extend the
rank of array, dimensions <= 0 colapse array along that dimension
Given a() = (((111,112),(121,122),(131,132)),
((211,212),(221,222),(231,232)),
((311,312),(321,322),(331,332)),
((411,412),(421,422),(431,432)))
the call aresize(a, 3, 0) changes a() to
a() = ((111,112),(211,212),(311,312))
this is boring code - highly repetitious, but it has to be so
I really don't have much of a conception of what you are actually trying
to accomplish, but using the aresize function, the following seems to do
what your code seems to be trying to accomplish in your most recent
repost (although your posted code doesn't deal with summing all the data
in column 9, which your narrative said you're trying to do):
Private Sub Other()
Dim i As Integer
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim Array1() As Variant
Dim ArrayMax As Variant
ReDim Array1(1 To 9, 1 To 13, 1 To 5, 1 To 5, 1 To 5)
For A = 1 To 5
For B = 1 To 5
For C = 1 To 5
For D = 1 To 13
For E = 1 To 9
Array1(E, D, C, B, A) = Rnd()
Next E
Next D
aresize Array1, , , 0, 0, 0
ArrayMax = Application.Max(Application.Index(Array1, 0, 9))
aresize Array1, , , 5, 5, 5
Next C
Next B
Next A
Debug.Print ArrayMax, UBound(Array1, 5), Array1(9, 9, 1, 1, 1)
End Sub
If you can't otherwise find the aresize function, feel free to post
telling me how I should try to get a copy to you.
Alan Beban
ExcelMonkey < wrote:
I have a 5D array. I fill the array with values within 5 imbedded For
Next Loops. Assume I use the rnd() function to fill them for
simplicity. Once I have filled all the rows and columns in the first
two dimensions (i.e. rows and columns) I want to sum all the data in
column 9. The following is not working. It is giving me a Type Mismatch
Error for ArrayMax.
Private Sub Other()
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim Array1() As Variant
Dim ArrayMax As Variant
ReDim Array1(1 To 9, 1 To 13, 1 To 5, 1 To 5, 1 To 5)
For A = 1 To 5
For B = 1 To 5
For C = 1 To 5
For D = 1 To 13
For E = 1 To 9
Array1(E, D, C, B, A) = Rnd()
Next E
Next D
ArrayMax = Application.Max(Application.Index(Array1, 0, 9, 0, 0, 0))
Next C
Next B
Next A
End Sub
---
Message posted from http://www.ExcelForum.com/