The Sum() Function in VBA

We will introduce how to use sum in VBA.

The Sum Function in VBA

The sum is the most commonly used function in excel. This function is very helpful because we can use the sum to get the sum from our financial sheets.

We will learn how to use the sum function in VBA, assign the result to a variable, and use the sum function in single or multiple ranges. We can easily use the sum function using the WorksheetFunction.

Let’s have an example where we will input some demo data into an excel sheet and get the sum of that data.

Code:

# VBA
Sub sumFunction()
Dim result As Double
result = WorksheetFunction.Sum(Range("A2:A8"))
MsgBox "Total is " & result
End Sub

Output:

We can easily get the sum of the range using the sum function. If we want to get the result of the sum of two ranges, we can use the same function.

Code:

# VBA
Sub sumFunction()
Dim result As Double
result = WorksheetFunction.Sum(Range("A2:A8"), Range("B2:B8"))
MsgBox "Total is " & result
End Sub

Output:

We can get the sum of multiple ranges using the same function. We can use more than 2 ranges to get the sum and assign the value of the result to a cell in excel.

Let’s have an example in which we will assign the value of the result to a cell.

Code:

# VBA
Sub sumFunction()
Dim result As Double
result = WorksheetFunction.Sum(Range("A2:A8"), Range("B2:B8"))
Range("C2").value(result)
End Sub

Output: