How to Use the VBA Average Function in Excel

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