Min/Max values in multi-dimensional array

[bergy]

Is there a VBA function that returns the maximum or minimum value of an array? How about the same for a multi-dimensional array?

I suppose I could write the fuction, but it is Friday and I am feeling a little lazy.

Thanks,

Bergy

 

[Barry Katcher]

If, by "multi-dimensional array" you mean pulling from several non-contiguous ranges, see below. Can be from multi sheets.

Book1
	A	B	C	D	E
1
2		1
3		7
4		4		99
5		3		44
6		4		66
7		8		22
8		9		88
9				55
10
11		99	-1
12		Max	Min
13
Sheet2

 

[bergy]

I am sorry, I guess I should have been more clear. I have a 2 dimensional, 4 x 120 array that I have filled with values, ie.:

MyArray(0 to 3, 0 to 119) as Integer

I need to find the maximum and minimum of each dimension. This array exists totally in VBA, not on a spreadsheet.

 

[XL-Dennis]

Hi,

You may play around with following procedure. Here I use a range of data to populate the array but it can easily be substituted with another array of values.

Option Explicit

Sub Find_Max_Value()
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnData As Range
Dim vaData As Variant
Dim i As Long, j As Long, lnMax As Long, lnMin As Long

Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets("Blad1")

With wsSheet
Set rnData = .Range("A1:B2")
End With

vaData = rnData.Value

lnMin = vaData(1, 1)
lnMax = vaData(1, 1)

For j = 1 To 2
For i = LBound(vaData) To UBound(vaData)
Debug.Print vaData(i, j)
lnMin = IIf(vaData(i, j) > lnMin, lnMin, vaData(i, j))
lnMax = IIf(vaData(i, j) > lnMax, vaData(i, j), lnMax)
Next i
Next j

MsgBox lnMin & " " & lnMax

End Sub


Kind regards,
Dennis

 

[bergy]

Thank you Dennis, it worked great.

 

[NateO]

Nice code Dennis. :wink:

Also, as a tack-on, as we're not approaching Excel's 5461 element limitation (removed in XP ), we can also use the Excel Object's min and max functions to get at the min and max values. Here's a few examples:

<font face=Courier New><SPAN style="color:darkblue">Option</SPAN> <SPAN style="color:darkblue">Explicit</SPAN>

<SPAN style="color:darkblue">Sub</SPAN> Mn_MX_Value()
<SPAN style="color:darkblue">Dim</SPAN> vaData, LnMax <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Long</SPAN>, lnMin <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Long</SPAN>, tmpAr() <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Integer</SPAN>, MyDim <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Integer</SPAN>
<SPAN style="color:darkblue">Dim</SPAN> i <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Integer</SPAN>
vaData = ThisWorkbook.Sheets(1).[A1:c10].Value
<SPAN style="color:darkblue">ReDim</SPAN> tmpAr(1 <SPAN style="color:darkblue">To</SPAN> <SPAN style="color:darkblue">UBound</SPAN>(vaData))
MyDim = 1 <SPAN style="color:green"><SPAN style="color:green">'Desired Dimension, 1 to 3</SPAN></SPAN>
<SPAN style="color:darkblue">For</SPAN> i = 1 <SPAN style="color:darkblue">To</SPAN> <SPAN style="color:darkblue">UBound</SPAN>(vaData)
    tmpAr(i) = vaData(i, MyDim)
<SPAN style="color:darkblue">Next</SPAN>
lnMin = Application.Min(tmpAr)
LnMax = Application.Max(tmpAr)
MsgBox lnMin & " " & LnMax
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN>

<SPAN style="color:darkblue">Sub</SPAN> test()
<SPAN style="color:darkblue">Dim</SPAN> MyArray(0 <SPAN style="color:darkblue">To</SPAN> 3, 0 <SPAN style="color:darkblue">To</SPAN> 119) <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Integer</SPAN>, i <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Integer</SPAN>
<SPAN style="color:darkblue">Dim</SPAN> lnMin <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Long</SPAN>, LnMax <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Long</SPAN>, tmp(0 <SPAN style="color:darkblue">To</SPAN> 119) <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Integer</SPAN>, MyDim <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Integer</SPAN>
<SPAN style="color:darkblue">For</SPAN> i = 0 <SPAN style="color:darkblue">To</SPAN> 119
    MyArray(0, i) = i - 2: MyArray(1, i) = i * 2 + 5
    MyArray(2, i) = i * 3 + 1: MyArray(3, i) = i * 4 - 3
<SPAN style="color:darkblue">Next</SPAN>
MyDim = 0 <SPAN style="color:green"><SPAN style="color:green">'Desired Dimension, 0 to 3</SPAN></SPAN>
<SPAN style="color:darkblue">For</SPAN> i = 0 <SPAN style="color:darkblue">To</SPAN> 119
    tmp(i) = MyArray(MyDim, i)
<SPAN style="color:darkblue">Next</SPAN>
lnMin = Application.Min(tmp)
LnMax = Application.Max(tmp)
MsgBox lnMin & " " & LnMax
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

Have nice day.

 

[XL-Dennis]

Hey Nate :D

Thanks for the feedback.

Your solution gave me the following idea:

Option Explicit
Option Base 1

Sub Find_Max_Min_Value()
Dim lnData(1 To 2, 1 To 2) As Long

lnData(1, 1) = 2
lnData(2, 1) = 4
lnData(1, 2) = 5
lnData(2, 2) = 1

With Application.WorksheetFunction
Debug.Print "Max: " & .Max(lnData)
Debug.Print "Min: " & .Min(lnData)
End With

End Sub



Kind regards,
Dennis