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
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.
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
- 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
- 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.
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
- 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.
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
- 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.
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
- 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.
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
- 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
Get FREE Advanced Excel Exercises with Solutions!