VBA Sum Function: Add Excel Cells and Ranges with Code

In VBA, add values from an Excel range with Application.Sum, for example, Application.Sum(Range("B2:B10")). VBA itself has no built-in language function named Sum; Excel’s SUM calculation is available through the Excel object model, or you can write a loop when you need custom rules.

What “Sum” Means in VBA

VBA is the programming language used to automate Excel, while SUM is an Excel worksheet function. That distinction explains why typing Sum(Range("A1:A5")) into an ordinary VBA procedure does not work unless your project defines a function with that name. To use Excel’s calculation, qualify it as Application.Sum or Application.WorksheetFunction.Sum.

Both approaches can total a range, but they are not identical in how errors are exposed to VBA. For most macros, Application.Sum is convenient because it returns a Variant that can hold either a number or an Excel error value. If you simply need a total for a worksheet cell, an ordinary worksheet formula may be simpler than VBA.

For example, with 12, 8.5, a blank cell, and 4.5 in B2:B5, the worksheet formula is =SUM(B2:B5), and its result is 25. This is standard Excel formula syntax, not VBA code. The equivalent calculation inside a VBA procedure would use an Excel object such as a Range and pass it to Application.Sum.

Use Application.Sum on a Range

Application.Sum is a concise choice when the values are already on a worksheet. A range can be written directly, or assigned to a Range variable first. Qualifying the range with a worksheet is important: an unqualified Range refers to the active sheet, which may not be the sheet your macro intended to use.

Here is the basic pattern, with the worksheet named Data and the values in B2:B100:

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

    ' Use the intended sheet, not whichever sheet is active.
    Set ws = ThisWorkbook.Worksheets("Data")
    total = Application.Sum(ws.Range("B2:B100"))

    MsgBox total
End Sub

The macro calculates the total and displays it in a message box. In a real workbook, change the sheet name and range to match your layout. If the named worksheet does not exist in ThisWorkbook, VBA raises an error; ThisWorkbook means the workbook containing the macro, whereas ActiveWorkbook means the workbook currently active in Excel.

To put the result in a cell instead, assign the result to a destination range. For instance, with values in Data!B2:B100 and the output intended for Data!B101, replace the message-box line with ws.Range("B101").Value = total. A fixed output address is straightforward for a stable report. If rows can be added, determine the last data row first so the range includes all records without accidentally summing unrelated cells.

Run a Macro Safely

Before running a macro that writes to a workbook, save a separate copy. VBA changes cannot normally be reversed with Ctrl+Z, so a mistaken assignment or an unexpectedly broad range may be difficult to recover from. Save a macro-enabled workbook as an .xlsm file if it needs to retain its VBA code.

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, or select Developer > Visual Basic. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon.
In the editor, choose Insert > Module and paste the procedure into the standard module.
Return to Excel and run the procedure from Developer > Macros, or press Alt+F8, select the macro, and choose Run.
Check the result and save the workbook after confirming it is correct.

On Mac, use Developer > Visual Basic or the Visual Basic Editor shortcut, which may require Fn depending on the keyboard’s function-key settings. Excel for the web can open a macro-enabled workbook but cannot create, edit, or run VBA macros; use the desktop Excel application for these steps. Only enable macros in files you trust.

Application.Sum and WorksheetFunction.Sum

Excel exposes SUM in two common forms: Application.Sum and Application.WorksheetFunction.Sum. For a normal range of numbers, either can calculate the same total. The practical difference is how VBA handles a worksheet error included in the arguments. WorksheetFunction.Sum generally raises a VBA run-time error when an input contains an Excel error value, while Application.Sum can return that error as a Variant. That lets the code inspect the returned value with IsError before deciding what to do.

For example, if a source range might contain #N/A or #VALUE!, do not assign an unchecked result to a Double. A Double can hold a numeric result but cannot hold an Excel error value, so that assignment can cause a type mismatch. Keep the result as a Variant if you need to test it, or validate the range before converting the result to a numeric type.

Choose WorksheetFunction.Sum when a worksheet error should be treated as an exceptional condition that your VBA error-handling code will catch. Choose Application.Sum when it is useful to receive and test the returned value. Neither version makes bad source data disappear: a worksheet error in the range remains an issue that the macro or user should address.

When a VBA Loop Is More Appropriate

A loop is useful when the total follows rules that Excel’s ordinary SUM calculation does not express by itself—for example, adding values only when another column meets a condition, applying a conversion, or recording which rows were excluded. It also makes the inclusion rule visible in the code. The following example totals numeric cells in a specified range and skips text, blanks, and error values.

Sub SumNumericCellsOnly()
    Dim ws As Worksheet
    Dim sourceRange As Range
    Dim cell As Range
    Dim total As Double

    ' Change the sheet and range to match the workbook.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set sourceRange = ws.Range("B2:B100")

    For Each cell In sourceRange.Cells
        If Not IsError(cell.Value2) Then
            If IsNumeric(cell.Value2) Then
                total = total + CDbl(cell.Value2)
            End If
        End If
    Next cell

    ws.Range("B101").Value = total
End Sub

