VBA SUM

What Is VBA SUM Function?

The VBA Sum function is a method used to calculate the sum of values in a range or an array. It is similar to the SUM function in Excel, but it’s used within VBA code to perform calculations programmatically.

Look at the example below. A subroutine is created to perform the Sum cells in a given range. The Range is from cells A1 to A6.

VBA-SUM-Definition
VBA-SUM-Definition1

The sum is printed in the Immediate tab as follows. The sum of the values from cells A1 to A6 is 196.

VBA-SUM-Definition2
Key Takeaways
  • The Sum function in VBA is similar to its Excel counterpart but is used within VBA code for programmatic calculations. It can handle both single-cell ranges and multi-cell ranges.
  • It supports both numerical values and cell references as arguments.
  • You can sum values from different ranges, worksheets, or workbooks.
  • The function returns the sum of the provided values or ranges as a result.
  • Ensure that the ranges provided are properly formatted and include all the correct information, otherwise, it may lead to unexpected results.
  • Ranges can be contiguous or non-contiguous and can also be named ranges.

Syntax

There are two ways in which you can call the VBA Sum formula. They are:

Application.WorksheetFunction.Sum([Range])

Or

WorksheetFunction.Sum([Range])

Where,

Range: This is the range of cells or array of values for which you want to calculate the sum.

How to use VBA SUM function?

Here is a simple example of how to use the VBA sum column to last row formula.

Follow these simple steps to replicate this in your Excel Workbook.

  1. In the newly opened Excel Workbook, choose the “Developer” icon in the Excel toolbar and click on it.

    Step 1(1)

    In the Developer section, select “Visual Basic”. It opens the VBA editor.

    Step-1(2)

    In the editor, in the title bar, click the “Insert” button and select the “Module” option.

    Step-1(3)

  2. Name the subroutine to find the sum of an entire row.

    Step 2(1)

    Step-2(2)

  3. Call the VBA Sum function for the entire row by naming the row number in the Range function, as shown below. Store this value in another cell, F7.

Code:

Sub RowSum()

    Range(“F7”) = WorksheetFunction.Sum(Range(“10:10”))

End Sub

Step 4: Click “F5” or the “Run” icon on the activity bar in the Excel VBA Editor to run the subroutine.

VBA SUM-Step-4

The sum is printed in cell F7.

Examples

See the different ways in which you can use the VBA Sum function to use the VBA sum cells formula.

Example #1

Suppose you have an Excel table with a list of items you’ve purchased. You want to find the total amount you want to pay for the bill. Using a table is more advantageous since this makes it more dynamic. You can call the range of the table and find its sum using the VBA Sum function.

VBA-SUM-Example-1

Step 1: Start the procedure by naming the subroutine to find the sum using the VBA SUM cells formula to do so.

VBA SUM-Example-1-Step-1

Step 2: Define a ListObject variable and define the table under that.

Example-1-Step-2

Step 3: Get all the values under the column of the table you want to find the sum of. Using ListColumns, name the column and take all the data under the column.

Example-1-Step-3

Step 4: Define an Integer variable where you can store the sum of all the values. Then, call the VBA Sum formula of the range of values stored in the previous step.

Example-1-Step-4

Step 5: Print the total price in a Message Box in VBA. Call the vbInformation icon for cosmetic purposes.

Example-1-Step-5

Code:

Sub SumOfTable()

    Dim tbl As ListObject

    Set tbl = ThisWorkbook.Sheets(“Sheet1”).ListObjects(“Table1”)

    Dim columnRange As Range

    Set columnRange = tbl.ListColumns(“Price”).DataBodyRange

    Dim SumRan As Integer

    SumRan = WorksheetFunction.Sum(columnRange)

    MsgBox “The amount to be paid is: ” & SumRan, vbInformation

End Sub

Step 6: Press “F5” to run the code. The output is displayed as shown below:

Example-1-Step-6

Example #2

Here, you will see an example of how to find the sum of two disjointed ranges together. Suppose you have two separate bills for electricity and utilities. You want to find the total amount to be paid. Both being in different ranges isn’t a problem when using the VBA Sum formula.

VBA-SUM-Example-2

Step 1: Format the specific cells to currency (USD, etc.) Right-click on the cells and select “Format Cells.”

VBA SUM-Example-2-Step-1

Step 2: In the popup, select “Currency” in the categories and select “$ English (United States).”

