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 :