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

Get FREE Advanced Excel Exercises with Solutions!

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

Apply Custom Function to Calculate Average in Excel VBA


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.

excel vba average function


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

Apply VBA Average Function to Calculate Average of Array

  • 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

Apply VBA Average Function to Calculate Average of Array

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

Apply VBA Average Function to Calculate Average of Array

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

Use VBA Average Function with Range Object

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

Use VBA Average Function with Range Object

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

Apply Custom Function to Calculate Average in Excel VBA

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

Apply Custom Function to Calculate Average in Excel VBA

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

Use VBA Average Function to Calculate Average Dynamic Range of Cells

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

Use VBA Average Function to Calculate Average Dynamic Range of Cells


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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo