VBA Max Function

What is Excel VBA Max Function?

The VBA Max function is used to find the maximum value from a given set of numeric values, a range of cells or an array. It is part of the VBA Worksheet Function object and works similarly to the Excel worksheet function MAX. The VBA Max function returns the highest value among the provided input values. Let us look at an example where we find VBA Max string length in a range of cells (A1:A5).

In this example, we have a range of cells (A1:A5) containing various strings. The VBA Max code initializes a variable maxLength to 0, which will be used to store the maximum string length. The code then loops through each cell in the range using a For Each loop.

For each cell, it calculates the length of the cell’s value using the LEN excel function and compares it with the current maximum length. The output of the code will be a message box displaying “The maximum string length in the range is: 8,” as the word “Elephant” has the longest length of 8 characters among the given strings.

What is Excel VBA Max Function?

The VBA Max function finds the maximum value from a range or array of numeric values.
Use Application.WorksheetFunction.Max to access the VBA Max function.
Ensure that the input range or array contains only numeric values to avoid errors or unexpected results.
If the input range is empty, the VBA Max function will return 0.
Handle non-numeric values separately or exclude them from the input range when using the VBA Max function.

How to use VBA Max Function?

Follow these steps to utilize the VBA Max function and determine the VBA Max Value in a range of cells.

Step 1: Open your Excel workbook and access the VBA editor by pressing “Alt + F11.”

Step 2: In the VBE, click “Insert” in the menu bar and select “Module.” It will insert a new module into the project.

Step 3: Inside the selected worksheet module or the new module, insert the necessary VBA code to use the VBA Max function.

Step 4: Close the VBA editor and return to your Excel workbook.

Step 5: Press “Alt + F8” to open the list of available macros, and then run the macro you created.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Start Learning Now

Examples

Let us look at some examples on how to implement the MAX function.

Example #1 – Finding the Maximum Value in a Range

Let’s say we have a range of numbers in cells A1 to A10, and we want to find the maximum value among them using the VBA Max function.

Step 1: In the new module, we created a subroutine “FindMaxValueInRange.”

Step 2: In the next step, we begin by declaring two variables: rng (of type Range) to store the range of values and “maxValue” (of type Variant) to store the result of the Max function.

Step 3: Here, we set the rng variable to the range “A1:A10,” which contains the numeric values from which we want to find the maximum value.

Step 4: Using the “Application.WorksheetFunction.Max(rng)” syntax, we call the MAX function to find the maximum value in the specified range rng.

The result of the VBA Max function is stored in the maxValue variable.

Step 5: Finally, we display the maximum value using a message box to give the user the result.

Step 6: Now save the module and exit the VBE. Now press Alt + F8 to open the Macro menu, select “FindMaxValueInRange” and click Run.

Step 7: A message box will appear once you execute the code, giving us the maximum value in the given range.

Here is the full code:

Sub FindMaxValueInRange()
Dim rng As Range
Dim maxValue As Variant
‘ Set the range from which you want to find the maximum value
Set rng = Range(“A1:A10”)
‘ Find the maximum value in the range using the Max function
maxValue = Application.WorksheetFunction.Max(rng)
‘ Display the maximum value in a message box
MsgBox “The maximum value in the range is: ” & maxValue
End Sub

Example #2 – Advanced Example of Max in Excel VBA:

In this example, we will demonstrate how to find the maximum value between two user-input values and handle situations where the input values are non-numeric or contain null values.

Step 1: In the new module, create a subroutine named “FindMaxOfTwoValues.”

Step 2: In the next step, we will declare three variables: input1, input2, and maxValue, all of type Variant. These variables will be used to store user input and the maximum value.

Step 3: We use the “InputBox” function to prompt the user to enter two values. The first value is stored in the input1 variable, and the second is stored in the input2 variable.

Step 4: To ensure the inputs are numeric, we check both input1 and input2 using the “IsNumeric” function. If either of the inputs is non-numeric, we display an error message and exit the macro using Exit Sub.

Step 5: Assuming both inputs are numeric, we find the maximum value between the two inputs using the “Application.WorksheetFunction.Max” function. This function handles the comparison even if the inputs are strings or mixed VBA data types.

The result of the Max function is stored in the maxValue variable.

Step 6: Finally, we display the maximum value using a message box to give the user the result.

Step 7: Now save the module and exit the VBE. Now press Alt + F8 to open the Macro menu, select “FindMaxOfTwoValues,” and click Run.

Step 8: Once you run the code, you will see the first message box, which will prompt us to enter a value. Now, let us enter 4567, for example, and click Ok.

Step 9: After you click on Ok, it will prompt another message box asking us to enter a second value, and here we will enter 9855.

Step 10: After clicking on Ok, you will see the following message box, which will prompt the VBA Max of two values we entered.

Here is the full code:

Sub FindMaxOfTwoValues()
Dim input1 As Variant
Dim input2 As Variant
Dim maxValue As Variant
‘ Prompt the user to enter two values
input1 = InputBox(“Enter the first value:”)
input2 = InputBox(“Enter the second value:”)
‘ Check if the inputs are numeric, otherwise, exit the macro
If Not IsNumeric(input1) Or Not IsNumeric(input2) Then
MsgBox “Please enter valid numeric values.”
Exit Sub
End If
‘ Find the maximum value between the two inputs using the Max function
maxValue = Application.WorksheetFunction.Max(input1, input2)
‘ Display the maximum value in a message box
MsgBox “The maximum value between ” & input1 & ” and ” & input2 & ” is: ” & maxValue
End Sub

Important Things To Note

The VBA Max function is part of the Worksheet Function object, so you must use the “Application.WorksheetFunction.Max” syntax to access it.
The input range or array for the VBA Max function should contain only numeric values. If any non-numeric values are present, it may lead to unexpected results or errors.
When using the VBA Max function to find the maximum between two values, ensure that the inputs are numeric, or handle non-numeric inputs properly to avoid runtime errors.
If the input range or array is empty, the VBA Max function will return 0. Handle empty or null values accordingly in your code to avoid incorrect results.
The system’s memory limitations determine the VBA Max array size. It is crucial to be cautious when dealing with large arrays to prevent memory overflow errors and ensure efficient code execution.

Frequently Asked Questions (FAQs)

1. Why is VBA Max function not working?

If the VBA Max function is not working, there could be several reasons:
1. The input range or array might contain non-numeric values, causing an error. Ensure that the data is entirely numeric before using the Max function.
2. The input range or array might be empty, resulting in 0. Check for empty ranges before using the Max function to avoid this issue.
3. A typo in the function name or incorrect syntax could prevent VBA from recognizing the Max function. Verify the function name and syntax are correct.

2. What is the difference between VBA Max and VBA Min functions?

The VBA Max and Min functions serve different purposes:
• The Max function returns the maximum value from a given set of numeric values (range or array).
• Min function returns the minimum value from a given set of numeric values (range or array).

3. Why is my VBA Max function returning 0?

If your VBA Max function returns 0, it is likely due to an empty range or array being used as input. When the input range or array is empty, the VBA Max function returns 0. To avoid this, ensure that the input contains numeric values and is not empty before using the VBA Max function.

4. How do you avoid null values in VBA Max function?

To avoid null values in the VBA Max function, you should check the input data for non-numeric values before using the function. You can use the IsNumeric function to verify that all values in the input range or array are numeric. Additionally, you can handle non-numeric values separately or exclude them from the input range to get accurate results from the VBA Max function.

Download Template

This article must help understand VBA Max Function with its formulas and examples. You can download the template here to use it instantly.

VBA-Max Download

VBA Max Function