XE0927: Using Macros Questions and Answers (FastTips)ID: Q109224
|
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