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.

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:

Result:

Excel-sum-numbers

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:

Output:

Excel-sum-variables

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:

Output:

Excel-sum-decimals

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:

Output:

Excel-sum-cells-scattered

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:

Result:

Excel-sum-range-one

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:

Output:

Excel-sum-range-multipe

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:

Output:

Excel-sum-range-mixed