Use Excel’s SUM Function in VBA

To use Excel’s SUM function in VBA, call WorksheetFunction.Sum and pass it a range or values. SUM is an Excel worksheet function exposed to VBA through the WorksheetFunction object; it is not a standalone VBA built-in. The complete VBA procedures below are copyable, but they were not executed because the configured runtime does not support VBA.

How to Use Excel’s SUM Function in VBA

The general form is Application.WorksheetFunction.Sum(argument1, argument2, ...). Use a fully qualified worksheet reference when a macro might run while another sheet is active, so the calculation always reads from the intended sheet. Each example below uses ThisWorkbook.Worksheets("Sales"); the workbook must contain a worksheet with that name.

Sum One or More Ranges

These procedures populate the Sales sheet with sample values, calculate a total, and write it to E2. The first sums B2:B4 (12 + 8 + 5 = 25). The second passes B2:B3 and D2:D3 as separate range arguments (12 + 8 + 5 + 10 = 35), which is how to total non-contiguous ranges.

One range:

Sub SumOneRange()
    Dim ws As Worksheet
    Dim total As Double

    Set ws = ThisWorkbook.Worksheets("Sales")
    ws.Range("B2").Value = 12
    ws.Range("B3").Value = 8
    ws.Range("B4").Value = 5

    total = Application.WorksheetFunction.Sum(ws.Range("B2:B4"))
    ws.Range("E2").Value = total
End Sub

Two separate ranges:

Sub SumTwoRanges()
    Dim ws As Worksheet
    Dim total As Double

    Set ws = ThisWorkbook.Worksheets("Sales")
    ws.Range("B2").Value = 12
    ws.Range("B3").Value = 8
    ws.Range("D2").Value = 5
    ws.Range("D3").Value = 10

    total = Application.WorksheetFunction.Sum( _
        ws.Range("B2:B3"), ws.Range("D2:D3"))
    ws.Range("E2").Value = total
End Sub

For one range, Sum receives one Range object. For two ranges, it receives each Range object as its own argument; do not combine the addresses into one range string. In both procedures, assigning total to ws.Range("E2").Value stores the calculated result in E2.

The following runnable Python example illustrates arithmetic for values 12, 8, and 5; it does not call WorksheetFunction.Sum or execute Excel or VBA.

Run this example:

values = [12, 8, 5]
total = sum(values)
print(total)

25

Write the Sum to a Worksheet Cell

The procedures above store the result in a variable, then assign it to the destination cell’s Value property. The general pattern is total = Application.WorksheetFunction.Sum(ws.Range("B2:B10")) followed by ws.Range("E2").Value = total. In VBA, Value receives the result through assignment with =. Keeping the read range and destination fully qualified avoids depending on whichever worksheet is active.

Sum an Array in VBA

When values are already in a VBA array, a loop gives you direct control over accumulation. Array(...) returns a Variant array, so declare numbers As Variant; initialize total before the loop and add each element from LBound(numbers) through UBound(numbers). This complete procedure totals 15 and displays it in the VBA Immediate window. It accumulates values in VBA and does not call Excel’s SUM worksheet function. The VBA example is unexecuted in the configured runtime.

Sub SumArray()
    Dim numbers As Variant
    Dim total As Double
    Dim i As Long

    numbers = Array(1, 2, 3, 4, 5)
    total = 0

    For i = LBound(numbers) To UBound(numbers)
        total = total + numbers(i)
    Next i

    Debug.Print total
End Sub

Sum Values That Meet a Condition

This procedure writes sample values to A2:A6 on Sales, adds only values greater than 5, and displays the total (22) in the VBA Immediate window. The If statement controls which values contribute to the running total. This is conditional accumulation in VBA. For a worksheet-based conditional total, Excel also provides SUMIF; call it through WorksheetFunction.SumIf when that better fits the task. The VBA example is unexecuted in the configured runtime.

Sub SumValuesGreaterThanFive()
    Dim ws As Worksheet
    Dim total As Double
    Dim i As Long

    Set ws = ThisWorkbook.Worksheets("Sales")
    ws.Range("A2").Value = 2
    ws.Range("A3").Value = 6
    ws.Range("A4").Value = 9
    ws.Range("A5").Value = 4
    ws.Range("A6").Value = 7

    total = 0
    For i = 2 To 6
        If ws.Cells(i, 1).Value > 5 Then
            total = total + ws.Cells(i, 1).Value
        End If
    Next i

    Debug.Print total
End Sub

FAQ

What does WorksheetFunction.Sum do? It calls Excel’s SUM worksheet function from VBA and returns the total of the supplied numeric values or ranges.

Can it sum non-contiguous ranges? Yes. Pass each range as its own argument, as shown above.

How are nonnumeric cells handled? Excel SUM ignores text and empty cells in referenced ranges; values supplied directly as arguments can have different coercion behavior. If a result depends on how mixed inputs are treated, verify the inputs and use ranges consistently.

Can ranges come from different worksheets? Yes. Pass separately qualified references, such as Worksheets("North").Range("B2:B10") and Worksheets("South").Range("B2:B10"), as arguments to the same SUM call.