Excel VBA does not have a built-in Max function, but a macro can use Excel’s worksheet function through Application.WorksheetFunction.Max or Application.Max. For example, with numbers in B2:B20 on a worksheet named Data, a macro can find the largest value with Application.WorksheetFunction.Max(Worksheets("Data").Range("B2:B20")).
Is there a Max function in VBA?
Not as a native VBA language function. VBA includes functions such as Abs and Round, but Max belongs to Excel’s worksheet-function library. The distinction matters: in a worksheet cell, you can enter =MAX(B2:B20); in VBA, call the corresponding Excel function through the Excel application object.
There are two common forms: Application.WorksheetFunction.Max and Application.Max. Both calculate the maximum using Excel’s MAX behavior. The difference is chiefly how they handle an error returned by the worksheet function; that can affect how your macro responds to a range containing an error value.
This is useful when a macro needs to inspect a changing range, make a decision based on a threshold, or write the largest value into a report. If you only need a result on the sheet, a normal worksheet formula may be simpler and easier for another user to maintain.
Call MAX from a VBA macro
In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and put a procedure in the module. The following example reads the values in B2:B20 on the Data worksheet and displays the maximum:
Sub ShowMaximum()
Dim ws As Worksheet
Dim maxValue As Double
' Use a specific worksheet rather than the active sheet.
Set ws = ThisWorkbook.Worksheets("Data")
maxValue = Application.WorksheetFunction.Max(ws.Range("B2:B20"))
MsgBox "Largest value: " & maxValue
End Sub
If the cells contain 12, 18, 7, and 15, the result is 18. In a worksheet, with those same four numbers in B2:B5, =MAX(B2:B5) also returns 18. The VBA version stores the result in a variable, so the macro can use it in later calculations or decisions rather than simply showing it.
The example uses ThisWorkbook, which means the workbook containing the VBA code. Naming the worksheet explicitly avoids a common source of bugs: an unqualified Range("B2:B20") refers to the active sheet, which may not be the sheet the macro is intended to process.
To run a macro, you can use Alt+F8 in Windows and select its name. On Mac, Option+F11 opens the Visual Basic Editor on many keyboards; some require Fn as well. Shortcuts and keyboard settings can vary on Mac, so the Developer tab is another way to open the editor or run macros. Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm).
Application.Max versus WorksheetFunction.Max
For ordinary numeric data, either form is often sufficient. The practical choice becomes clearer when the input might contain an Excel error such as #N/A or #VALUE!.
Application.WorksheetFunction.Maxbehaves like a direct worksheet-function call. If MAX evaluates to an error, VBA generally raises a run-time error, so use an error handler if that possibility must be managed.Application.Maxreturns a Variant. When the calculation produces an Excel error, the result can be checked withIsErrorbefore the macro tries to use it as a number.
For example, this version checks for an error result before displaying the value:
Sub ShowMaximumWithCheck()
Dim ws As Worksheet
Dim result As Variant
' Point to the intended sheet.
Set ws = ThisWorkbook.Worksheets("Data")
result = Application.Max(ws.Range("B2:B20"))
If IsError(result) Then
MsgBox "The range contains an error value."
Else
MsgBox "Largest value: " & result
End If
End Sub
If you prefer WorksheetFunction.Max, use a VBA error handler to catch a run-time error rather than assuming every input range is clean. An error handler is especially useful in automated workbooks where users may paste data of varying quality. Do not use error handling to hide problems silently: report the issue or write a clear status so the cause can be investigated.
What values does MAX include?
When MAX receives a range reference, it uses numeric values in the referenced cells and ignores blank cells, text, and logical values such as TRUE or FALSE. A number stored as text in a cell is generally ignored as well. In contrast, numeric values or logical values supplied directly as arguments can be treated differently from values inside a referenced range. This distinction is one reason a formula can produce an unexpected result when the inputs are mixed.
For example, a date or time stored as a real Excel value is numeric underneath its display format, so MAX can return it as the largest value. The cell may show a calendar date even though Excel is comparing its serial number. Likewise, negative numbers are valid inputs: the maximum of -8, -3, and -11 is -3, because it is the greatest number, not the one with the greatest absolute size.
MAX ignores blanks, but it does not make invalid data numeric. If a cell contains the text “42” rather than the number 42, a range-based call may skip it. If every cell is blank or nonnumeric, MAX returns 0. That zero can look like a genuine result, so validate the input range when an empty result set is possible.
Errors need separate attention. A reference containing an error value can make the MAX calculation return an error rather than a usable number. Decide whether the right response is to stop, report the bad cell, or deliberately exclude errors. Excluding errors without telling the user can conceal a data-quality problem.
Find the maximum with a VBA loop
A loop is useful when values are already in a VBA array, when you need custom rules, or when calling Excel’s worksheet function is not appropriate. For a typed array of numbers, initialize the running maximum with the first item, then compare the remaining items:
Sub FindArrayMaximum()
Dim values(1 To 4) As Double
Dim i As Long
Dim maxValue As Double
' Example numeric data.
values(1) = 12
values(2) = 18
values(3) = 7
values(4) = 15
' Start with an actual item, not zero, so all-negative data works.
maxValue = values(1)
For i = 2 To UBound(values)
If values(i) > maxValue Then maxValue = values(i)
Next i
MsgBox "Largest value: " & maxValue
End Sub
This returns 18. Initializing the maximum to zero would be wrong for a set such as -8, -3, and -11: it would report zero, even though zero is not in the data. Starting with the first item avoids that mistake. The example assumes the array has at least one element; for a dynamic array, check that it has been initialized and contains items before calling UBound.
A loop also lets you skip values according to a clear rule, such as considering only positive amounts. That flexibility comes with extra responsibility: decide how to handle empty inputs, text, and errors, and document the rule. For a straightforward worksheet range, Excel’s MAX function is usually shorter and benefits from familiar worksheet behavior.
Put the maximum in a cell or use it in a decision
A macro can assign the result to another cell or compare it with a limit. For example, with numbers in B2:B20 and the Data worksheet in the same workbook, the following procedure writes the maximum to E2:
Sub WriteMaximumToCell()
Dim ws As Worksheet
Dim maxValue As Double
' Read the source range and write the result on the same sheet.
Set ws = ThisWorkbook.Worksheets("Data")
maxValue = Application.WorksheetFunction.Max(ws.Range("B2:B20"))
ws.Range("E2").Value = maxValue
End Sub
If the macro needs the value only for a decision, keep it in a variable instead of writing it to the sheet. For instance, it could compare the maximum with a target and show a warning only when the target is exceeded. That avoids changing the worksheet unnecessarily, but the comparison should use the same units and data assumptions as the source values.
For a result that should update automatically as users edit the data, enter a worksheet formula instead. A formula such as =MAX(B2:B20) recalculates with the sheet and is visible for review. A macro is more appropriate when the maximum is one step in a larger process, such as building a report or applying a conditional action.
Common mistakes and troubleshooting
- Calling
Maxby itself: VBA may report that the function or method is not defined. UseApplication.WorksheetFunction.Max,Application.Max, or write your own comparison routine. - Using the wrong sheet: An unqualified range can point to whichever sheet is active. Set a worksheet variable and use
ws.Range(...). - Getting zero unexpectedly: Check whether the cells contain actual numbers or numbers stored as text. Also check whether the range is blank or contains only text, since MAX returns zero when there are no numeric values to evaluate.
- Receiving a run-time error: Look for error values in the source range and consider whether the selected calling style needs an error check or handler.
- Expecting the maximum magnitude: MAX returns the greatest value, not the value with the greatest absolute size. For -20 and 5, it returns 5.
- Running code on the wrong workbook:
ThisWorkbookmeans the workbook that contains the macro, whileActiveWorkbookmeans the workbook currently active in Excel. Choose deliberately, especially when a macro opens or processes other files.
Macros may be blocked depending on the workbook’s source and Excel’s security settings. Run VBA only from workbooks you trust, and do not enable macros simply because a file asks you to. If the file was downloaded or received from someone else, verify its source before enabling content.
Frequently Asked Questions
What is the VBA equivalent of Excel’s MAX function?
Use Application.WorksheetFunction.Max or Application.Max, followed by the range or values to compare. VBA does not provide a standalone native Max function.
Can I find the maximum across two ranges?
Yes. Pass both ranges as arguments, for example Application.WorksheetFunction.Max(ws.Range("B2:B10"), ws.Range("D2:D10")). Make sure ws has been set to the intended worksheet.
Why does VBA MAX return zero?
Excel’s MAX returns 0 when it has no numeric values to evaluate. Check for an empty range, text values, or numbers stored as text. If zero is not a valid result for your situation, validate the inputs before treating the return value as a real maximum.
Does MAX ignore blank cells and text?
For values inside a referenced range, MAX ignores blanks and text, including numeric-looking text stored in cells. It evaluates numeric values; errors in the range can cause an error result.
Can I use MAX in a VBA User-Defined Function?
Yes. A VBA function can call Application.WorksheetFunction.Max just as a macro can. If the function is called from a worksheet, make sure its error behavior is appropriate and remember that a VBA-based workbook must be saved in a macro-enabled format for the function to remain available.
Related
- Excel Table Data Sorting: Steps, Options, and Fixes
- Excel Spreadsheet Not Responding? Causes and Fixes
- Excel Suddenly Not Responding? Causes and Fixes
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Find Unique Values in a ColumnTo find unique values in an Excel column with VBA, loop through the source cells, keep the first…
- How to Turn On Excel’s Macro Recorder in Microsoft 365In Excel for Microsoft 365 on Windows, select Developer > Code > Record Macro, set the macro’s name…
- Insert Borders in Excel Using VBA: Code and TipsTo insert borders with VBA, set the LineStyle, Weight, and optionally Color properties of a range’s Borders collection.…
- Excel VBA Copy Paste Transpose: Working Code and TipsTo copy a range and paste it transposed with Excel VBA, copy the source range and use PasteSpecial…
- Create a Dictionary in VBA: Keys, Values, and Code ExamplesTo create a dictionary in VBA for Excel on Windows, declare an object and use CreateObject("Scripting.Dictionary"). Add entries…
- Excel VBA: How to Assign a Keyboard Shortcut to a MacroTo assign a keyboard shortcut in Excel for Microsoft 365 on Windows, press Alt+F8, select the macro, choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.