Excel: Cell Reference Formats for an Excel Macro

SUMMARY

Before examining the different cell reference forms, it should be noted that Microsoft Excel can operate with either A1 or R1C1 type of cell references. The first part of this discussion focuses on the A1 type reference, followed by descriptions of the R1C1 type reference.

MORE INFORMATION

$A$1

This is an absolute reference to a cell on the sheet that contains this statement. For example, the following reference selects cell B3 on the macro sheet that contains the SELECT statement:

   =SELECT($B$3)

A1

This is a relative reference to a cell on the sheet that contains this statement. For example, the following reference selects the cell B3 on the macro sheet that contains the statement, but maintains B3 as a relative reference on the sheet where it appears [i.e., if the =SELECT(B3) statement is in cell A1 and is copied to cell A2, Excel changes the relative reference so that cell A2 contains =SELECT(B4)]:

   =SELECT(B3)

!$A$1

This is an absolute reference to a cell on the active sheet. For example, the following reference selects cell B2 on the worksheet named "example":

   =ACTIVATE("example")
   =SELECT(!$B$2)

!A1

This is a relative reference to a cell on the active sheet. For example, the following reference selects cell B2 on worksheet "example" and maintains B2 as a relative reference on the sheet where it appears (see also "A1", above):

   =ACTIVATE("example")
   =SELECT(!B2)

'Worksheet Name'!A1

This is a relative reference to a cell on the specified worksheet.

****************************************************************** IMPORTANT: The following information assumes that the sheet is set to R1C1 style references. ******************************************************************

R1C1

This is an absolute reference to a cell on the sheet that contains the statement. For example, the following reference selects cell R3C2 (same cell as B3 when using the A1 type of reference) on the macro sheet that contains the SELECT statement:

   =SELECT(R3C2)

This is a relative reference to a cell on the sheet that contains this statement. For example, the following reference selects the cell (on the macro sheet that contains the statement) that is three rows down and two columns to the right of the cell that contains the SELECT statement:

   =SELECT(R[3]C[2])

!R1C1

This is an absolute reference to a cell on the active sheet. For example, the following reference selects cell R2C2 on the worksheet named "example":

   =ACTIVATE("example")
   =SELECT(!R2C2)

This is a relative reference to a cell on the active sheet, but its relativity is to the cell that contains the macro statement. For example, the following reference selects the cell (on the active worksheet) that is three rows down and two columns to the right of the cell on the macro sheet that contains the SELECT statement:

   =ACTIVATE("Worksheet1")
   =SELECT(!R[3]C[2])

   =ACTIVATE("SalesQ1")
   =SELECT(!R[3]C[2])
   =RETURN()

'Worksheet Name'!R1C1

This is an absolute reference to a specific cell on a named sheet.

'Example Sheet'!R[#]C[#] (where # is a positive or negative integer)

This is a reference to a cell on the sheet named "Example Sheet" whose position is determined relative to the cell on the macro sheet in which the statement appears.

For additional information on references not parsed until execution, query on the following words:

   parsed and execution