Microsoft KB Archive/124222

XL: Using AutoSum in a Visual Basic Macro Article ID: Q124222 Revision Date: 13-SEP-1996

The information in this article applies to:


 * 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

SUMMARY In Visual Basic, Applications Edition, there is no function to emulate the behavior of the AutoSum tool. However, you can activate this tool from a module by using the SendKeys method to send the keyboard shortcut to start the AutoSum tool, or you can create a macro that sums a contiguous column of numbers. For examples of these procedures, see the sample Visual Basic code in the “More Information” section of this article.

MORE INFORMATION Microsoft provides examples of Visual Basic 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. This Visual Basic procedure is provided ‘as is’ and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose. Note that a line that is preceded by an apostrophe introduces a comment in the code–comments are provided to explain what the code is doing at a particular point in the procedure. Note also that an underscore character (_) indicates that code continues from one line to the next. You can type lines that contain this character as one logical line or you can divide the lines of code and include the line continuation character. For more information about Visual Basic for Applications programming style, see the “programming Style in This Manual” section in the “Document Conventions” section of the “Visual Basic User’s Guide.”

Sample Visual Basic Macro 1 The following Visual Basic macro activates the AutoSum function in the active cell:

Sub SumRange Application.SendKeys “%=” ’sends the key combination ALT+= Application.SendKeys “~” ’sends the enter key

End Sub

Sample Visual Basic Macro 2

’ Auto_Sum Macro

’ This function sums a group of numbers in either a column or a row ’ that the active cell belongs to. It will first search for a number in ’ the cell directly above the active cell. If it does not find a number ’ it will then search the cell immediately to the right of the active cell, ’ then to the left, and finally the cell below for a number. The first ’ number it finds in the above search, determines which column or row ’ will get summed. If a number is not found in the active cell’s immediate ’ neighboring cells, then a message stating this is displayed. ’ ’ This macro assumes that the active cell is a blank cell in which you ’ want the sum to appear. You may receive a sum of 0, if you ’ have a formula in a cell being summed.

Sub Auto_Sum

' check if address of activecell is the top left most cell If ActiveCell.Address = &quot;$A$1&quot; Then

' if the cell to the right of cell is a number, sum cells _ to right of activecell If Len(ActiveCell.Offset(0, 1).Value) > 0 And _ IsNumeric(ActiveCell.Offset(0, 1).Value) Then Call SumRight ' else sum cells below activecell ElseIf Len(ActiveCell.Offset(1, 0).Value) > 0 And _ IsNumeric(ActiveCell.Offset(1, 0)) Then Call SumBelow Else Call msg End If ’ check if address of activecell is the top most right cell ElseIf ActiveCell.Address = “$IV$1” Then

' if the cell to the left of cell is a number, sum cells _ to left of activecell If Len(ActiveCell.Offset(0, -1).Value) > 0 And _ IsNumeric(ActiveCell.Offset(0, -1).Value) Then Call SumLeft ' else sum cells below activecell ElseIf Len(ActiveCell.Offset(1, 0).Value) > 0 And _ IsNumeric(ActiveCell.Offset(1, 0)) Then Call SumBelow Else Call msg End If

' check if address of activecell is the bottom most right cell ElseIf ActiveCell.Address = &quot;$IV$16384&quot; Then

' if the cell to the above of cell is a number, sum cells _ to right of activecell If Len(ActiveCell.Offset(-1, 0).Value) > 0 And _ IsNumeric(ActiveCell.Offset(-1, 0).Value) Then Call SumAbove ' else, if cell to left is a number, sum cells to left ElseIf Len(ActiveCell.Offset(0, -1).Value) > 0 And _ IsNumeric(ActiveCell.Offset(0, -1)) Then Call SumLeft Else Call msg End If

' check if address of activecell is the bottom most left cell ElseIf ActiveCell.Address = &quot;$A$16384&quot; Then

' if the cell above is a number, sum cells above of activecell If Len(ActiveCell.Offset(-1, 0).Value) > 0 And _ IsNumeric(ActiveCell.Offset(-1, 0).Value) Then Call SumAbove ' else, if cell to right is a number, sum cells to right ElseIf Len(ActiveCell.Offset(0, 1).Value) > 0 And _ IsNumeric(ActiveCell.Offset(0, 1)) Then Call SumRight Else Call msg End If

