XL: Visual Basic Example to Total Rows and Columns in an Array

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows

SUMMARY

In Microsoft Excel, you can use arrays to calculate and manipulate data in a worksheet. You can also use Visual Basic for Applications to store the values from a range of cells into a Visual Basic array.

The sample macro code in this article adds an additional column and row to a rectangular region of cells that will contain totals for each row and column in that region.

MORE INFORMATION

The following Visual Basic Code reads data from the current region of cells surrounding the active cell on the active worksheet. The macro stores the data within an array, then sums each row and column, placing the output on the worksheet. The size of the array is determined by the number of cells in the current region.

To run the macro:

1. Highlight a cell or range of cells within the region you want to sum, on the Tools menu, point to Macro, and click Macros. (In versions earlier than 97, click Macro on the Tools menu.)

2. Select the TotalRowsAndColumns macro, and then click Run.

Macro Example

Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

Sub TotalRowsAndColumns()

' This macro assumes that you have selected any cell or group of
' cells within a rectangular region of cells that you would like to
' have totaled. The totals will appear in the row below and the
' column to the right of the current region.

' Declaring myArray as a Variant prepares it to receive a range of
' cells. At that point it is transformed automatically into an array
' with beginning subscript myArray(1,1).

'Refer to the region surrounding the current selection

r = .Rows.Count c = .Columns.Count

'resize for totals row and column and place into array

' The following 10 lines of macro code (including comments)
' are what is commonly referred to
' as a Nested Loop. The variable i keeps track of the row number,
' while j keeps track of the column number. Every time j cycles
' through the available columns, i gets incremented by one and j
' starts the cycle from one to c all over again.

   For j = 1 To c
      'total for row i
      myArray(i, c + 1) = myArray(i, c + 1) + myArray(i, j)
      'total for column j
      myArray(r + 1, j) = myArray(r + 1, j) + myArray(i, j)
      'grand total
      myArray(r + 1, c + 1) = myArray(r + 1, c + 1) + myArray(i, j)
   Next j

' Return the array, which now contains an extra row and column
' for the totals, to the worksheet.

End With

End Sub