RePost: Summing column in 5D Array

[ExcelMonkey]

I have a 5D array. I fill the array with values within 5 imbedded Fo
Next Loops. Assume I use the rnd() function to fill them fo
simplicity. Once I have filled all the rows and columns in the firs
two dimensions (i.e. rows and columns) I want to sum all the data i
column 9. The following is not working. It is giving me a Type Mismatc
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 Su

 

[Alan Beban]

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]

Sorry for any ambiguity in my description. I will tell you exactly what
I am doing. I am looking at ouput costs for industrial equipment. The
pieces of equipment make up dimesion 1 (to 13). The second dimension
(i.e. the columns) are the different elements of costs associated with
each piece of equipment. Column 9 is the total costs column. The
remaining dimensions are time and stochastic variables. Dimension 3 is
hours, Dimension 4 is years and Dimension 5 is for stochastic runs
(i.e. 5 different runs)

Do I want in every hour to find out which piece of equipment has the
highest cost. I apologize I said "Summing" when I meant to say Maxing.
And I want to calculate this at the end of every hour. So when the
looping is completed, I will have the max equipment cost across 13
pieces of equipment. And since I do this every hour, I will be able to
look at this data across hours, years, and multiple stocahstic runs.
So my statement:

ArrayMax = Application.Max(Application.Index(Array1, 0, 9, 0, 0, 0))

was my attempt to record the highest value in column 9 before advancing
to the next hour loop. I will eventually store this value in another
array and when the run is completed I will have max cost data for
#hours*#years*stochastic runs.

Is that any clearer?

 

[Alan Beban]

Yes, it is clearer. And with Harlan Grove's resizing code available to
your workbook, I believe the code I last posted will accomplish the goal
of your post. It basically resizes (and preserves) the array to
eliminate the last 3 dimensions after the first 2 dimensions data have
been reloaded, calculates the maximum of Column 9 of the corresponding
(at that point) 2 dimensional array data (hence the typical
......Index(Array1,0,9) structure), then resizes (and preserves) the
array to 5 dimensions for the loops to complete the loading. At least
that's what I think it does, and that's what I think you want it to do.

So what's next?

Alan Beban

 

[ExcelMonkey]

I never thought that this would be so difficult. The hardest part
about dealing with this many dimensions is trying to describe what i
is I am trying to do with them.

I will give it a try. I guess the best thing to do is to send me th
code.

You can email me at:

@#$%[email protected].@#$%

Take out the characters beore the "d" and after the "a".


Thank-yo

 

[Alan Beban]

Done.

Alan Beban

 

[ExcelMonkey]

Allan did you have trouble sending Harlan Groves info to the emai
address I provided

 

[Alan Beban]

No. I sent it just before 3pm yesterday to the address with 11
characters followed by @ followed by 4 characters followed by .ca

I received no indication of delivery failure.

I will resend it within 5 minutes; it is now 11am PST.

Alan Beban

 

[ExcelMonkey]

Got todays email. Thank-you. Two quick questions. How many dimension
does this accomodate? How man rows does it accomodate? The example
gave was a simple version of what I am actually going to do.

Thank-you again Alan. Hopefully this will put an end to my endles
posts on this topic

 

[Alan Beban]

Harlan Grove is obviously the better person to answer (it accommodates
up to 6 dimensions). Perhaps if you repost your questions with a subject
"Harlan Grove's resizing code", he might be interested enough to reply.

Alan Beban

 

[bwamajac]

Hi,

I came across this post in searching the net for help with a simila
challenge (manipulating multi-dimensional arrays in VB to extract onl
certain elements- mostly into one-dimensional arrays- for furthe
calculations).

I think this aresize() function would be a big help to me.

If someone could forward it to my email:


@#$%[email protected].@#$%

(exclude the first four and last five characters, of course)

I would be very appreciative!

I'll post again to thank anyone who sends me the code.

Thanks

 

[Norman Jones]

Hi Bwamajac,

I believe that you can download Harlan Grove's aresize function at:

ftp://members.aol.com/hrlngrv/arrays.zip

 

[bwamajac]

I got it. Thanks