====================================================================== Microsoft Product Support Services Application Note (Text File) WE0125: CELL REFERENCING FROM A MACRO ====================================================================== Revision Date: 2/92 No Disk Included The following information applies to Microsoft Excel for Windows version 3.0. -------------------------------------------------------------------- | 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 [the MS-DOS(R) | | utility DISKCOPY is appropriate for this purpose]; 3) All | | components of this Application Note must be distributed together; | | and 4) This Application Note may not be distributed for profit. | | | | Copyright 1992 Microsoft Corporation. All Rights Reserved. | | Microsoft, MS-DOS, and the Microsoft logo are registered | | trademarks and Windows is a trademark of Microsoft Corporation. | -------------------------------------------------------------------- Introduction ------------ Microsoft Excel has a powerful macro language that allows you to automate frequently performed tasks, customize functions, or create interactive applications. Cell referencing is an essential element in Microsoft Excel's macro language. 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 on either a worksheet or a macro sheet. In a macro, there are a variety of ways to refer to a cell, depending on whether the cell you are referring to is on a macro sheet or a worksheet and depending on which sheet is active when the macro command is executed. Definitions ----------- Term Definition ---- ---------- Absolute reference A reference that is fixed. It will not adjust itself if copied to another cell, if rows are inserted above it, or if columns are inserted to the left of it. Relative reference A reference that will adjust itself if copied to another cell, if rows are inserted above it, or if columns are inserted to the left of it. Active sheet The sheet from which the macro is run or the sheet that was last activated using the ACTIVATE function. (For more information on the ACTIVATE function, see page 2 of the "Microsoft Excel Function Reference.") Referencing style Microsoft Excel uses two different referencing styles, A1 style referencing and R1C1 style referencing. 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. A1 Style Referencing -------------------- Syntax Description ------ ----------- $A$1 This is an absolute reference to cell A1. A1 This is a relative reference to cell A1. !$A$1 This is an absolute reference to cell A1 on the ACTIVE sheet. !A1 This is a relative reference to cell A1 on the active sheet. SHEET1.XLS!$A$1 This is an absolute reference to cell A1 on SHEET1.XLS. SHEET1.XLS!A1 This is a relative reference to cell A1 on SHEET1.XLS R1C1 Style Referencing ---------------------- Syntax Description ------ ----------- R1C1 This is an absolute reference to cell A1 (row 1, column 1). R[1]C[1] This is a relative reference to the cell that is one row below and one column to the right of the cell in which this reference is typed. Note: This reference is not relative to the currently active cell. !R1C1 This is an absolute reference to cell A1 on the active sheet. !R[1]C[1] This is a relative reference to the cell on the active sheet that is one row below and one column to the right of the cell in which this reference is typed. Note: This reference is not relative to the currently active cell. SHEET1.XLS!R1C1 This is an absolute reference to cell A1 on SHEET1.XLS. SHEET1.XLS!R[1]C[1] This is a relative reference to the cell on SHEET1.XLS that is one row below and one cell to the right of the cell in which this reference is typed. 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 are some tips for selecting cells from within a macro: - =SELECT(A1) will always attempt to select cell A1 on the macro sheet; however, it will always result in a macro error unless the macro sheet is the active sheet. - =SELECT(SHEET1.XLS!$A$1) or =SELECT(SHEET1.XLS!A1) will always attempt to select cell A1 on SHEET1.XLS; however, it will always result in a macro error unless SHEET1.XLS is the active sheet. - =SELECT(R1C1) is not a valid macro statement (unless you are using the R1C1 reference style by choosing Workspace from the Options menu and selecting the R1C1 check box). The correct form is =SELECT("R1C1"). This statement will select cell A1 of the currently active sheet. - Only five functions accept references in the form "R1C1" or "R[1]C[1]". This quoted referencing style causes delayed parsing of the cell reference. The quotation marks hold the cell reference in its unparsed state until the line with the quoted reference is evaluated by executing the macro. Attempting to use one of these referencing forms in a function that is not designed to accept it will result in a macro error or produce incorrect results. For example, =IF("R1C1"=100) will always return FALSE because "R1C1" is being treated as a text string, and the IF statement will not evaluate it. Functions that can use absolute or relative references in the "R1C1" or "R[1]C[1]" style are: Function Description -------- ----------- SELECT Can use both absolute and relative references in this style TEXTREF Can use absolute references only (R1C1) INDIRECT Can use absolute references only (R1C1) ABSREF Can use relative references only (R[1]C[1]) FORMULA Can use both absolute and relative references in this style but ONLY in its first argument - Activating worksheets and selecting cells can cause a macro to run slowly. Quite often it is not actually necessary to select a cell; instead you can use external references or the OFFSET function to return information about a particular cell or range of cells. For example, use: =OFFSET(ACTIVE.CELL(),1,1) instead of: =SELECT("R[1]C[1]") to refer to the cell that is one row below and one column to the right of the active cell. Macro Debugging --------------- - Step and Evaluate. Microsoft Excel has a Step feature that allows you to step through your macro line by line and evaluate your commands one by one. This technique can be invaluable when trying to determine why your macro is halting or not behaving as you anticipated. To use the step feature, run your macro by choosing Run from the Macro menu. After selecting your macro from the list, choose the Step button. You can then either step or evaluate the individual lines of your macro code. To evaluate a line of code in stages, you can choose the Evaluate button repeatedly. - F9. To evaluate a portion of a statement or an entire line of code in your macro, use your mouse to select the area you want to evaluate in the formula bar and press f9. This will immediately evaluate the selected area. Be sure to press the esc key on the keyboard after viewing the value, or the original formula will be replaced with the value that is displayed in the formula bar. - CTRL+ACCENT GRAVE. To view the value of all statements on a macro sheet, press and hold down the CTRL key and then press the ACCENT GRAVE key (`). CTRL+ACCENT GRAVE is a toggle switch that puts the macro sheet into View Values mode instead of View Formulas mode, which is the default. Press CTRL+ACCENT GRAVE again to return the macro sheet to View Formulas mode. An alternative way to toggle between View Values and View Formulas is to choose Display from the Options menu and select the Formulas check box or clear it (when a macro sheet is active, the default is View Formulas). More Information ---------------- For additional information on cell referencing, macro functions, and macro writing, see the "Complete Guide to Microsoft Excel Macros" by Charles Kyd and Chris Kinata, published by Microsoft Press.