How to Use VBA Average Function in Excel (4 Practical Examples)

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

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

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,

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