XE0927: Using Macros Questions and Answers (FastTips)

Last reviewed: December 26, 1996
Article ID: Q109224

The information in this article applies to:

  • Microsoft Excel for Windows, version 5.0

SUMMARY

Following is the complete text for the FastTip for Microsoft Excel version 5.0 for Windows. It contains the commonly asked questions and answers about using macros.

  Microsoft(R) Product Support Services Application Note (Text File)
              XE0927: USING MACROS QUESTIONS AND ANSWERS
                                                   Revision Date: 1/96
                                                      No Disk Included

The following information applies to Microsoft(R) Excel, version 5.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 (C) 1996 Microsoft Corporation.  All Rights Reserved.
 Microsoft, MS-DOS, Visual Basic, and Windows are registered
 trademarks of Microsoft Corporation.
1. Q. When I open my workbook, I want it to run a macro. How do I do
      this?

   A. To run a specified macro each time you open a workbook, you can
      use an auto-open macro in one of two ways:

       - You can define the name Auto_Open on a worksheet or macro
         sheet so that the definition refers either to your macro name
         (in Microsoft Excel 4.0 and Visual Basic(R) macros) or to the
         first cell in your macro (in Microsoft Excel 4.0 macros
         only).

         -or-

       - You can create an Auto_Open procedure in a Visual Basic
         module.

      To define the name Auto_Open:

      1. Open the workbook containing the macro you want to run.

      2. Open the workbook that you want to run the macro (if it is
         not the same as the one you opened in step one). Select
         either a worksheet or a macro sheet tab.

      3. From the Insert menu, choose Name, and then choose Define.

      4. In the Name box, type Auto_Open.

      5. In the Refers To box, type the reference to the macro that
         you want to run, and choose OK. Use the following table to
         determine how to reference your macro.

         If you are using                 Type the reference
         this type of macro               in this format
         -------------------------------------------------------

         Microsoft Excel 4.0 macro        =MyMacro
         sheet located in same            -or-
         workbook                         =Macro1!A1

         Microsoft Excel 4.0 macro        =BOOK2.XLS!Mymacro
         sheet located in separate        -or-
         workbook                         =[BOOK2.XLS]Macro1!A1

         Visual Basic module              =MyMacro
         located in same workbook

         Visual Basic module              =BOOK2.XLS!MyMacro
         located in separate
         workbook

      To use the Auto_Open procedure (Visual Basic modules only):

      1. Open the workbook from which you want to run the macro and
         either select the tab for the module that contains your macro
         or insert a new module.

      2. Add the appropriate procedure from the following table
         (depending on where the macro that you want to run is
         located).

         If you macro is located   Use this procedure
         -------------------------------------------------
         In the same workbook      Sub Auto_Open
                                      Run("MyMacro")
                                   End Sub

         In a separate workbook    Sub Auto_Open
                                      Run("BOOK2.XLS!Module1.MyMacro")
                                   End Sub

         NOTE: In order to use the Run statement, the workbook
         containing the macro that you want to use must be open. Your
         macro can be a Microsoft Excel 4.0 macro or a Visual Basic
         macro.

      Each time you open the workbook that contains the Auto_Open
      procedure, Microsoft Excel automatically runs the specified
      macro.

      You can use similar methods to define an Auto_Close name and use
      an Auto_Close procedure that will run a specified macro each
      time a workbook is closed. Other similar defined names and
      procedures are Auto_Activate and Auto_Deactivate (which are used
      to run macros when a specified workbook is activated or
      deactivated).

      For additional information, see Chapter 13, "Creating Automatic
      Procedures," in the "Visual Basic User's Guide."