' if activecell not &quot;$A$1&quot;, check if activecell is in row 1 ElseIf ActiveCell.Row = 1 Then If IsNumeric(ActiveCell.Offset(0, 1)) And _ Len(ActiveCell.Offset(0, 1).Value) > 0 Then Call SumRight ElseIf Len(ActiveCell.Offset(0, -1)) > 0 And _ IsNumeric(ActiveCell.Offset(0, -1)) Then Call SumLeft ' else, if number in below cell, sum cells below activecell ElseIf Len(ActiveCell.Offset(1, 0).Value) > 0 And _ IsNumeric(ActiveCell.Offset(1, 0)) Then Call SumBelow Else Call msg End If ’ if activecell is in column 1 ElseIf ActiveCell.Column = 1 Then ’if cell above the activecell is a number, sum cells from above

activecell If Len(ActiveCell.Offset(-1, 0).Value) > 0 And  IsNumeric(ActiveCell.Offset(-1, 0)) Then Call SumAbove ’ elseif cell to right of activecell  is a number, sum cells to right of active cell ElseIf Len(ActiveCell.Offset(0, 1).Value) > 0 And  IsNumeric(ActiveCell.Offset(0, 1)) Then Call SumRight ’ else, sum cells below activecell ElseIf Len(ActiveCell.Offset(1, 0).Value) > 0 And  IsNumeric(ActiveCell.Offset(1, 0)) Then Call SumBelow Else Call msg End If

’ else, if active cell in column 256 (last column) ElseIf ActiveCell.Column = 256 Then If Len(ActiveCell.Offset(-1, 0).Value) > 0 And _ IsNumeric(ActiveCell.Offset(-1, 0)) Then Call SumAbove ’else, if cells to left of activecell is a number, sum cells to

left ElseIf Len(ActiveCell.Offset(0, -1).Value) > 0 And  IsNumeric(ActiveCell.Offset(0, -1)) Then Call SumLeft ’ else, sum cells below active cell ElseIf Len(ActiveCell.Offset(1, 0).Value) > 0 And  IsNumeric(ActiveCell.Offset(1, 0)) Then Call SumBelow Else Call msg End If

ElseIf ActiveCell.Row = 16384 Then ’ if cell above activecell has a number, sum numbers above If Len(ActiveCell.Offset(-1, 0)) > 0 And  IsNumeric(ActiveCell.Offset(-1, 0)) Then Call SumAbove ’ else, if the cell to the right of the activecell is a number,  sum cells to right of activecell ElseIf Len(ActiveCell.Offset(0, 1)) > 0 And  IsNumeric(ActiveCell.Offset(0, 1)) Then Call SumRight ’ else, if cell to left is a number ElseIf Len(ActiveCell.Offset(0, -1)) > 0 And  IsNumeric(ActiveCell.Offset(0, -1)) Then Call SumLeft ’ else sum cells below activecell Else Call msg End If

’ if activecell is somewhere in the “middle” of the sheet  then if cell above activecell is a number sum cell above ElseIf Len(ActiveCell.Offset(-1, 0).Value) > 0 And  IsNumeric(ActiveCell.Offset(-1, 0)) Then Call SumAbove ’ else, if cells to right of activecell is a number, sum cells to right ElseIf Len(ActiveCell.Offset(0, 1).Value) > 0 And _ IsNumeric(ActiveCell.Offset(0, 1)) Then Call SumRight ‘else, if cells to left of activecell is a number, sum cells to left ElseIf Len(ActiveCell.Offset(0, -1).Value) > 0 And  IsNumeric(ActiveCell.Offset(0, -1)) Then Call SumLeft ’ else, if cells below active cell is a number, sum cells below ElseIf Len(ActiveCell.Offset(1, 0).Value) > 0 And  IsNumeric(ActiveCell.Offset(1, 0)) Then Call SumBelow’ else, no numbers to sum show message Else Call msg End If

End Sub

For additional information, please see the following article in the Microsoft Knowledge Base:

ARTICLE-ID: Q103164 TITLE : Excel: How to Use AutoSum in a Macro

KBCategory: kbprg kbcode KBSubcategory:

Additional reference words: 5.00 5.00c 7.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1996.