VBA Max Function

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.

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:

Output:

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:

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

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:

Output:

What if 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:

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.

Output:

What if it contains both numbers and text?

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

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.