Table of Contents
Using Visual Basic for Applications (VBA) to calculate the average value of a range is a simple task. To do this, you will need to use the WorksheetFunction.Average function, which can be used to calculate the average value of a range of cells. You can pass in the range of cells as the argument for the function and it will return the average value for that range. This can be assigned to a variable for use in your VBA code. To use the function, simply type ‘=Average(‘ followed by the range of cells, and end it with a closing parenthesis. This will return the average value of the range.
You can use the following basic syntax to calculate the average value of a range in Excel using VBA:
Sub AverageRange()
Range("E2") = WorksheetFunction.Average(Range("B1:B12"))
End Sub
This particular example calculates the average value in the range B2:B12 and assigns the result to cell E2.
If you would instead like to display the average value in a message box, you can use the following syntax:
Sub AverageRange()
'Create variable to store average value
Dim avg As Single
'Calculate average value of range
avg = WorksheetFunction.Average(Range("B1:B12"))
'Display the result
MsgBox "Average Value in Range:" & avg
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 Average of Range Using VBA and Display Results in Cell
Suppose we would like to calculate the average value in the points column and output the results in a specific cell.
We can create the following macro to do so:
Sub AverageRange()
Range("E2") = WorksheetFunction.Average(Range("B1:B12"))
End Sub
When we run this macro, we receive the following output:
Notice that cell E2 contains a value of 21.27273.
This tells us that the average value in the points column is 21.27273.
Example 2: Calculate Average of Range Using VBA and Display Results in Message Box
Suppose we would instead like to calculate the average value in the points column and output the results in a message box.
Sub AverageRange()
'Create variable to store average value
Dim avg As Single
'Calculate average value of range
avg = WorksheetFunction.Average(Range("B1:B12"))
'Display the result
MsgBox "Average Value in Range: " & avg
End Sub
When we run this macro, we receive the following output:
The message box tells us that the average value in the range B2:B12 is 21.27273.
Note that in this example we calculated the average value in the range B2:B12.
However, if you’d like to instead calculate the average value in an entire column you could type B:B instead.
This will calculate the average value for every cell in column B.