MAX of Multidimensional Array

[ZZZ]

Hello,

Is there a way to use the MAX function on one dimension of
a multidimensional array declared in VBA? I guess the
simplest example would be a 2D array with an index number
in the first column and various numbers in column two that
you want to extract the max from.

Thanks
Zed

 

[keepitcool]

Zed,

As long as you stick to 2 dimensions you can use the index function,
with either row or column argument set to 0

Sub TestMax()
Dim i, j, arr&()
'2 dimensional
ReDim arr&(1 To 10, 1 To 20)
For i = 1 To UBound(arr, 1)
For j = 1 To UBound(arr, 2)
arr(i, j) = j + i * 100
Next: Next
With Application.WorksheetFunction
MsgBox _
"Max row 3:" & vbTab & .Max(.Index(arr, 3, 0)) & vbNewLine & _
"Max col 4:" & vbTab & .Max(.Index(arr, 0, 4))
End With

End Sub

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Tom Ogilvy]

Note that you are limited to arrays of 5461 elements. So for example

ReDim arr&(1 To 273, 1 To 20)

would work,

ReDim arr&(1 To 274, 1 To 20)

would not work.

In xl 2002 and later, I believe this restriction has been relaxed.