How to Sum Values in Excel using VBA

In Excel, you can use VBA to calculate the sum of 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 this.

Sum 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 a sum. In VBA, there’s a property called WorksheetFunction that can help you to call functions into a VBA code.

Let sum values from the range A1:A10.

1. First, enter the worksheet function property and then select the SUM 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 sum.
   
4. In the end, type closing parenthesis and assign the function’s returning value to cell B1.
   

Range("B1") = Application.WorksheetFunction.Sum(Range("A1:A10"))

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

Sum Values from an Entire Column or a Row

In that 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.Sum(Range("A:A"))

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

Use VBA to Sum Values from the Selection

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

Sub vba_sum_selection()

Dim sRange As Range
Dim iSum As Long

On Error GoTo errorHandler

Set sRange = Selection

iSum = WorksheetFunction.Sum(Range(sRange.Address))
MsgBox iSum

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

VBA Sum All Cells Above

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

Sub vba_auto_sum()

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.Sum(iRange)

Exit Sub

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

End Sub

Sum a Dynamic Range using VBA

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

Sub vba_dynamic_range_sum()

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.Sum(iRange)

Exit Sub

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

End Sub

Sum 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 sum 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.Sum(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.Sum(Rows(iCol))

Exit Sub

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

End Sub

Using SUMIF with VBA

Just like sum you can use the SUMIF function to sum values with criteria just like the following example.

Sub vba_sumif()

Dim cRange As Range
Dim sRange As Range

Set cRange = Range("A2:A13")
Set sRange = Range("B2:B13")

Range("C2") = _
WorksheetFunction.SumIf(cRange, "Product B", sRange)

End Sub