The separate error check matters because error values are not ordinary numbers. The nested If statements also avoid relying on VBA’s And operator to stop evaluating after the first condition; VBA does not short-circuit that operator. This example deliberately uses IsNumeric, so numeric text such as "7" may be converted and included. That differs from SUM applied to a referenced range, which ignores text stored in cells. If numeric text should be excluded, add a stricter test or clean the source data rather than assuming the loop behaves exactly like SUM.

A loop gives you control, but it is more code to maintain and can be slower than a built-in calculation for very large ranges. Use it for a genuine custom rule, not just to recreate a calculation that Application.Sum already performs.

Using the Total in a Worksheet Function

You can create a user-defined function (UDF) in VBA when a workbook needs a reusable calculation in worksheet cells. Put a public function in a standard module, then call it from a cell. This is different from calling a macro: the UDF returns a value to the formula cell rather than displaying a message or directly changing other cells.

Public Function SumCells(ByVal values As Range) As Variant
    ' Return Excel's sum result, including an error value if present.
    SumCells = Application.Sum(values)
End Function

With the values 12, 8.5, a blank, and 4.5 in B2:B5, enter =SumCells(B2:B5) in another worksheet cell; the result is 25. The function must exist in the workbook or an accessible add-in, and the workbook must permit macros. A UDF like this adds little over the built-in =SUM(B2:B5) formula, so it is most useful when it applies a calculation or policy that a normal worksheet function does not provide.

Excel worksheet UDFs are intended to return results, not to alter other cells or control Excel’s interface. A function that changes unrelated cells can behave unpredictably during recalculation. If the task is to write a report total into a fixed cell, use a Sub procedure such as the macro above instead of a worksheet UDF.

Blanks, Text, Errors, and Dates

Understanding what the source range contains is often more important than choosing between two syntaxes. When SUM receives a cell range, blank cells and text entries are ignored, while numeric values are added. An error value in the range generally propagates as an error result; it is not the same as a blank. A date stored as a real Excel date is represented by a serial number, so it is included in a sum just like any other number. Summing dates may be mathematically valid but not meaningful for the report, so check the column’s purpose before adding it.

Also distinguish a number stored as text from a number stored as a numeric value. A range-based SUM ignores text entries, even if they look like numbers. A loop using IsNumeric and CDbl can convert some numeric text, which may make its answer larger than the worksheet function’s answer. Inconsistent imports are a common cause: a column can look uniform while some entries are stored as text.

If the result is zero or smaller than expected, check that the range points to the intended sheet and cells, that the values are numeric, and that filters or hidden rows are not being confused with exclusions. SUM includes hidden and filtered-out values in an ordinary range. If the requirement is to total only visible rows, use a calculation designed for visible cells, such as Excel’s SUBTOTAL on the worksheet, rather than assuming SUM omits hidden data.

Performance and Common Mistakes

For a single total, passing one range to Application.Sum is usually simpler and faster than reading each cell one at a time. If a macro must process a very large dataset with several custom checks, performance can improve by reading the range into a Variant array once, then looping through the array in memory. That extra technique is worthwhile only when profiling shows that repeated worksheet access is a bottleneck.

Unqualified range: Range("B2:B100") refers to the active sheet. Use ws.Range("B2:B100") when the target sheet is known.
Calling Sum without a qualifier: Sum(...) is not a built-in VBA function. Use Application.Sum(...), WorksheetFunction.Sum(...), or a function that your project defines.
Wrong output type: A Double cannot store an Excel error. Check for errors or retain the result as a Variant before converting.
Unexpected text: The range-based worksheet function ignores text in referenced cells; a custom loop may convert text that looks numeric.
Wrong workbook: ThisWorkbook and ActiveWorkbook can refer to different files. Choose deliberately when code may run while several workbooks are open.

When debugging, inspect the actual address of the range and the data type of a surprising cell. A range that is one row too short, a misspelled sheet name, or imported text can explain a wrong total more often than a defect in SUM itself. For a macro that updates a workbook, test on a copy and verify the output cell before using it on the original file.

Frequently Asked Questions

Is Sum a built-in VBA function?
No. SUM is an Excel worksheet function. In VBA, call it through Application.Sum or Application.WorksheetFunction.Sum, or write your own function or loop.

What is the simplest way to sum a range in VBA?
Use Application.Sum(ws.Range("B2:B10")), with ws set to the worksheet containing the values. Qualifying the range avoids relying on whichever sheet happens to be active.

Does VBA Sum ignore blank cells and text?
Excel’s SUM ignores blanks and text in a referenced range, but includes numeric cells and propagates worksheet errors. A custom VBA loop can follow different rules, so check how it handles text and errors.

Can I use a VBA sum as a cell formula?
Yes. Create a public UDF in a standard module and call it from a worksheet cell, or use Excel’s built-in SUM formula. A UDF requires macro support and is usually unnecessary for a plain total.

Why does my VBA sum return zero?
Check that the range contains numeric values, that it includes the intended rows, and that it is qualified with the correct worksheet. Values stored as text may be ignored by range-based SUM.

Can Excel for the web run VBA?
No. Excel for the web can open macro-enabled files, but creating, editing, or running VBA requires desktop Excel. On Mac, use the desktop Visual Basic Editor; menu and shortcut details can vary with keyboard settings.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.