Microsoft KB Archive/103840

From BetaArchive Wiki



Excel AppNote: Cell Referencing from a Macro (XE0125)

Last reviewed: February 2, 1998
Article ID: Q103840

The information in this article applies to:

  • Microsoft Excel for Windows, versions 3.0, 4.0
  • Microsoft Excel for the Macintosh, versions 3.0, 4.0

This Application Note outlines how to reference cells from a macro and discusses the A1 and R1C1 reference styles. It includes tables that define terms such as "absolute reference" and "relative reference" and contains descriptions of specific macro functions and the types of cell referencing they will accept. The following is the complete text of "Cell Referencing From a Macro," (XE0125).

You can obtain this Application Note from the following sources:

You can obtain this Application Note from the following sources:

  • Microsoft's World Wide Web Site on the Internet
  • The Internet (Microsoft anonymous ftp server)
  • The Microsoft Network (MSN)
  • Microsoft Download Service (MSDL)
  • Microsoft Product Support Services

For complete information, see the "To Obtain This Application Note" section at the end of this article.

  Microsoft(R) Product Support Services Application Note (Text File)
                 XE0125: CELL REFERENCING FROM A MACRO
                                                   Revision Date: 8/93
                                                      No Disk Included
| INFORMATION PROVIDED IN THIS DOCUMENT AND ANY SOFTWARE THAT MAY     |
| ACCOMPANY THIS DOCUMENT (collectively referred to as an Application |
| Note) IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER      |
| EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED      |
| WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR       |
| PURPOSE. The user assumes the entire risk as to the accuracy and    |
| the use of this Application Note. This Application Note may be      |
| copied and distributed subject to the following conditions:  1) All |
| text must be copied without modification and all pages must be      |
| included;  2) If software is included, all files on the disk(s)     |
| must be copied without modification;  3) All components of          |
| this Application Note must be distributed together;  and  4) This   |
| Application Note may not be distributed for profit.                 |
|                                                                     |
| Copyright (C) 1992-1993 Microsoft Corporation.  All Rights Reserved.|
| Microsoft, Microsoft Press, and MS-DOS are registered trademarks    |
| and Windows is a trademark of Microsoft Corporation.                |
| Macintosh is a registered trademark of Apple Computer, Inc.         |
|-------------------------------------------------------------------- |

The following information applies to Microsoft Excel, versions 3.0 and 4.0.

OVERVIEW

This Application Note outlines how to reference cells from a macro and discusses the A1 and R1C1 reference styles. It includes tables that define terms such as "absolute reference" and "relative reference" and contains descriptions of specific macro functions and the types of cell referencing they will accept.

INTRODUCTION

Microsoft Excel has a powerful macro language that allows you to automate frequently performed tasks, customize functions, and create interactive applications. How you reference a cell is an essential part of writing a macro; therefore, to write a successful macro, you must understand the different ways to reference a cell. Macro commands take either relative or absolute references with respect to a cell, a range of cells, or defined ranges. There are a variety of ways to refer to a cell in a macro, depending on whether that cell is on a macro sheet or a worksheet and depending on which sheet is active when you execute the macro.

When you are creating a macro, you'll most often want to refer to a specific cell (absolute) on a worksheet or to a cell in terms of its relationship to the active cell (relative). You should be able to write most macros using absolute references or relative references that are enclosed in quotation marks.

DEFINITIONS

   Term         Definition
   ------------------------------------------------------------------
   Absolute     A reference that is fixed. The reference will not be
   reference     changed if it's copied to another cell, if rows are
                inserted above it, or if columns are inserted to the
                left of it.

   Relative     A reference that will be adjusted if it's copied to
   reference    another cell, if rows are inserted above it, or if
                columns are inserted to the left of it.

   Active       The sheet that you last opened or activated. This
   sheet        can occur prior to running the macro or during the
                macro's execution. If you see more than one sheet on
                your display, the title bar of the active sheet
                and/or scroll bars will be a different color than
                the other sheets. If you see only one sheet, the
                active sheet is at the forefront of your display.

   Referencing  Microsoft Excel uses two types of reference styles:
   style        A1 reference style and R1C1 reference style. You can
                use either style by choosing Workspace from the
                Options menu and selecting the R1C1 check box for
                R1C1 style referencing, or leaving the R1C1 check
                box clear for A1 style referencing. In A1 style,
                columns are labeled with letters, and rows are
                labeled with numbers; in R1C1 style, both rows and
                columns are labeled with numbers. There are absolute
                and relative forms of both styles.

ABSOLUTE REFERENCES

