How to Average Values in Excel using VBA

In Excel, you can use VBA to calculate the average values from a range of cells or multiple ranges. And, in this tutorial, we are going to learn the different ways that we can use it.

Average in VBA using WorksheetFunction

In VBA, there are multiple functions that you can use, but there’s no specific function for this purpose. That does not mean we can’t do an average. In VBA, there’s a property called WorksheetFunction that can help you to call functions into a VBA code.

Let’s average values from the range A1:A10.

1. First, enter the worksheet function property and then select the AVERAGE function from the list.
   
2. Next, you need to enter starting parenthesis as you do while entering a function in the worksheet.
   
3. After that, we need to use the range object to refer to the range for which we want to calculate the average.
   
4. In the end, type closing parenthesis and assign the function’s returning value to cell B1.
   

Application.WorksheetFunction.Average(Range("A1:A10"))

Now when you run this code, it will calculate the average for the values that you have in the range A1:A10 and enter the value in cell B1.

Average Values from an Entire Column or a Row

In that case, you just need to specify a row or column instead of the range that we have used in the earlier example.

'for the entire column A
Range("B1") = Application.WorksheetFunction.Average(Range("A:A"))

'for entire row 1
Range("B1") = Application.WorksheetFunction.Average(Range("1:1"))

Use VBA to Average Values from the Selection

Now let’s say you want to average value from the selected cells only in that you can use a code just like the following.

Sub vba_average_selection()

Dim sRange As Range
Dim iAverage As Long

On Error GoTo errorHandler

Set sRange = Selection

iAverage = WorksheetFunction.Average(Range(sRange.Address))
MsgBox iAverage

Exit Sub

errorHandler:
MsgBox "make sure to select a valid range of cells"

End Sub

In the above code, we have used the selection and then specified it to the variable “sRange” and then use that range variable’s address to get the average.

VBA Average All Cells Above

The following code takes all the cells from above and average values from them and enters the result in the selected cell.

Sub vba_auto_Average()

Dim iFirst As String
Dim iLast As String
Dim iRange As Range

On Error GoTo errorHandler

iFirst = Selection.End(xlUp).End(xlUp).Address
iLast = Selection.End(xlUp).Address

Set iRange = Range(iFirst & ":" & iLast)
ActiveCell = WorksheetFunction.Average(iRange)

Exit Sub

errorHandler:
MsgBox "make sure to select a valid range of cells"

End Sub

Average a Dynamic Range using VBA

And in the same way, you can use a dynamic range while using VBA to average values.

Sub vba_dynamic_range_average()

Dim iFirst As String
Dim iLast As String
Dim iRange As Range

On Error GoTo errorHandler

iFirst = Selection.Offset(1, 1).Address
iLast = Selection.Offset(5, 5).Address

Set iRange = Range(iFirst & ":" & iLast)
ActiveCell = WorksheetFunction.Average(iRange)

Exit Sub

errorHandler:
MsgBox "make sure to select a valid range of cells"

End Sub

Average a Dynamic Column or a Row

In the same way, if you want to use a dynamic column you can use the following code which will take the column of the active cell and average for all the values that you have in it.

Sub vba_dynamic_column()

Dim iCol As Long

On Error GoTo errorHandler

iCol = ActiveCell.Column
MsgBox WorksheetFunction.Average(Columns(iCol))

Exit Sub

errorHandler:
MsgBox "make sure to select a valid range of cells"

End Sub

And for a row.

Sub vba_dynamic_row()

Dim iRow As Long

On Error GoTo errorHandler

iRow = ActiveCell.Row

MsgBox WorksheetFunction.Average(Rows(iCol))

Exit Sub

errorHandler:
MsgBox "make sure to select a valid range of cells"

End Sub