VBA Sum Function [Ranges, Variables, Numbers]

How to use Excel Sum function in VBA

The Excel SUM function can be used in VBA by WorksheetFunction object.

It takes up to thirty arguments i.e.

expression.Sum (Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7, Arg8, Arg9, Arg10, Arg11, Arg12, Arg13, Arg14, Arg15, Arg16, Arg17, Arg18, Arg19, Arg20, Arg21, Arg22, Arg23, Arg24, Arg25, Arg26, Arg27, Arg28, Arg29, Arg30)

Each argument can be a number, numeric variable, cell, or Range of cells in an Excel sheet.

We will show you using different arguments in the examples below.

An example of using numbers in the Sum function

In the first example, we provided three numbers as arguments to the Sum function. The result is displayed in the message box:

Sub sum_ex()

MsgBox "The Total is: " & (WorksheetFunction.Sum(10, 20, 30))

End Sub

Result:

Using VBA variables in the Sum function example

Now we have three variables: two Integer types and one Long type.

We passed these variables to the Sum function as arguments and the result is displayed in the message box.

VBA code:

Sub sum_ex()

Dim num1 As Integer
Dim num2 As Integer
Dim num3 As Long


num1 = 100
num2 = 200
num3 = 700

MsgBox "The Total is: " & (WorksheetFunction.Sum(num1, num2, num3))

End Sub

Output:

Example of using decimal numbers

Floating point numbers can also be passed to the Sum to get the total.

We declared two Single type and a double type variable and assigned values.

Then passed all three variables to the Sum function and see the output:

Sub sum_ex()

Dim flt1 As Single
Dim flt2 As Single
Dim flt3 As Double

flt1 = 5.54
flt2 = 6.5
flt3 = 9.2

MsgBox "The Total of Decimal Numbers: " & (WorksheetFunction.Sum(flt1, flt2, flt3))

End Sub

Output:

Using Excel cells in the sum function

We passed five different cells to the Sum function and get the total.

The sheet containing those cells and their sum is displayed in the output below:

Sub sum_ex()

MsgBox "Total of Five Cells: " & (WorksheetFunction.Sum(Range("A2"), Range("A3"), Range("B3"), Range("C3"), Range("D3")))

End Sub

Output:

Using one range of cells in the Sum function

We used the same sheet as above. Rather than providing cells separately, we used a single range containing all those cells i.e. A2:D3.

In the sum function, we passed that range to get the total of all cells, and the result is displayed in the message box.

VBA code:

Sub sum_ex()

MsgBox "Total of single range: " & (WorksheetFunction.Sum(Range("A2:D3")))

End Sub

Result:

Using multiple ranges in the Sum function example

For this example, we created the following ranges in the VBA:

Range 1 = A2:A6

Range 2: C3:C4

Range 3: E2:E6

We passed all to the sum functions and see the result:

Sub sum_ex()

Dim rng1 As Range
Dim rng2 As Range
Dim rnd3 As Range

Set rng1 = Range("A2:A6")
Set rng2 = Range("C2:C4")
Set rng3 = Range("E2:E6")

MsgBox "Total of three ranges: " & (WorksheetFunction.Sum(rng1, rng2, rng3))

End Sub

Output:

What if range contains text/string with numbers

Sum function simply ignores and gets the total of all numeric values in the mixed range.

In the example below, we have A2:A6 cells that contain numbers and text.

See what we get with the Sum function:

Sub sum_ex()
Dim rng1 As Range

Set rng1 = Range("A2:A6")

MsgBox "Total of Mixed Range: " & (WorksheetFunction.Sum(rng1))

End Sub

Output: