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:

sum in vba first example assign value to a variable

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:

sum in vba second example using multiple ranges and assign value to a variable

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:

using sum function in VBA and assign the value to a cell

Write for us
DelftStack articles are written by software geeks like you. If you also would like to contribute to DelftStack by writing paid articles, you can check the write for us page.

Related Article - VBA Function

  • Exit Sub in VBA
  • Add New Line in VBA
  • VLOOKUP Function in VBA