VBA SUM-Example-2-Step-2

Step 3: Go to the VBA Editor and name the subroutine to print the total cost.

Example-2-Step-3

Step 4: Define a Range variable and initialize it with the range of the first table, or the “Electricity” table.

Example-2-Step-4

Step 5: Similarly, declare a Range variable to store the second range.

Example-2-Step-5

Step 6: Call the VBA Sum formula and add the two ranges as the argument in the Sum function. Print the result in cell “D13.”

Example-2-Step-6

Code:

Sub SumOfMultipleRanges()

    Dim rng1 As Range

    Set rng1 = Range(“C2:C11”)

    Dim rng2 As Range

    Set rng2 = Range(“E2:E11”)

    Range(“D13”) = WorksheetFunction.Sum(rng1, rng2)

End Sub

Step 7: Click the green arrow button when you want to run the code. Go back to the worksheet after running the subroutine.

VBA SUM-Example-2-Step-7

Example #3

Using the VBA Sum formula, you can not only find the sum of a specific range but also the sum of an entire row or column. You can see it in this example.

1. Suppose you have a lot of values in the column “I,” and you don’t know the size of the range; you can sum the entire row instead.

VBA-SUM-Example-3

Step 1: Define a subroutine to find the sum of the entire column.

Example-3-Step-1

Step 2: Print the sum value in cell “N8.” Specify the worksheet if you have multiple ones. To use the entire column, use “I:I” as an argument in the VBA Sum formula.

Example-3-Step-2

Step 3: Print the value in cell “N8” in a MsgBox function.

VBA SUM-Example-3-Step-3

Code:

‘Entire column

Sub ColSum()

    ThisWorkbook.Sheets(“Sheet1”).Range(“N8”) = WorksheetFunction.Sum(Range(“I:I”))

    MsgBox “The sum of column I is: ” & ThisWorkbook.Sheets(“Sheet1”).Range(“N8”).Value, vbInformation

End Sub

Step 4: Run the above subroutine to print the sum value in the specified cell and a Message Box.

Example-3-Step-4
VBA SUM-Example-3-Step-4(1)
VBA SUM-Example-3-Step-4(2)

2. Similarly, suppose you have a set of values in a row that cannot be quantified, you can use the VBA Sum function to find the sum of the entire row using the Worksheet function ‘SUM.’

Example-3(2)

Step 1: To start with finding the sum of an entire row, name the subroutine appropriately.

Example-3(2)-Step-1

Step 2: Define a Double variable to store the sum value after adding the values of an entire row. To call an entire row, you use the row number in the beginning and the end of the range; this makes it so that the entire row is calculated, as shown.

Example-3(2)-Step-2

Note: Calling an integer variable may cause overflow errors in VBA. For larger values, use the Double datatype.

Step 3: Print the sum of the entire row in a Message Box function.

Example-3(2)-Step-3

Code:

Sub FullRowSum()

    Dim rSum As Double

    rSum = WorksheetFunction.Sum(Range(“16:16”))

    MsgBox “The sum of row 16 is: ” & rSum, vbInformation

End Sub

Step 4: The subroutine is run to find the sum of the entire row.

VBA SUM-Example-3(2)-Step-4

Example #4

Consider an example where you have an array of variables. You want to find their sum and print it. This can be done by calling the Sum function in Excel VBA.

Step 1: Start by naming the subroutine to find the sum of all elements in an array.

VBA SUM-Example-4-Step-1

Step 2: Initialize an integer array and its size.

Example-4-Step-2

Step 3: Populate the empty array with values.

Example-4-Step-3

Step 4: Call the VBA Sum function for the array and print it in a Message box.

Example-4-Step-4

Code:

Sub FullRowSum()

    Dim rSum As Double

    rSum = WorksheetFunction.Sum(Range(“16:16”))

    MsgBox “The sum of row 16 is: ” & rSum, vbInformation

End Sub

Step 5: Run the above subroutine to find the sum of the array.

VBA SUM-Example-4-Step-5

Example #5

Suppose you have two tables in Excel. In a shop, when you buy an item, there are extra delivery fees. Depending on the corresponding product and the overhead prices, you are supposed to find the sum of each bill for different overhead prices for easier tracking.

VBA-SUM-Example-5