When you want your macro to work with specific cells, you should always use absolute references.

  IMPORTANT: When you are using references that are not enclosed in
  quotation marks, they must be written in the reference style
  currently set for your workspace, either A1 or R1C1. (To set the
  reference style, choose Workspace from the Options menu. For more
  information about reference styles, see the "Referencing Style"
  definition in the "Definitions" section of this Application Note.)
  However, references enclosed in quotation marks must always be in
  R1C1 style regardless of your workspace reference style.

   To enter an absolute         Use this form of   Use this form of
   reference that               A1 style           R1C1 style
   -----------------------------------------------------------------
   Refers to cell A1 on the     $A$1               R1C1
   macro sheet

   Refers to cell A1 on the     !$A$1              !R1C1 or "R1C1"
   active sheet

   Refers to cell A1 on the     Name!$A$1          Name!R1C1 or
   named sheet                                     "Name!R1C1"

RELATIVE REFERENCES

When you enclose a relative reference in quotation marks, the reference is relative to the active cell on the active sheet while the macro is running. Relative references without quotation marks are always relative to the cell containing the macro statement.

  NOTE: When you use a relative reference that is not enclosed in
  quotation marks, make sure that you want this reference to be
  relative to the cell containing the macro statement.

                                                          Use this
   To enter a relative reference that                     reference style
   ----------------------------------------------------------------------
   - Refers to the cell that is one row down and one       "R[1]C[1]"
     column to the right of the active cell on the         "Name!R[1]C[1]"
     active or named sheet.

   - Refers to cell A1 on the macro sheet.                 A1

   - Refers to the cell on the macro sheet that is one     R[1]C[1]
     row down and one column to the right of the macro
     command containing this reference.

   - Refers to cell A1 on the active sheet. The            !A1
     reference, however, is relative to the macro sheet
     cell that contains the reference. It is not
     relative to the active cell on the active sheet. If
     you copy the formula that contains this reference
     to any other cell on the macro sheet, the reference
     will be updated relative to cell A1 on the macro
     sheet. For example, if you copy =SELECT(!A1) in
     cell A5 on your macro sheet and paste it in cell
     A6, !A1 is updated to !A2.

   - Refers to the cell on the active or named sheet       !R[1]C[1]
     that is one row down and one column to the right of   Name!R[1]C[1]
     the macro sheet cell that contains the reference.
     This reference is not relative to the active cell
     on the active sheet.

     For example, if you have GET.FORMULA(!R[1]C[1])
     in cell A5 on the macro sheet, when you run this
     macro while another sheet is active, the formula
     in cell B6 on that sheet is returned regardless
     of the active cell. Because the GET.FORMULA
     function is in cell A5, the reference evaluates
     to B6, one row down and one column to the right
     of A5. Due to the exclamation point and in the
     second case, the filename, Microsoft Excel
     appends the active sheet name or the given sheet
     name to the reference.

  NOTE: A worksheet or another macro sheet must be active when the
  command macro encounters a reference preceded by an exclamation
  point.

WORKING WITH REFERENCES THAT ARE ENCLOSED IN QUOTATION MARKS

A limited number of functions accept references enclosed in quotation marks, that is, in the form "R1C1" (absolute) or "R[1]C[1]" (relative). Because they are enclosed in quotation marks, these references are treated as text and are not evaluated as references until you run your macro. If you attempt to use this method of referencing a cell in a function that is not designed to accept it, you will receive a macro error or incorrect results. For example, the formula =IF("R1C1"=100) will always return FALSE because "R1C1" is not evaluated as a reference. Instead, it is evaluated as a literal text string.

The following table lists some of the more common functions that can use absolute ("R1C1") and/or relative ("R[1]C[1]") references.

   Function   Description
   -----------------------------------------------------------------------
   SELECT()   Can use both absolute and relative references in this style.

   TEXTREF()  Can use both absolute and relative references in this style.

   ABSREF()   Can use relative references only ("R[1]C[1]").

   FORMULA()  Can use both absolute and relative references in this style.

TEXTREF() is an extremely useful function because in most cases in which a function is not designed to accept a reference enclosed in quotation marks, TEXTREF() can convert the cell reference to the style of reference currently set for the workspace. For example, you can use the formula =IF(TEXTREF("R1C1")=100) in your macro to evaluate the IF() statement correctly. If cell A1 contains the value 100, the function will return TRUE.

TIPS FOR USING THE SELECT() FUNCTION

Most macros need to select a particular cell or a range of cells in order to perform a certain task, whether it be to select a range of cells so they can be copied and pasted to a new range or to select one cell and determine what value that cell holds. The following table includes some tips for selecting cells from within a macro.

               Use this
To do this     function          Explanation
Select cell    =SELECT(!$A$1)    =SELECT(Name!$A$1) or =SELECT(Name!A1)
A1 on the      =SELECT(!A1)      will attempt to select cell A1 on the
active sheet   -or-              named sheet. If this sheet is not the
                =SELECT("R1C1")  active worksheet, this command will
                                 result in a macro error. Because SELECT()
                                 will work only on the active worksheet,
                                 it is not necessary to specify a filename.
                                 Each of the listed functions will select
                                 cell A1 on the active sheet.

Select cell    =SELECT(A1)       If the macro sheet is not the active
A1 on the                        sheet, this will result in a macro
macro sheet                      error.


