Excel and VBA go hand in hand when it comes to automating tasks and performing complex calculations. As someone who loves diving into data analysis, I often use VBA to sum values in Excel. It’s a powerful way to crunch numbers without manually entering formulas in every cell. VBA’s ability to manipulate Excel’s worksheet functions within a subroutine saves tons of time, especially with repetitive tasks.
From building simple VBA code modules that total up numbers in a range to pushing Excel’s builtin functions to their limits, I’ve found that there’s almost always a way to get the job done within Visual Basic for Applications. Whether it’s populating charts or tallying up numbers in a message box, VBA’s flexibility and integration with Microsoft Excel have made it an indispensable tool in my developer toolkit.
Sum in VBA using WorksheetFunction
In Excel VBA, if I need to perform a sum, I often reach for the WorksheetFunction property. It’s a gateway to Excel’s builtin sum functionality, letting VBA scripts tap into the power of Excel’s native functions. Here’s how to sum a range using the WorksheetFunction.Sum
method:
To calculate the sum of numbers in a range (like A1), I use these steps:
 Access
WorksheetFunction
property.  Invoke the
Sum
function from the available list.  Refer to the intended range through a
Range
object.  Assign the resulting sum to a designated cell.
Here’s a typical example of the syntax to sum values in the range A1 and output the result to the cell B1:
Range("B1") = Application.WorksheetFunction.Sum(Range("A1:A10"))
Factors to keep in mind with this syntax:
 The arguments of
WorksheetFunction.Sum
are versatile: you can pass in ranges, arrays, or individual numbers.  The
WorksheetFunction
object automatically ignores nonnumeric cells.  Return types can differ based on context, typically being a Double or Long data type.
Through this approach, VBA harnesses the exact Excel sum function that spreadsheet users will be familiar with. It’s a straightforward and reliable way to sum numbers without reinventing the wheel.
Sum Values from an Entire Column or a Row
When I need to add up numbers across an entire column or row in Excel, I use a neat VBA trick. Take a look:
 Entire Column A Sum:
Range("B1") = Application.WorksheetFunction.Sum(Range("A:A"))
 Entire Row 1 Sum:
Range("B1") = Application.WorksheetFunction.Sum(Range("1:1"))
Easy as pie, right? We’re just swapping our usual range with a full column or row reference.
Use VBA to Sum Values from the Selection
Here’s a neat way I sum values of just the cells I’ve picked out in Excel:
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 essence, I assign the cells I’ve selected to the variable sRange
and then tally them up with WorksheetFunction.Sum
. If there’s a hiccup, a message pops up, reminding me to choose cells that make sense. Simple and slick!
VBA Sum All Cells Above
Here’s a quick VBA snippet I use to sum cells above the active one:
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
Key Pointers:
iFirst
marks the top of the range.iLast
grabs the cell just above the current one.
Remember:
 Select below the cells you want to sum.
 If there’s an error, I’ve made sure a message prompts you to pick a valid cell range.
Sum a Dynamic Range using VBA
To sum values within a dynamic range in VBA, I usually follow these steps:
 Declare the starting and ending points of the range.
 Create a Range object.
 Use the WorksheetFunction.Sum function to calculate the sum.
Here’s a quick guide:

Declare Range Points:
Dim iFirst As String
Dim iLast As String

Set Range Object:
Dim iRange As Range
Set iRange = Range(iFirst & ":" & iLast)

Calculate the Sum:
ActiveCell = WorksheetFunction.Sum(iRange)
If an error pops up, a message box prompts to select a valid range. This method is handy in scenarios where the range isn’t fixed and can vary.
Sum a Dynamic Column or a Row
Here’s how I manage dynamic sums in Excel using VBA:
For Columns:
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
For Rows:
Sub vba_dynamic_row()
Dim iRow As Long
On Error GoTo errorHandler
iRow = ActiveCell.Row
MsgBox WorksheetFunction.Sum(Rows(iRow))
Exit Sub
errorHandler:
MsgBox "make sure to select a valid range of cells"
End Sub
I find this to be a neat trick to quickly sum either the row or the column where my selection currently is. Just make sure I’m selecting a cell within a row or column with numerical data to see the total pop up. If I don’t, the error handler gives a gentle nudge to check my selection. Simple and efficient!
Using SUMIF with VBA
To sum cells that meet specific criteria, I use the SUMIF
function in VBA like so:
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
Here’s the breakdown:
 cRange: The range of cells with criteria to test.
 sRange: The cells to sum if cRange meets our criteria.
 “Product B”: The specific criteria for summing values in sRange.
Simply put, I tell Excel to add up all the values in sRange
where their corresponding cells in cRange
are “Product B”. Super handy for quick calculations based on conditions!