WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Max

VBA Max

Max, as the name suggests, is used to finding out the maximum value from a given data set or an array, this is a worksheet function so it is used with the worksheet method as worksheet function, there is a limitation to this method as this function takes an array as an argument there can only be 30 values in the array.

Excel VBA Max Function

We have several numerical functions in excel. We can count numerical values in the range, we can sum, and we can find the minimum value as well the 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 don’t have any built-in function called “MAX” to get the maximum number. We will see how to use this Excel VBA Max Function.

VBA Max

Example of Max Function in Excel VBA

Unfortunately, we don’t 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 below code.

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

In the above example, I have declared three variables to store the number.

Dim a As Integer

Dim b As Integer

Dim c As Integer

I have declared one more variable to show the results.

Dim Result As Integer.

For the first 3 three variables, I have assigned the value like 50, 25, and 60, respectively.

a = 50

b = 25

c = 60

In the next line, I 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, I am showing the value in the message box in VBA. MsgBox Result

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Now I will run this code using F5 or manually and see what the result is in the message box.

VBA MAX Example 1-1

So, the result is 60.

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

Advanced Example of Max in Excel VBA

Loops are very important 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.

I have a list of items and their monthly sales performance of those items, as shown below.

Example 2

Now for each item, I want to know what the maximum sale number is across 4 months, as shown in the picture.

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

Example 2-1

Now we will see how to find the maximum value by using 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

This will identify the maximum number easily.

Now run the code manually or press the F5 key and see the result as shown below.

VBA MAX Example 2-3

In order 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.
  • This is the opposite formula of the MIN function in excel.
  • MAX is not a VBA function. It is a built-in function in excel, so using the worksheet function class.

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

Recommended Articles

This has been a guide to VBA Max. Here we learn how to use Max Function in VBA to find the maximum value from a supplied range of numbers along with examples and downloadable codes. Below are some useful excel articles related to VBA –

  • VBA FileCopy
  • VBA Debug Print
  • VBA FileSystemObject
  • ByRef in VBA
  • VBA Find and Replace
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More