2. Q. How can I convert my Microsoft Excel version 4.0 macros to
      Visual Basic macros? And, how can I learn more about Visual
      Basic for Applications?

   A. While you are learning to use the Microsoft Excel 5.0 macro
      language, you can continue to write and run version 4.0 macros.
      Although there is no way to automatically translate 4.0 macros,
      the Microsoft Excel 4.0 macro language has been updated to
      incorporate the new features in Microsoft Excel 5.0.

      To begin converting your macros and learning Visual Basic:

       - Review Appendix B, "Switching from the Microsoft Excel 4.0
         Macro Language," in the" Visual Basic User's Guide". This
         appendix describes some key differences between the version
         4.0 and macro languages. In addition, it discusses ways to
         approach the conversion process.

       - Refer to the Visual Basic Samples file located in the
         Examples subdirectory in the directory in which you installed
         Microsoft Excel. This workbook file contains examples of
         tasks commonly  accomplished with macros, such as moving and
         selecting, performing repetitive processes using looping
         structures, and communicating with other applications using
         dynamic data exchange (DDE). The version 4.0 macro and its
         Visual Basic equivalent are provided on sheets in this
         workbook.

       - Review the Examples And Demos lessons in Microsoft Excel
         Help.

       - Use the Record Macro command to record parts of your macro in
         a Visual Basic module.  Recording a macro will help you learn
         the new macro commands. In addition, you can run the
         unconverted parts of your Microsoft Excel 4.0 macros from
         Visual Basic macros by using the Run method.

         Tip: While you record, you may find it helpful to see your
         Visual Basic module and the sheet on which you're selecting,
         moving, and carrying out commands simultaneously. To do this,
         select the appropriate Visual Basic module tab and choose the
         New Window command from the Window menu. Select your sheet
         tab and choose the Arrange command from the Window menu.
         Select the Tiled option and select the Windows Of Active
         Document option, and then choose OK.

       - Print a copy of the Visual Basic Equivalents To Macro
         Functions help topic, located under Excel Macro Functions in
         Microsoft Excel Help. Keep this list of equivalents nearby
         for handy reference.

3. Q. How are Visual Basic 3.0 and the Visual Basic for Applications
      different?

   A. The Applications Edition of the Visual Basic Programming System
      helps you to customize Microsoft Excel, whereas Visual Basic
      3.0 allows you to create independent applications. Because the
      Applications Edition of Visual Basic must operate within
      Microsoft Excel, there are some differences in what you can and
      cannot control. For example, the Applications Edition has a
      limited set of object properties for dialog box controls: you
      cannot format or align text, and the events that you can use
      for dialog box controls are limited to OnClick, OnChange, and
      OnShow; Visual Basic 3.0 events such as Focus, Keydown, and
      Mousedown are not available.

      For additional information about the features available in the
      Microsoft Visual Basic Programming System, Applications
      Edition, please refer to the "Visual Basic User's Guide".

4. Q. With the new menu structure in version 5.0 of Microsoft Excel,
      how do I run my version 4.0 macros that add custom commands to
      menus?

   A. You should eventually revise your macros so that they use the
      Microsoft Excel 5.0 menu structure. If you do customize 5.0
      menus with Microsoft Excel 4.0 macros, note that if your macro
      adds menu commands to the Worksheet or Chart menu bars, they
      will be added to the menu that is in the same relative position
      as the Microsoft Excel 4.0 menu. For example, if in 4.0 your
      macro adds a command to the Formula menu (the third menu from
      the left), the command is added to the View menu in 5.0 (the
      third menu from the left).

       - If you prefer to work with 4.0 menus, switch to the 4.0 menus
         by selecting the Microsoft Excel 4.0 Menus option on the
         General tab.

         -or-

       - Turn on version 4.0 menus from your macro by adding the
         OPTIONS.GENERAL(,,,,,,TRUE) statement before the statements
         that add menus or commands.

      Although custom commands are added both to the 4.0 and 5.0
      menus, they are not deleted from both menus. Therefore, if you
      have a Microsoft Excel 4.0 macro that deletes custom menus and
      commands, you must add macro code that will delete the menus and
      commands from the version 5.0 menus.


KBCategory: kbusage
KBSubcategory:

Additional reference words: 5.00 XL50TLC



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: December 26, 1996
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.