In this tutorial you will learn How to Calculate Average Value of Range in VBA
To Calculate Average Value of Range in VBA you can use the following syntax.
Sub AverageRange() Range("E2") = WorksheetFunction.Average(Range("B1:B11")) End Sub
The following code can be used if you would like to show the average value 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:B11")) 'Display the result MsgBox "Average Value in Range:" & avg End Sub
Let’s see the above syntax with an example
How to Calculate Average Value of Range in VBA
Assuming you want to Calculate Average Value of Range in the points scored column and get the result on a specific cell.
We can use the following macro to get the desired result.
Sub AverageRange() Range("E2") = WorksheetFunction.Average(Range("B1:B11")) End Sub
Example 1 : Calculate Average Value of Range in VBA
Step 1: Prepare the dataset
Below table contains a list of football teams with Points Scored and number of wins
Step 2 : Create the Macro
Go to developer table ->Visual Basic and add the provided syntax
Step 3 : Run the Macro
Now on developer tab and click on macros
Select the Sheet and click on the Run button
After executing the macro we get the following result
In the above screenshot you will notice that cell E2 contains a value of 60
Example 2 : Calculate Average Value of Range in VBA and display a message box
Assuming you want to Calculate Average Value of Range in the points scored column and display the result in a message box .
You can create the following macro
Sub AverageRange() 'Create variable to store average value Dim avg As Single 'Calculate average value of range avg = WorksheetFunction.Average(Range("B1:B11")) 'Display the result MsgBox "Average Value in Range: " & avg End Sub
After executing the macro you will get the following result :
How to Calculate Average Value of Range in VBA
In the above tutorial we have shown you how to Calculate Average Value of Range in VBA .
We hope this tutorial was useful.
Related articles :