Excel AppNote: Cell Referencing from a Macro (XE0125)Last reviewed: February 2, 1998Article ID: Q103840 |
The information in this article applies to:
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(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.
OVERVIEWThis 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.
INTRODUCTIONMicrosoft 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 REFERENCESWhen 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 REFERENCESWhen 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 MARKSA 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() FUNCTIONMost 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 DEBUGGINGMost 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 CommandsThe 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 StatementTo 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 StatementsTo 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 MacroIf 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:
MORE INFORMATIONFor 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
|
Additional query words: 3.00 4.00
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |