Summing values is pretty simple in Excel with a spreadsheet and the SUM() function. You just set a cell to have a formula, ‘=SUM([Range]),’ and you can sum the values in any Range. But how can you the same in VBA?
It took me a bit of digging to figure this one one, surprisingly. I wanted to know if VBA had the same method, where you could specify a range and get the entire sum for the cell’s in that Range.
I was not having any luck at first, so I thought I was going to have to iterate over the Range, cell by cell, and sum all the values manually. But then I thought, “How can normal do this better than VBA?” Turns out, I finally found my answer in the form of Worksheet functions!
Worksheet functions are extremely powerful, and anyone familiar with Excel spreadsheets will know a lot o these functions once they see them. Let’s take a look at the SUM() function!
VBA Sum
Say you had a few numbers in some cells:
Create a macro and paste the following code:
Sub VBASum()
Worksheets("Sheet1").Cells(1, 4).Value = WorksheetFunction.Sum(Range("A1:C1"))
End Sub
This will write the sum of the cells in the 4th cell of the first row. Run the macro and:
Voila! Just like the SUM() function in normal Excel. Simply pass in a Range as the parameter, and you can get your sum in one line.