Select cell    =SELECT("R1C1")   =SELECT(R1C1) without the reference
R1C1 on the                      enclosed in quotation marks will select
currently                        R1C1 only on the macro sheet and only if
active                           you are using the R1C1 reference style.

worksheet

Return         External          Activating worksheets and selecting cells
information    references        can cause a macro to run slowly. Rather
about a        or the            than select a cell, you can use functions
particular     OFFSET()          to derive needed information about that
cell or        function          cell and/or its contents. For example, to
range of                         return the value in the cell that is one
cells                            row below and one column to the right of
                                 the active cell, use
                                 =OFFSET(ACTIVE.CELL(),1,1) instead of
                                 =SELECT("R[1]C[1]").

Make a         FORMULA.GOTO()    =FORMULA.GOTO(Name!$A$1) or
selection on                     =FORMULA.GOTO("Name!R1C1") will activate
a sheet that                     the named worksheet and select cell A1.

is not currently active

MACRO DEBUGGING

Most command-equivalent macro functions return the value FALSE before they are run, TRUE if they are run successfully, and FALSE or an error value if they don't run successfully. If your macro functions are returning an incorrect or unexpected result or if one or more macro commands cause your macro to halt in error, you can use any combination of the following methods to isolate and resolve the problem.

Use the Step and Evaluate Commands

The step feature allows you to step through and evaluate your macro commands line by line. To use the step feature, run your macro by choosing Run from the Macro menu. After you select your macro from the list, choose the Step button. You can then either step through or evaluate the individual lines of your macro code. Step takes you to the next macro command. Evaluate calculates each nested function within a line of macro code, one argument at a time--this is a useful way to isolate a problem within a specific line of a macro. While you are in step mode, you cannot edit your macro. To exit step mode and edit your macro, choose the Halt button.

Use Key Commands to Evaluate Portions of a Macro Statement

To evaluate a portion of a statement or an entire line of code in your macro in the formula bar, select the area you want to evaluate and press F9 or, if you are using Microsoft Excel for the Macintosh(R), press and hold down the COMMAND key and then press the EQUAL SIGN (=) key. Microsoft Excel will immediately calculate the selected portion of your function and display the value in the formula bar.

  CAUTION: Be sure to press the ESC key after you view the value;
  otherwise the original formula will be replaced with the displayed
  value.

Use Key Commands to View Values Returned by Macro Statements

To view the value returned by all statements on a macro sheet, press and hold down the CTRL key, or the COMMAND key if you are using Microsoft Excel for the Macintosh, and then press the ACCENT GRAVE (`) key. CTRL+ACCENT GRAVE (Windows) or COMMAND+ACCENT GRAVE (Macintosh) toggles between the view values mode and the view formulas mode. Another way to toggle between view values and view formulas is to choose Display from the Options menu and select or clear the Formula check box (when a macro sheet is active, the default view is view formulas).

Run a Portion of the Macro

If your macro is long, you may want to test small portions of it to isolate problems. To divide your macro into more manageable sections, do the following:

  1. Activate your macro sheet. At the end of the range of macro code that you want to test, insert a row and type =HALT(). This will stop the macro's execution at this point in the macro.
  2. Activate the document from which you are running your macro and choose Run from the Macro menu.
  3. With the dialog box active, choose the macro sheet name from the Window menu and select the cell containing the first line of macro code that you want to test. This will place the macro cell reference in the Run Macro dialog box.
  4. Choose OK or Step.

The macro will stop where it encounters the HALT() function.

MORE INFORMATION

For additional information on cell referencing, macro functions, and writing macros, see the "Complete Guide to Microsoft Excel Macros" by Charles Kyd and Chris Kinata, published by Microsoft Press(R), or "Microsoft Excel Macros Step by Step" by Steve Wexler and Julianne Sharer.

TO OBTAIN THIS APPLICATION NOTE

Application Notes are available by modem from the Microsoft Download Service (MSDL), which you can reach by calling (425) 936-6735. This service is available 24 hours a day, 7 days a week. The highest download speed available is 9600 bits per second (bps). For more information about using the MSDL, call (800) 936-4100 and follow the prompts. To obtain XE0125, download XE0125.EXE (MS-DOS/Windows) or XE0125.SEA (Macintosh). XE1025.EXE and XE0125.SEA are compressed, self-extracting files. After you download XE0125, run it to extract the file(s) it contains. If you are unable to access the source(s) listed above, you can have this Application Note mailed or faxed to you by calling Microsoft Product Support Services Monday through Friday, 6:00 A.M. to 6:00 P.M. Pacific time at (425) 635-7070 or (425) 635-7080. If you are outside the United States, contact the Microsoft subsidiary for your area. To locate your subsidiary, see the Microsoft World Wide Offices Web site at:

      http://www.microsoft.com/worldwide/default.htm


            
        



Additional query words: 3.00 4.00

Keywords : kbappnote
Version : WINDOWS:3.0,4.0; MACINTOSH:3.0,4.0
Platform : MACINTOSH WINDOWS


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.

Last reviewed: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.