VBA Max

Publication Date :

Blog Author :

Edited by :

Reviewed by :

Table Of Contents

arrow

Excel VBA Max Function

We have several numerical functions in Excel. We can count numerical values in the range and sum and find the minimum value and maximum value of the lot. To find the maximum value of the lot, we have an excel function called MAX, which will return the maximum value of the supplied range of numbers. In VBA, we do not have any built-in function called “MAX” to get the maximum number. We will see how to use this Excel VBA Max function.

Table of contents
VBA Max

Example of Max Function in Excel VBA

Unfortunately, we do not have the luxury of using MAX as the VBA built-in function, but we can access this function as a part of the Worksheet function class.

Now, look at the code below.

Code:

Sub MAX_Example1()

    Dim a As Integer
    Dim b As Integer
    Dim c As Integer

    Dim Result As Integer

    a = 50
    b = 25
    c = 60

    Result = WorksheetFunction.Max(a, b, c)

    MsgBox Result

End Sub
VBA MAX Example 1

We have declared three variables to store the number in the above example.

Dim a As Integer

Dim b As Integer

Dim c As Integer

We have declared one more variable to show the results.

Dim Result As Integer.

For the first 3 three variables, we assigned values like 50, 25, and 60, respectively.

a = 50

b = 25

c = 60

In the next line, we have applied the MAX as a VBA worksheet function class to store the result to the variable “Result.”

Result = WorksheetFunction.Max(a, b, c)

So finally, we are showing the value in the message box in VBA.

MsgBox Result

We will run this code using F5 or manually and see the result in the message box.

VBA MAX Example 1-1

So, the result is 60.

From all the supplied numbers: 50, 25, and 60, the maximum number is 60.

Advanced Example of Max in Excel VBA

Loops are crucial in VBA to run through all the cells and arrive at the result. We will see how to combine VBA MAX with loops to arrive at the maximum value from the list of numbers.

We have a list of items and the monthly sales performance of those items, as shown below.

Example 2

Now for each item, we want to know the maximum sale number across four months, as shown in the picture.

By applying MAX to Excel, we can find this in a few seconds.

Example 2-1

We will now see how to find the maximum value using the VBA code.

The below code will perform the task of finding the maximum number for each item.

Code:

Sub MAX_Example2()

    Dim k As Integer

    For k = 2 To 9
        Cells(k, 7).Value = WorksheetFunction.Max(Range("A" & k & ":" & "E" & k))
    Next k

End Sub
Example 2-2

It will identify the maximum number easily.

Run the code manually or press the F5 key to see the result below.

VBA MAX Example 2-3

To get the maximum values month name, use the below code.

Code:

Sub MAX_Example2()

    Dim k As Integer

    For k = 2 To 9
        Cells(k, 7).Value = WorksheetFunction.Max(Range("B" & k & ":" & "E" & k))
        Cells(k, 8).Value = WorksheetFunction.Index(Range("B1:E1"), WorksheetFunction.Match _
                        (Cells(k, 7).Value, Range("B" & k & ":" & "E" & k)))
    Next k

End Sub
Example 2-4

Based on the value provided by the VBA max function, the INDEX function & MATCH function will return the associated month in the next line.

VBA MAX Example 2-5

Things to Remember

  • If their duplicate number is there, it will show only one number which comes first.
  • It is the opposite formula of the MIN function in excel.
  • The MAX function is not a VBA function. However, it is a built-in function in Excel, so use the worksheet function class.

You can download this Excel Template here - VBA Max Function Template.