# How to Use the VBA Average Function in Excel – 4 Examples

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:

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

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

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF