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

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


"ZZZ" wrote:

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

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.

--
Regards,
Tom Ogilvy

keepitcool wrote in message
...
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


"ZZZ" wrote:

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