In Excel VBA, calculate an average by calling Excel’s built-in function: use Application.WorksheetFunction.Average(range) when you want a numeric result, or Application.Average(range) when you want to check for an Excel error value. For example, with numbers in B2:B6, the worksheet formula is =AVERAGE(B2:B6); VBA can calculate the same average without writing a formula into the sheet.
Call Excel’s AVERAGE function from VBA
VBA does not have a separate built-in language function named Average for calculating an Excel range. Instead, VBA can ask Excel to evaluate the worksheet function AVERAGE. The usual call is Application.WorksheetFunction.Average, followed by the range or values to average. This is useful in a macro that needs a result for a later calculation, a message, or a decision.
For instance, with the values 10, 12, a blank cell, 18, and 20 in B2:B6, the average is 15. AVERAGE ignores the blank, so the calculation is (10 + 12 + 18 + 20) / 4. If your only goal is to display an average on the worksheet, enter the formula in a cell instead of using VBA; it updates automatically as the source values change.
A VBA call does not have to place a formula in a cell. It returns a value to your code, where you can store it in a variable, compare it with a threshold, or show it to the user. The method you choose matters most when the range contains no numbers or contains an error, because Excel’s error result needs to be handled deliberately.
How AVERAGE treats cells
Excel’s AVERAGE function adds numeric values and divides by the count of those numeric values. When the input is a cell reference, it ignores empty cells, text, and TRUE or FALSE values. It does include zero, which counts as a number. A zero can therefore lower the result, while a blank cell does not enter the calculation at all.
For example, a range containing 4, 0, and 8 has an average of 4, because the sum is 12 and there are three numeric values. The same range with the middle cell blank averages 6. That distinction is important when a zero means “no activity” in your data but a blank means “not recorded.” Decide which meaning fits your records before interpreting the result.
Other details to keep in mind:
- A number stored as text in a referenced cell is generally ignored by AVERAGE, even if it looks like a number.
- An error value in the referenced range can make the average return an error.
- Hidden rows are still included by AVERAGE. Filtering or hiding data does not automatically change its calculation.
- AVERAGE returns a divide-by-zero error when there are no numeric values to average.
These rules match the worksheet function whether it is entered in a cell or called through VBA. They are not the same as calculating an average by counting every cell in the selected range, which can accidentally treat blanks or text as zero.
WorksheetFunction.Average and Application.Average
Both approaches invoke Excel’s AVERAGE calculation, but their error behavior differs. Application.WorksheetFunction.Average is convenient when you expect a valid numeric result. If Excel cannot return one—for example, because the range contains an error or has no numeric values—VBA can raise a runtime error. Your procedure then needs an error handler if such input is possible.
Application.Average returns a Variant. When the calculation produces an Excel error, such as #DIV/0!, that result can be tested with VBA’s IsError function before you use it as a number. This often makes it easier to handle unpredictable worksheet data without sending normal input errors to a VBA error handler. It does not remove the need to decide what the macro should do when the input is invalid.
Choose based on how you want to handle failure:
- Use
WorksheetFunction.Averagewhen valid numeric data is expected and a VBA error handler is appropriate. - Use
Application.Averagewhen you want to inspect an error result withIsError. - For either method, check that the range you pass is the intended range and that its cells contain appropriate values.
Neither option is inherently a more accurate average. The practical difference is how the result is represented and how your VBA code responds when Excel cannot calculate it.
Example: calculate and display a range average
Suppose the worksheet named Sales has figures in B2:B6. The following macro uses Application.Average, checks the returned value, and displays a result to two decimal places. It does not alter the worksheet data.
Sub ShowSalesAverage()
Dim sourceRange As Range
Dim result As Variant
' Point to the cells to average.
Set sourceRange = ThisWorkbook.Worksheets("Sales").Range("B2:B6")
' Application.Average can return an Excel error value.
result = Application.Average(sourceRange)
If IsError(result) Then
MsgBox "The range has no usable average or contains an error."
Else
MsgBox "Average: " & Format$(CDbl(result), "0.00")
End If
End Sub
To use it, open the Visual Basic Editor, insert a standard module, and paste the procedure. On Windows, press Alt+F11 to open the editor; on Mac, the shortcut is usually Option+F11, though some keyboards require Fn as well. You can also open it from the Developer tab by choosing Visual Basic. The worksheet name in the code must match the actual tab name, and the workbook must be open when the macro runs.
Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so a backup is important if you later modify the code to write results or change cells. To keep VBA code in the workbook, save it in the Excel Macro-Enabled Workbook format (.xlsm). Only enable macros in files you trust.
Use WorksheetFunction.Average with error handling
If the range is expected to contain valid numbers, WorksheetFunction.Average is a straightforward choice. The example below handles a calculation error instead of allowing the procedure to stop unexpectedly. It uses the same Sales!B2:B6 layout as the preceding example.
Sub ShowSalesAverageWithHandler()
Dim sourceRange As Range
Dim averageValue As Double
' Select the worksheet cells to average.
Set sourceRange = ThisWorkbook.Worksheets("Sales").Range("B2:B6")
On Error GoTo AverageError
averageValue = Application.WorksheetFunction.Average(sourceRange)
On Error GoTo 0
MsgBox "Average: " & Format$(averageValue, "0.00")
Exit Sub
AverageError:
MsgBox "Excel could not calculate an average for this range."
End Sub
The handler catches a calculation failure, but it does not identify whether the cause was an empty range, an error in a cell, or some other problem. For a workbook used by other people, you may want to validate the data separately and provide a more specific message. Avoid a blanket error handler that silently substitutes zero: zero is a real average in some datasets and can disguise a data problem.
Put the result in a cell or use a worksheet formula
If the answer should remain visible and update as the data changes, a worksheet formula is usually the simplest tool. With numbers or other source values in B2:B6, enter =AVERAGE(B2:B6) in another cell, such as B7. For the example values 10, 12, blank, 18, and 20, B7 displays 15. The formula recalculates when the referenced cells change.
A macro is more useful when averaging is one step in a larger task—for example, processing many sheets, checking whether a target has been reached, or generating a report. If you want VBA to write the formula rather than calculate a one-time value, it can assign the formula to a destination cell. With B2:B6 as the source and B7 as the destination, use this VBA statement:
Range("B7").Formula = "=AVERAGE(B2:B6)"
This leaves a live formula in B7 rather than storing only the current result. Be careful with unqualified Range references in larger procedures: they refer to the active sheet, which may not be the sheet you intended. Qualifying the worksheet explicitly makes the target clearer and reduces surprises when the macro switches sheets.
Common problems and useful alternatives
A wrong-looking result often comes from the contents of the range rather than from VBA syntax. Check for numbers stored as text, formula cells that return an empty string, zeros that should have been blanks, or error values. Also verify that the range starts and ends in the intended rows. A macro that averages a whole column may include data outside the section a reader expects.
AVERAGE includes hidden rows. If the intended result should exclude filtered-out rows, consider the worksheet function SUBTOTAL instead. With data in B2:B100, the formula =SUBTOTAL(1,B2:B100) calculates an average while excluding rows filtered out by a filter; function number 1 still includes manually hidden rows. To exclude manually hidden rows as well, use 101: with the same B2:B100 layout, =SUBTOTAL(101,B2:B100) calculates an average that excludes them. These formulas answer a different question from ordinary AVERAGE, so select one based on how hidden data should count.
Another common question is whether AVERAGE counts text as zero. It does not count text in a referenced range as a numeric value; it skips it. If you specifically need text and logical values treated as part of the calculation, Excel also provides AVERAGEA, whose rules differ. Do not switch to AVERAGEA simply to “include everything” without checking what a text entry such as “N/A” should mean mathematically.
Frequently Asked Questions
What is the VBA code for the Excel average function?
Use Application.WorksheetFunction.Average(Range("B2:B10")) for a numeric result. Use Application.Average if you want to check whether Excel returned an error value with IsError.
How do I average a range in VBA without putting a formula in a cell?
Assign the result of an Average call to a variable, such as a Variant, and use that variable in your procedure. The calculation occurs in VBA, so it does not add a formula to the worksheet.
Why does my VBA average cause a run-time error?
The range may contain an Excel error or may have no numeric values, causing AVERAGE to return an error. WorksheetFunction.Average can raise a VBA runtime error in that situation. Add an error handler or use Application.Average and test its result with IsError.
Does Excel VBA AVERAGE count blank cells or zeros?
It ignores genuinely blank cells but includes numeric zero. A formula returning an empty string is text rather than a truly empty cell, and text in a referenced range is ignored as well.
How can I average only visible rows?
Use SUBTOTAL rather than AVERAGE when filtered-out rows should be excluded. Function number 1 excludes filtered rows but includes manually hidden rows; function number 101 excludes both filtered and manually hidden rows.
Should I use VBA or an AVERAGE formula?
Use a worksheet formula when you want a result that updates visibly with the data. Use VBA when the average is part of an automated process, report, or decision and the code needs to use the result.
Related
- Excel Text to Columns: Split at the Last Space
- Excel Text to Columns: Split at Line Breaks
- Excel Text to Columns Greyed Out? Causes and Fixes
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Array Declaration: Syntax, Types, and ExamplesIn VBA, declare an array with a variable name followed by parentheses, then specify its element type: for…
- Excel VBA Date Today: How to Insert the Current DateIn Excel VBA, use the built-in Date function to get today’s date. To put it in a worksheet…
- PowerPoint VBA InputBox Location and How to Position ItIn PowerPoint VBA, call the built-in InputBox function from a macro; its optional XPos and YPos arguments set…
- Excel VBA Code to Sort Data by One or More Columns in ExcelTo sort data with Excel VBA, set the range to sort, add one or more sort keys, specify…
- Excel VBA Function Output: Return Values and DisplayAn Excel VBA function outputs a value by assigning that value to the function’s name. That value appears…
- Excel VBA: Change a PivotTable Data SourceTo change an Excel PivotTable’s source with VBA, create a new PivotCache from the replacement range, assign it…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.