Return the Maximum Value in an Array

[michaeldh]

Hi Guy's,

How can I return the maximum value which is within a VBA array of values. Is there a command that can achieve this or do I have to test each value in the array?

Thanks for your help.

Michael.

 

[smozgur]

myArray is your array variable name.

MaxVal = Application.WorksheetFunction.Max(myArray)

May be helps.

Suat

 

[smozgur]

Sorry, I often forget explaining "How&Why" part.

Excel worksheet functions can be used in VBA codes with Application.WorksheetFunction property.

We use MAX() worksheet function to find the maximum value in range parameter. Because every range object is also an array object then we can use same function (MAX()) for VBA arrays which we created in our code by giving array name as parameter.

I hope this helps, too.

Suat

 

[Jay Petrulis]

Hi,

Be careful when applying the WorksheetFunction.Max to an array with more than 65536 elements.

I don't know where (if) it is explicitly documented, but it appears that the maximum number of elements in any one dimension that can handle the worksheetfunction operators is the number of rows -- the entire column.

Try the following with various numbers...<pre>Sub test()
Dim MyArr, x

ReDim MyArr(1 To 75000)
For x = 1 To 75000
MyArr(x) = x
Next x

MsgBox Application.Max(MyArr)
End Sub</pre>

If you do have a large data set, either redim the array for two or more dimensions or test in a loop.

_________________
Bye,
Jay
This message was edited by Jay Petrulis on 2002-12-27 11:28