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

**The VBA Average Function in Excel **

**→ Function Objective**

calculate the arithmetic mean.

**→ Syntax**

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

**→ Arguments **

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

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

This is the sample dataset.

### Example 1 – Apply the VBA Average Function to Calculate the Average of an Array

**Steps:**

- Go To:

**Developer → Visual Basic**

- Select
**Visual Basic**. - Go to:

**Insert → Module**

- In the
**VBA Average Function**module, use the VBA code.

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

- To run the VBA code, click:

**Run → Run Sub/UserForm**

- Go back to the Excel sheet.

This is the output:

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

### Example 2 – Use the VBA Average Function with a Range Object

**Steps:**

- Follow the steps described in
*Method 1 to*insert a new module. - Enter the 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
```

- To run the VBA code, click:

**Run → Run Sub/UserForm**

- Go back to the Excel sheet.

The average is $27,780.00.

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

### Example 3 – Apply a Custom Function to Calculate the Average in Excel VBA

**Steps:**

- Follow the steps described in
*Method 1 to*insert a new module. - Enter the VBA code.

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

- Press
**Ctrl + S**. - Go back to the dataset and select
**D13**. - Enter the function:

`=TopAverage(D5:D12,5)`

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

- Press
**ENTER.**

A message box displays: “*The average is: 35723.8*”.

### Example 4 – Use the VBA Average Function to Calculate the Average of a Dynamic Range of Cells

**Steps:**

- Follow the steps described in
*Method 1 to*insert a new module. - Enter the VBA code.

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

- To run the VBA code, click:

**Run → Run Sub/UserForm**

A message box displays “*The Average of Dynamic range is: $27780*” .

## Things to Remember

You can open the **Microsoft Visual Basic for Applications **window by pressing **Alt + F11**.

To enable the** Developer **tab, go to:

**File → Option → Customize Ribbon**

**Download Practice Workbook**

Download the practice workbook.

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