In this example, there are 2 overhead charges, 300 and 400. You need to sum the corresponding values separately and print them on the sheet. For this example, instead of using the Sum function, you can use the VBA SumIF function.

VBA-SUM-Example-5(1)
VBA-SUM-Example-5(2)

Step 1: Define a subroutine to find the sum of a range with specifications.

VBA SUM-Example-5-Step-1

Step 2: Define the first range variable to store the first sum (with 300).

Example-5-Step-2

Step 3: Find the sum of both the ranges. Here, the specification is that only the values with 300 are taken from column ‘A’ and added to column ‘C’.

Example-5-Step-3

Step 4: Similarly, set another range to print the sum (for overhead charges of 400).

Example-5-Step-4

Step 5: Like Step 3, call the SUMIF function and set it so that only values equal to 400 are taken from column ‘A’ and added with the adjacent values from column ‘C.’

Example-5-Step-5

Step 6: Print both the sums in a message box. For improved readability, print both answers in different lines using vbCrLf, which is a constant in Excel VBA used to print a new line.

Example-5-Step-6

Code:

Sub CriteriaRange()

    Dim sumRng As Range

    Set sumRng = ThisWorkbook.Sheets(“Sheet3”).Range(“F7”)

    sumRng = WorksheetFunction.SumIf(Range(“A1:A10”), 300, Range(“C1:C10”))

    Dim sumRng2 As Range

    Set sumRng2 = ThisWorkbook.Sheets(“Sheet3”).Range(“F12”)

    sumRng2 = WorksheetFunction.SumIf(Range(“A1:A10”), 400, Range(“C1:C10”))

    MsgBox “The price with $300 tax for each object: ” & sumRng.Value & vbCrLf & _

           “The price with $400 tax for each object: ” & sumRng.Value, vbInformation

End Sub

Step 7: Run the subroutine to print the answers in the worksheet and print it in a message box.

VBA SUM-Example-5-Step-7
VBA SUM-Example-5-Step-7(1)

With this, you’ve successfully found the sums of multiple ranges based on user-defined criteria.

Important Things To Note

  1. Ensure that the ranges provided to the Sum function are correctly specified and do not contain any errors.
  2. Consider using named ranges in VBA for better readability and maintenance of your code.
  3. Check for potential errors or edge cases in your data before applying the Sum function.
  4. Do consider the performance implications, mainly when summing large ranges or across multiple worksheets.
  5. Don’t forget to validate your input data before applying the Sum function to avoid incorrect results.
  6. Avoid using the Sum function on ranges that include merged cells in excel or hidden rows/columns, as it may lead to unexpected results.
  7. Don’t rely solely on the Sum function for complex calculations; consider using other functions or methods if needed.

Frequently Asked Questions (FAQs)

1. Can I use the VBA SUM function with a non-contiguous range?

Yes, you can use the VBA SUM function with non-contiguous ranges by providing multiple range references separated by commas. Follow this example below:

Dim total As Double
total = Application.WorksheetFunction.Sum(Range(“A1:A5”), Range(“C1:C5”))

2. Can I use the VBA SUM function with named ranges?

Yes, you can use the VBA SUM function with named ranges by providing the names of the ranges separated by commas. Suppose you have a table named “Table1”, you can call the range of one of the columns and find its sum as seen below:

Dim tbl As ListObject
Set tbl = ThisWorkbook.Sheets(“Sheet1”).ListObjects(“Table1”)
Dim columnRange As Range
Set columnRange = tbl.ListColumns(“Price”).DataBodyRange
Dim SumRan As Integer
SumRan = WorksheetFunction.Sum(columnRange)

3. Can I use the VBA SUM function in VBA to sum values from multiple worksheets?

Yes, you can use the VBA SUM function to sum values from multiple worksheets by providing references to the ranges from different worksheets. See this example for more context:

Dim total As Double
total = Application.WorksheetFunction.Sum(Worksheets(“Sheet1”).Range(“A1:A5”), Worksheets(“Sheet2”).Range(“A1:A5”))

4. Are there any alternatives to the SUM function in VBA?

Yes, alternatives to the SUM function in VBA include using a loop to iterate through cells or array formulas with the Evaluate method.

Download Template

This article must help us understand the VBA SUM formula and examples. You can download the template here to use it instantly.

Guide to VBA SUM in Excel. Here we explain how to create & use sum as method, along with examples & downloadable excel template. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *