VBA Max Function

An example using numbers in the Max function

We provided a few numbers to the Max() function and see the result what it returns in the message box:

Sub max_ex()

MsgBox "Maximum Number is: " & WorksheetFunction.Max(15, 25, 3, , 114, 7, 99, 101, 33)

End Sub

Output:

Using Long variables in the Max function

Sub max_ex()

Dim mx1 As Long, mx2 As Long, mx3 As Long

mx1 = 115
mx2 = 333
mx3 = 100

MsgBox "Variable with Maximum Value: " & WorksheetFunction.Max(mx1, mx2, mx3)

End Sub

Result:

Getting the maximum number of Single type (with floating numbers)

This example gets the maximum value from three Single type variables (with decimal values):

Sub max_ex()

Dim mx1 As Single, mx2 As Single, mx3 As Single

mx1 = 55.55
mx2 = 999.9
mx3 = 45.89

MsgBox "Max. Decimal Value: " & WorksheetFunction.Max(mx1, mx2, mx3)

End Sub

Result:

Using Excel cells in the Max() function

In this example, we will use a range of A1 to A10 cells to get the maximum value.

All cells contain numbers as shown after the code:

Sub max_ex()

Dim Rng As Range

Set Rng = Range("A1:A10")

MsgBox "Maximum Value in A1:A10 Cells = " & WorksheetFunction.Max(Rng)

End Sub

Output:

What if the cell contains currency values?

• We have a sheet that contains a Price column for demo only.
• We will get the maximum price in that column by using WorksheetFunction’s Max() function.
• The sheet and result are shown below:

Sub max_ex()

Dim Price_Rng As Range

Set Price_Rng = Range("C2:C10")

MsgBox "Maximum Price = " & WorksheetFunction.Max(Price_Rng)

End Sub

You can see, it returned the correct result with currency values in the column.

What if we use the text column in Max()

See the output below where we specified a text column (B2:B10) in the Max() function.

Sub max_ex()

Dim text_Rng As Range

Set text_Rng = Range("B2:B10")

MsgBox "Maximum Value = " & WorksheetFunction.Max(text_Rng)

End Sub

Output:

What if it contains both numbers and text?

In that case, it works. See this in the example below:

Sub max_ex()

Dim mix_Rng As Range

Set mix_Rng = Range("B2:D10")

MsgBox "Maximum Value = " & WorksheetFunction.Max(mix_Rng)

End Sub

You can see, in our given range the B column contains text, the C column numbers (currency), and the D column text as well.

Still, we got the maximum number from the currency column.