Table of Contents
VBA (Visual Basic for Applications) is a programming language that can be used to automate tasks in Microsoft Office applications such as Excel. The Find Max Value in Range VBA function can be used to quickly locate the highest numerical value in a range of cells. This is useful for quickly finding and identifying the maximum value in a range of data. This function takes in a range of cells, searches through them, and identifies the highest numerical value in the range and returns it.
You can use the following basic syntax to calculate the max value in a range using VBA:
Sub MaxValue()
Range("D2") = WorksheetFunction.Max(Range("B2:B11"))
End Sub
This particular example calculates the max value in the range B2:B11 and assigns the result to cell D2.
If you would instead like to display the max value in a message box, you can use the following syntax:
Sub MaxValue()
'Create variable to store max value
Dim maxValue As Single
'Calculate max value in range
maxValue = WorksheetFunction.Max(Range("B2:B11"))
'Display the result
MsgBox "Max Value in Range: " & maxValue
End Sub
The following examples shows how to use each of these methods in practice with the following dataset in Excel that contains information about various basketball players:
Example 1: Calculate Max Value of Range Using VBA and Display Results in Cell
Suppose we would like to calculate the max value in the points column and output the results in a specific cell.
We can create the following macro to do so:
Sub MaxValue()
Range("D2") = WorksheetFunction.Max(Range("B2:B11"))
End Sub
When we run this macro, we receive the following output:
Notice that cell D2 contains a value of 43.
This tells us that the max value in the points column is 43.
Example 2: Calculate Max Value of Range Using VBA and Display Results in Message Box
Suppose we would instead like to calculate the max value in the points column and output the results in a message box.
Sub MaxValue()
'Create variable to store max value
Dim maxValue As Single
'Calculate max value in range
maxValue = WorksheetFunction.Max(Range("B2:B11"))
'Display the result
MsgBox "Max Value in Range: " & maxValue
End Sub
When we run this macro, we receive the following output:
The message box tells us that the max value in the range B2:B11 is 43.
Note that in this example we calculated the max value in the range B2:B11.
However, if you’d like to instead calculate the max value in an entire column you could type B:B instead.
This will calculate the max value in all of column B.