Excel: Using External References to Make Macros Run Faster

Last reviewed: April 3, 1997
Article ID: Q51062

1.50 2.20 3.00 MACINTOSH kbother

SUMMARY

To reference cells on other sheets in Microsoft Excel version 1.50 or later, use external references. Using external references is much more efficient than activating the desired sheet, selecting the appropriate cell, and then returning to the original sheet.

For example, the following macro selects cell R2C2 (B2) on Sheet2 by activating Sheet2 and then returning to Sheet1:

   =ACTIVATE("Sheet2")
   =SELECT("R2C2")
   =FORMULA("=1+1")
   =ACTIVATE("Sheet1")
   =RETURN()

You can accomplish the same result much faster by using the following macro commands:

   =FORMULA("=1+1",Sheet2!B2)
   =RETURN()

MORE INFORMATION

This information was taken from page 151 in the "Macro Tips" section of the "Microsoft Excel Technical Reference for the Macintosh."

The "Microsoft Excel Technical Reference for the Macintosh" can be obtained as part of the Excel Developer's Kit by calling Microsoft End User Sales and Service at (800) 426-9400.

KBCategory: kbother
KBSubcategory:
Additional reference words: 1.50 2.20 3.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.

Last reviewed: April 3, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.