Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
RePost: Summing column in 5D Array
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
RePost: Summing column in 5D Array
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
RePost: Summing column in 5D Array
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? --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
RePost: Summing column in 5D Array
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 < wrote: 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? --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
RePost: Summing column in 5D Array
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: .@#$% Take out the characters beore the "d" and after the "a". Thank-yo -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
RePost: Summing column in 5D Array
Done.
Alan Beban ExcelMonkey < wrote: I never thought that this would be so difficult. The hardest party about dealing with this many dimensions is trying to describe what it 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 the code. You can email me at: .@#$% Take out the characters beore the "d" and after the "a". Thank-you --- Message posted from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
RePost: Summing column in 5D Array
Allan did you have trouble sending Harlan Groves info to the emai
address I provided -- Message posted from http://www.ExcelForum.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
RePost: Summing column in 5D Array
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 < wrote: Allan did you have trouble sending Harlan Groves info to the email address I provided? --- Message posted from http://www.ExcelForum.com/ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
RePost: Summing column in 5D Array
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 -- Message posted from http://www.ExcelForum.com |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
RePost: Summing column in 5D Array
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 ExcelMonkey < wrote: Got todays email. Thank-you. Two quick questions. How many dimensions does this accomodate? How man rows does it accomodate? The example I 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 endless posts on this topic! --- Message posted from http://www.ExcelForum.com/ |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
RePost: Summing column in 5D Array
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex conditional summing - array COUNT works, array SUM gives#VALUE | Excel Worksheet Functions | |||
Repost of hide a column | Excel Discussion (Misc queries) | |||
Summing cells in array | Excel Worksheet Functions | |||
rEPOST: Summing clock in and out by date | Excel Worksheet Functions | |||
Repost: Summing clock in and out by date | Excel Worksheet Functions |