**Average **estimation is a very common factor in our everyday life. In the business section, it is an important factor to summarize the production or sales over a certain period. You can apply the **VBA** Average function to calculate the average. That is an easy and time-saving task. I am going to discuss **four **simple and practical examples of **how Excel VBA Average Function** works effectively with appropriate illustrations.

**Excel VBA to Calculate Average (Quick View)**

```
Function TopAverage(DataAvg, Num)
'Returns the average of the highest Num values in Data
Sum = 0
For i = 1 To Num
Sum = Sum + WorksheetFunction.Large(DataAvg, i)
'Large(Data, i) returns the ith largest value from the Data.
Next i
TopAverage = Sum / Num
MsgBox "The average is: " & TopAverage
End Function
```

**Table of Contents**hide

## Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

**Introduction to VBA Average Function in Excel **

**→ Function Objective**

The **VBA Average Function** is used to calculate the **Arithmetic mean**.

**→ Syntax**

**expression.Average(Arg1,Arg2…)**

**→ Arguments Explanation**

ARGUMENT | REQUIRED/OPTIONAL | Data Type | EXPLANATION |
---|---|---|---|

Arg1,Arg2… |
Required |
Variant |
Numeric value from which to calculate the average |

## 4 Suitable Examples of VBA Average Function in Excel

Let’s say, we have a dataset where the **Sales Representative’s Name** and the types of **products sold**, and revenue earned by the **Sales Representative** are given in columns **B, C, **and** D **respectively. Now we will calculate the average of the revenue earned by those **Sales Representatives **using the **VBA Average function. **Here’s an overview of the dataset for today’s task.

### Example 1: Apply VBA Average Function to Calculate Average of Array

Now I’ll show how to calculate an average of an array using a** VBA Average Function. **It’s very helpful for some particular moments. From our dataset, we will calculate an average of an array using a** VBA Average Function.** Let’s follow the instructions below to learn!

**Step 1:**

- First of all, open a Module, to do that, firstly, from your
**Developer**tab, go to,

**Developer → Visual Basic**

- After clicking on the
**Visual Basic**ribbon, a window named**Microsoft Visual Basic for Applications – VBA Average Function**will instantly appear in front of you. From that window, we will insert a module for applying our**VBA code**. To do that, go to,

**Insert → Module**

**Step 2:**

- Hence, the
**VBA Average Function**module pops up. In the**VBA Average Function**module, write down the below**VBA**

```
Sub Average_Array()
Range("D13").Formula = "=Average(D5:D12)"
End Sub
```

- Hence, run the
**VBA**To do that, go to,

**Run → Run Sub/UserForm**

- After running the
**VAB Code**, you back to your**Excel**sheet, and you will be able to see the average of the revenue earned by the sales representatives using the**VBA**Average function in cell**D13**which has been given in the below screenshot.

### Example 2: Use VBA Average Function with Range Object

In this example, we will calculate an average of a **range object **using a** VBA Average Function**. From our dataset, we can easily do that**.** Let’s follow the instructions below to learn!

**Step 1:**

- First, according to
**method 1,****insert**a new module and type the below**VBA**code to calculate the average of the range object. The**VBA code**is,

```
Sub Average_Range_Object()
Dim x As Range
Set x = Range("D5:D12")
Range("D13") = WorksheetFunction.Average(x)
Set x = Nothing
End Sub
```

- Further, run the
**VBA**To do that, go to,

**Run → Run Sub/UserForm**

**Step 2:**

- After running the
**VAB Code**, you back to your**Excel**sheet, and you will be able to see the average of the revenue earned by the sales representatives using the**VBA**Average function in cell**D13,**and the average is**$27,780.00**which has been given in the below screenshot.

### Example 3: Apply Custom Function to Calculate Average in Excel VBA

This situation is an example of how a custom function can make things easier for you. This function takes two arguments: **DataAvg** and **Num**. **DataAvg **represents the range of data that you’re working with. **Num **is the number of largest values you want to average from the data range.

The code initializes the **Sum **variable to **0**. Then it uses For- Next loop to calculate the sum of the Num number largest values. Notice that I have used the built-in **LARGE **function of Excel in the code. You can use any worksheet function in the** custom VBA function **using WorksheetFunction and a period before the function name (WorksheetFunction.LARGE).

Finally, the average is found just by dividing the **Sum **by the **Num (Sum/Num)**. I showed the result in a message box with this text: “**The average is: **“. let’s follow the instructions below to learn!

**Step 1:**

- First, according to
**method 1,****insert**a new module and type the below**VBA**code to calculate the average using the custom function. The**VBA code**is,

```
Function TopAverage(DataAvg, Num)
'Returns the average of the highest Num values in Data
Sum = 0
For i = 1 To Num
Sum = Sum + WorksheetFunction.Large(DataAvg, i)
'Large(Data, i) returns the ith largest value from the Data.
Next i
TopAverage = Sum / Num
MsgBox "The average is: " & TopAverage
End Function
```

**Step 2:**

- Hence, save the worksheet using the keyboard shortcut as
**Ctrl + S**. After that, go back to your dataset, and select cell**D13**to write down the**custom function**named**TopAverage**. The custom function is,

`=TopAverage(D5:D12,5)`

- Where
**D5:D12**is the**DataAvg,**and**5**is the**Num**of the**TopAverage**function.

- After that, simply, press
**ENTER**on your keyboard, and instantly a message box named**Microsoft Excel**pops up showing “**The average is: 35723.8**” which is the average of the**top 5 revenue**.

### Example 4: Use VBA Average Function to Calculate Average Dynamic Range of Cells

Last but not least, we will use the **dynamic range of cells **in **VBA Average function** to calculate the average. Let’s follow the instructions below to learn!

**Steps:**

- First, according to
**method 1,****insert**a new module and type the below**VBA**code to calculate the average of the dynamic range. The**VBA code**is,

```
Sub Average_Function()
Dim R As Double
R = Evaluate("AVERAGE(D5:D" & Sheets("Dynamic").Range("D" & Rows.Count).End(xlUp).Row & ")")
MsgBox "The Average of Dynamic Range is: $" & R
End Sub
```

- Further, run the
**VBA**To do that, go to,

**Run → Run Sub/UserForm**

- After that, a message box named
**Microsoft Excel**pops up showing “**The Average of Dynamic range is: $27780**” which is the average of the dynamic range.

## Things to Remember

👉 You can pop up **Microsoft Visual Basic for Applications **window by pressing **Alt + F11** **simultaneously**.

👉 If a **Developer **tab is not visible in your ribbon, you can make it visible. To do that, go to,

**File → Option → Customize Ribbon**

## Conclusion

I hope all the suitable methods mentioned above to calculate the average with VBA code will provoke you to apply them in your **Excel **spreadsheets with more productivity. You can also visit our website **ExcelDemy** to learn more Excel-related content. You are most welcome to feel free to comment if you have any questions or queries.