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 = "$A$1" 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 = "$IV$16384" 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 = "$A$16384" 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 "$A$1", 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.
