The Sum function in Excel is one of the most widely used functions, and in fact, probably one of the first functions that an Excel user learns when they are learning to use Excel!  Simply stated, the Sum function adds up a range of cells for the user.  This function, along with all the Excel built-in functions, can also be used in VBA Code.

Sum Function Syntax

As with the function that is used within Excel, the syntax of the sum function has the name of the function, and then the range of cells to add up.

 =SUM(A1:A3)

Using the Sum Function in VBA Code

To use this function in VBA code, the line of code could be:

Sub TestSum()
Range("A4") = "=Sum(A1:A3)"
End Sub

Note that the function has to be within quotation marks to work.

Using the Sum function with Cell References

You can also use a Range object and use cell references in VBA code when using the sum function

Sub TestSum2()
Range("A4") = "=SUM(" & Range(Cells(1, 1), Cells(3, 1)).Address(False, False) & ")"
End Sub

The Range object allows you to refer to the cells you want to add up by referring to the row and column position of the cells – in the above example, the Row is referenced first, and then the column. Cells(1,1) for example is the same as saying A1, and Cells(3,1) is the same as saying A3. 

In both of these instances, when you run the code, a Sum formula will be put into your Excel sheet.

Using the Sum Function With the Worksheet Function Command

Instead of putting the formula in quotation marks, we can use the VBA sum function.

WorksheetFunction.Sum(Range("A1:A3")

To use this function in VBA code, the line of code could be:

Sub TestSum()
Range("A4") = WorksheetFunction.Sum(Range("A1:A3")
End Sub

OR using cell references

Sub TestSum2()
Range("A4") = WorksheetFunction.Sum(Range("A1:A3")
End Sub

In both these instances, when you run the code, it will put the value in the code – NOT a formula!

That may cause issues if the values in A1 to A3 were to change in the future.

In this article you learned how to use the Excel sum function in VBA code, as well as the WorksheetFunction.Sum in VBA code.  The Excel sum function is definitely more flexible in that if your values in the cells in Excel change, then the answer in the sum will change without having to re-run the procedure you have created to sum the cells.