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 the 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
```

**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 |

## How to Use VBA Average Function in Excel: 4 Practical Examples

Let’s say, we have a dataset where the sales representative’s name, 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 the average of an array using VBA. It’s very helpful for some particular moments. From our dataset, we will calculate an average of an array using a** VBA Average **function.

**Steps:**

- 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**

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

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

- Hence, run the VBA code. To do that, go to,

**Run → Run Sub/UserForm**

- After running the VBA 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.

**Read More:** How to Calculate Average of Multiple Columns in Excel

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

In this example, we will calculate an average of a range object using the** VBA Average **function. From our dataset, we can easily do that.

**Steps:**

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

```
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 code. To do that, go to,

**Run → Run Sub/UserForm**

- After running the VBA 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**. The average is $27,780.00 which is given in the below screenshot.

**Read More:** How to Calculate Average of Multiple Ranges in Excel

### 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 the *For- Next* loop to calculate the sum of the *Num* number’s largest values. Notice that I have used **the 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: *“.

**Steps:**

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

```
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
```

- 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.

**Read More:** How to Calculate Average of Top 5 Values in Excel

### 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 the **VBA Average **function to calculate the average.

**Steps:**

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

```
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 code. 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 the **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**

**Download Practice Workbook**

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

## 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 are most welcome to feel free to comment if you have any questions or queries.

## Related Articles

- How to Calculate VLOOKUP AVERAGE in Excel
- How to Find Average with OFFSET Function in Excel
- How to Calculate Average in Excel Excluding 0
- How to Average Values Greater Than Zero in Excel
- How to Add Average Line to Excel Chart

**<< Go Back to Calculate Average in Excel | How to Calculate in Excel | Learn Excel**