EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home VBA VBA Resources VBA Statistical Functions VBA Max
 

VBA Max

Madhuri Thakur
Article byMadhuri Thakur

VBA Max

What is Max Function in VBA?

Max Function is used to calculate the largest number. There are several numerical functions in excel which can be used to count the range, sum up the lot or to find the minimum or maximum value from the range of numbers. Max function is used to find the maximum value out of a range of values. It is an inbuilt function in Excel and categorized as the Max function. However, in VBA, there is no inbuilt function as Max to get the maximum value. Max function can be used in VBA Excel also. For the function argument (array, range, etc.), it can be either entered directly into the function or defined as variables to use instead.

 

 

Syntax:

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

=application.WorksheetFunction.max(arg1,arg2,arg3……………arg30)

Parameter or Arguments used in Max function are:

arg1……arg30: number 1 to number 30 from which the maximum number is to be inferred. It can be number, named ranges, arrays or reference to numbers.

Note:

  • If the arguments contain no numbers, MAX returns 0 (zero).
  • Arguments that have error values or text and cannot be translated into numbers will throw errors.
  • Max function returns a numeric value.

How to Enable the Developers Tab?

Developer tab is mandatory on the Excel ribbon to start and write the VBA macro. Follow the below steps to enable the developer’s tab in Excel.

Step 1: Go to File.

Open File

Step 2: Click on Options.

Options

Step 3: In a window opening up named Excel Options, click on Customize Ribbon to access the ribbon customization options.

Customize Ribbon

Step 4: Here in the customization options, you can see the Developer(Custom) option. Checkmark it, so that it gets activated on the main ribbon of excel and can easily be accessed. Click OK after checking the Developer option.

Developer(Custom) option

Step 5: Click on the Developer tab and then click the Visual Basic (ALT +F11) icon.

Developer Tab>Visual Basic

VBA editor will appear.

How to Use Max Function in Excel VBA?

Below are the different examples to use Max function in Excel VBA:

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

VBA Max – Example #1

Take four numbers 12, 25, 36, 45. Find out Max’s number by using the max function.

Code:

Sub AA()

Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim result As Integer
A = 12
B = 25
C = 36
D = 45
result = WorksheetFunction.Max(A, B, C, D)
MsgBox result

End Sub

Max number Example 1

Note:

  • Mention the data type of the variables through dim.
  • Assign numbers to variables.

Run the code by pressing the F5 key or by clicking on the Play Button. The result will be displayed in the message box.

Message Box Example 1-1

VBA Max – Example #2

Take four numbers 15, 34, 50, 62. Find out max number by using the Max function.

Code:

Sub AA1()

A = 15
B = 34
C = 50
D = 62
result = WorksheetFunction.Max(A, B, C, D)
MsgBox result

End Sub

VBA Max Example 2

Note:

  • Here, we have directly assigned numbers to four different variables without mentioning their data type. The program automatically decides the data type.
  • Used those variables in the formula and got the result in the message box.

Run the code by pressing the F5 key or by clicking on the Play Button. The result will be displayed in the message box.

Message Box Example 2-2

VBA Max – Example #3

Find the maximum value from the range by using the Max function.

VBA Max Example 3-1

Code:

Function getmaxvalue(Maximum_range As Range)
Dim i As Double
For Each cell In Maximum_range
If cell.Value > i Then
i = cell.Value
End If
Next
getmaxvalue = i
End Function

VBA Max Example 4

Note:

  • A function procedure in VBA code performs calculations and returns the result.
  • It can have an optional return statement. It is required to return a value from a function.
  • Before using the function we need to define that particular function.

Syntax:

Function functionname(parameter_list)
Statement 1
Statement 2
Statement 3
:
End Function

Here, the function keyword is followed by a unique function name e.g. getmaxvalue(args_1,…args_n) and may or may not carry the list of parameters with datatype e.g. Maximum_range As Range. It ends with the End Function which indicates the end of the function. Mention the data type of the variables through dim.

Calling a function:

To invoke a function call the function by using the function name e.g getmaxvalue(args_1,…args_n).

VBA MAX Function Example 3-2

The result will be as given below.

VBA Max Example 3-3

VBA Max – Example #4

Find the maximum value from the range by using the Max function.

VBA MAX Function Example 4-1

Code:

Function getmaxvalue(Maximum_range As Range)
Dim i As Double
For Each cell In Maximum_range
If cell.Value > i Then
i = cell.Value
End If
Next
getmaxvalue = i
End Function

Range Example 4

Note:

  • Maximum_range represents a range of cells passed from the excel sheet as a parameter.
  • The variable i is declared as Double.
  • The For loop is iterated. With each iteration, the, if condition checks whether the value read from the corresponding cell, is greater than i. If the condition evaluates true then cell value is assigned to i.
  • When all the cells in the Maximum_range have been iterated, the maximum among those will be assigned to i.
  • Finally, i is assigned to getmaxvalue and returned to the calling cell.

getmaxvalue Example 4-4

The result will be as given below.

getmaxvalue Example 4-5

Conclusion

VBA max function is used to find the maximum value from a range of numbers. A function procedure is required to perform calculations. Dim is used to define variables. End function is used to end the function. It performs the task very fast and accurate. Though it is not an inbuilt function in VBA excel, however, by using function procedure we can perform the max function in VBA excel.

Recommended Article

This is a guide to VBA MAX. Here we discuss how to use MAX function in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Solver
  2. VBA IF Statements
  3. VBA Sort
  4. VBA While Loop

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

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

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Web Development & many more.

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

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

EDUCBA

Download VBA Max Excel Template

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW