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 MAXExcel Function Called MAXThe MAX Formula in Excel is used to calculate the maximum value from a set of data/array. It counts numbers but ignores empty cells, text, the logical values TRUE and FALSE, and text values., 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.
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.
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
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 functionVBA Worksheet FunctionThe worksheet function in VBA is used when we need to refer to a specific worksheet. When we create a module, the code runs in the currently active sheet of the workbook, but we can use the worksheet function to run the code in a particular worksheet. 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 VBAMessage Box In VBAVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.. MsgBox Result
Now I will run this code using F5 or manually and see what the result is in the message box.
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.
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.
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.
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
This will identify the maximum number easily.
Now run the code manually or press the F5 key and see the result as shown below.
In order to get the maximum values month name, use the below 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
Based on the value provided by the VBA max function, the INDEX functionINDEX FunctionWhen the index function is used individually in Excel, it displays the value of the cell when we give it a position from a table array. When the index function is combined with the match function, it acts as a substitute for the VLOOKUP function. & MATCH functionMATCH FunctionThe MATCH function looks for a specific value and returns its relative position in a given range of cells. The output is the first position found for the given value. Being a lookup and reference function, it works for both an exact and approximate match. will return the associated month in the next line.
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 excelMIN Function In ExcelIn Excel, the MIN function is categorized as a statistical function. It finds and returns the minimum value from a given set of data/array..
- 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.
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 –