Visual Basic Example to Total Rows and Columns in an Array (113940)
The information in this article applies to:
- Microsoft Excel 97 for Windows
This article was previously published under Q113940 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 INFORMATIONMicrosoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
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:
- 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.)
- Select the TotalRowsAndColumns macro, and then click Run.
NOTE: This macro does not enter any formulas into your worksheet, so if
the numbers in the range being totaled change, you must run the macro
again.
Macro Example
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.
Dim r As Integer
Dim c As Integer
Dim i As Integer
Dim j As Integer
Dim myArray As Variant
' 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
With Selection.CurrentRegion
r = .Rows.Count
c = .Columns.Count
'resize for totals row and column and place into array
myArray = .Resize(r + 1, c + 1)
' 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 i = 1 To r
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
Next i
' Return the array, which now contains an extra row and column
' for the totals, to the worksheet.
.Resize(r + 1, c + 1) = myArray
End With
End Sub
To perform a similar operation to the one performed in this example, you
can modify the macro code. For example, to subtract, multiply, or divide
the values contained in the selected range of cells, you will need to
change the mathematical operator.
Modification Type: | Minor | Last Reviewed: | 10/11/2006 |
---|
Keywords: | kbProgramming KB113940 |
---|
|