VBA Max Function

The Max() is WorksheetFunction that you may use in the VBA coding to get the maximum number.

It takes up to thirty arguments i.e.

A graphic representation of the VBA Max function tutorial

What is Max function and its syntax

The Max() is WorksheetFunction that you may use in the VBA coding to get the maximum number.

It takes up to thirty arguments i.e.

expression.Max (Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7, Arg8, Arg9, Arg10, Arg11, Arg12, Arg13, Arg14, Arg15, Arg16, Arg17, Arg18, Arg19, Arg20, Arg21, Arg22, Arg23, Arg24, Arg25, Arg26, Arg27, Arg28, Arg29, Arg30)

You may provide numbers, variables, range of cells in Excel to get the maximum number.

We will show a few examples of using the Max function below.

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



Using Long variables in the Max function

  • This time we used VBA Long type variables in the Max() function.
  • Three variables are declared and assigned the values.
  • Then Max() function is used to get the variable with the maximum value:
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



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



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



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()

If text is given in the Max() function, though it does not generate an error, however, the result is returned as 0.

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



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.