# VBA Sum Function [Ranges, Variables, Numbers]

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

**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:**