How to Calculate Average Value of Range in VBA(Quick & Easy Guide)

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.

How to Highlight Rows in VBA(Quick & Easy Guide)