"Harlan Grove's resizing code",

  • Thread starter Thread starter ExcelMonkey
  • Start date Start date
E

ExcelMonkey

I just got my hands on Harlan Groves resizing code for arrays:

aresize()

My understanding of the code is that it allows you to change the siz
of an array without affecting the date within the array (I think).
was wondering if anyone can tell me what the limits of this functio
are. That is, how many dimensions can it take> How many rows can i
take? And any other limitation that I should know.

P.S. I am looking forward to using it
 
In a related thread, "RePost: Summing column in 5D array", you posted
the following code to try to illustrate what you are attempting.

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

I said that you might get there by replacing the ArrayMax line with

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

The aresize code accommodates up to 6 dimensions. I don't have any
comments on your other questions about it.

Alan Beban
 
There *is* a limitation on the lines of code I suggested below, at least
in xl versions up through xl2000 (and perhaps later as well, I don't
know), though it's not a limitation on the aresize code. I don't know
if it's relevant because I don't know how large your data will get to
be, but the Index function as used below will fail if the number of
elements of Array1 in the first two dimensions exceeds 5461.

This limitation can be avoided if the functions in the freely
downloadable file at http://home.pacbell.net/beban are available to your
workbook, a la

ArrayMax = Application.Max(ColumnVector(Array1, 9))

Alan Beban

Alan said:
In a related thread, "RePost: Summing column in 5D array", you posted
the following code to try to illustrate what you are attempting.

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

I said that you might get there by replacing the ArrayMax line with

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

The aresize code accommodates up to 6 dimensions. I don't have any
comments on your other questions about it.

Alan Beban
Click to expand...
 
based on the description of what you want to do, I don't see any need to
resize the array.

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
Dim MaxMachine()
Dim MaxCost()
Dim mxcost as double, mxMachineID as Long
Dim totCost as double
ReDim Array1(1 To 9, 1 To 13, 1 To 5, 1 To 5, 1 To 5)
Redim MaxMachineID(1 to 5, 1 to 5, 1 to 5)
Redim MaxCost(1 to 5, 1 to 5, 1 to 5)
For A = 1 To 5 ' for each trial
For B = 1 To 5 ' for each year
For C = 1 To 5 ' for each hour
' file the array for a specific run, year and hour
mxMachineID = -1
mxcost = 0
For E = 1 To 13 for each machine
Totcost = 0
For D = 1 To 8 ' for each cost element
Array1(E, D, C, B, A) = Rnd()
totcost = totcost + array1(E,D,C,B,A)
Next D ' next cost element
array(E,9,C,B,A) = TotCost
if totcost > mxcost then
mxcost = totcost
mxMachineID = E
End if
Next E ' next machine
MaxCost(C,B,A) = mxCost
MaxMachineID = mxMachine
Next C ' next hour
Next B ' next year
Next A ' next run

End Sub
 
There *is* a limitation on the lines of code I suggested below, at least
in xl versions up through xl2000 (and perhaps later as well, I don't
know), though it's not a limitation on the aresize code. I don't know
if it's relevant because I don't know how large your data will get to
be, but the Index function as used below will fail if the number of
elements of Array1 in the first two dimensions exceeds 5461.

This limitation can be avoided if the functions in the freely
downloadable file at http://home.pacbell.net/beban are available to your
workbook, a la
Click to expand...
...

If the goal is iterating over the subarray for which the 3rd of 5 dimensions
equals 9, using aresize to coerce to 2D followed by ColumnVector followed by
another aresize call to restore 5D is grossly wasteful of system resources. It'd
be simpler to use something like the following to find the max value in all
entries in the subarray Array1(.,.,9,.,.).


Dim d(1 To 6) As Long, i As Long, x As Variant, maxval As Variant

'one more dimension in d than in Array1
d(1) = 1
For i = 2 To 6
d(i) = d(i - 1) * UBound(Array1, i - 1) 'assuming Array1 is 1-based
Next i

i = 0
For Each x In Array1

i = i + 1

If Int(1 + ((i - 1) Mod d(4)) / d(3)) = 9 Then
If IsEmpty(maxval) Or x > maxval Then maxval = x
End If

Next x


This could be the core of a general purpose array slicing function, but I'm too
lazy right now to implement it. To create a subarray aa for which the 3rd of 5
dimensions is 9, define aa using

ReDim aa(1 To UBound(Array1, 1), 1 To UBound(Array1, 2), _
1 To UBound(Array1, 4), 1 To UBound(Array1, 5))

and replace the 'If IsEmpty...' statement in the code above with

aa(Int(1 + ((i - 1) Mod d(2)) / d(1)), Int(1 + ((i - 1) Mod d(3)) / d(2)), _
Int(1 + ((i - 1) Mod d(5)) / d(4)), Int(1 + ((i - 1) Mod d(6)) / d(5))) = x

While d(1) and d(6) are technically unnecessary, they simplify iterative
techniques for generating the indices of aa as you iterate through Array1 using
For Each.
 
I just got my hands on Harlan Groves resizing code for arrays:

aresize()

My understanding of the code is that it allows you to change the size
of an array without affecting the date within the array (I think). I
was wondering if anyone can tell me what the limits of this function
are. That is, how many dimensions can it take> How many rows can it
take? And any other limitation that I should know.
Click to expand...

The only people of whom I'm aware have seen this code are Alan, myself and
anyone with whom Alan may have shared it. FWLIW, it's avalable at

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

As for its limits, it should be able to handle anything VBA itself can handle.
When in doubt, test it and see for yourself.
 
Back
Top