Microsoft KB Archive/109224

-

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.

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. Additional query words: XL50TLC

Keywords         : Version          : 5.00 Platform         : WINDOWS Issue type       : Last Reviewed: April 22, 1999