Excel Macros 简明教程
Excel Macros - Configuring a Macro
您可以记录一个宏并使用 Auto_Open 名称将其保存,以便在您打开包含此宏的工作簿时运行它。
You can record a macro and save it with the name Auto_Open to run it whenever you open the workbook that contains this macro.
您还可以编写 VBA 代码,在工作簿的 Open 事件中实现相同目的。Open 事件将在您每次打开工作簿时运行子过程 Workbook_Open() 中的代码。
You can also write VBA code for the same purpose with the Open event of the workbook. The Open event runs the code in the sub procedure Workbook_Open () every time you open the workbook.
Recording an Auto_Open Macro
您可以按以下步骤记录一个 Auto_Run 宏:
You can record an Auto_Run macro as follows −
-
Click the VIEW tab on the Ribbon.
-
Click Macros.
-
Click Record Macro. The Record Macro dialog box appears.
-
Type Auto_Run for the macro name.
-
Type a description and click OK.

-
Start recording the macro.
-
Stop Recording.
-
Save the workbook as macro enabled workbook.
-
Close the workbook.
-
Open the workbook. The macro Auto_Run will run automatically.
如果您希望 Excel 启动时不运行 Auto_Open 宏,请在启动 Excel 时按住 SHIFT 键。
If you want Excel to start without running an Auto_Open macro, hold the SHIFT key when you start Excel.
Limitations of Auto_Open Macro
以下是一些 Auto_Open 宏的限制:
The following are the limitations of Auto_Open macro −
-
If the workbook in which you saved the Auto_Open macro contains code for workbook Open event, the code for the Open event will override the actions in the Auto_Open macro.
-
An Auto_Open macro is ignored when the workbook is opened by running code that uses the Open method.
-
An Auto_Open macro runs before any other workbooks open. Hence, if you record actions that you want Excel to perform on the default Book1 workbook or on a workbook that is loaded from the XLStart folder, the Auto_Open macro will fail when you restart Excel, because the macro runs before the default and startup workbooks open.
如果您遇到其中任何限制,则必须写入一个 Open 事件代码(如下节所述),而不是记录一个 Auto_Open 宏。
If you encounter any of these limitations, instead of recording an Auto_Open macro, you must write a code for the Open event as described in the next section.
VBA Code for Open Event of a Workbook
您可以编写在打开工作簿时执行的代码。VBA 为您提供了一个名为 Open 的事件,该事件包含一个 VBA 过程,用于执行在打开工作簿时要执行的操作。
You can write code that will get executed when you open a workbook. VBA provides you with an event called open that incorporates a VBA procedure for the actions to be done on opening a workbook.
在其中存储专用于绝对引用(Report_ProjectXYZ)的宏的工作簿中打开工作簿。运行该宏时,将在工作簿中添加一个新的工作表,并且项目报告结构显示在新的工作表上。
Open the workbook in which you stored the macro that you have written for the absolute references – Report_ProjectXYZ. When this macro is run, a new worksheet will be added in the workbook and the project report structure appears on the new worksheet.
您可在打开工作簿时编写宏代码来执行这些操作。这意味着当您打开“项目报告”工作簿时,带有报告结构的新工作表将准备就绪,供您输入详细信息。
You can write a macro code that will perform these actions when you open the workbook. That means when you open the Project Report workbook, a new worksheet with the report structure will be ready for you to enter the details.
按照 VBA 编辑器中给定的以下过程进行操作:
Follow the below given procedure in VBA editor−
-
Double click on ThisWorkbook in Projects Explorer.
-
In the code window, select Workbook in the left dropdown list and Open in the right dropdown list. Sub Workbook_Open () appears.

-
Click Modules in the Projects Explorer.
-
Double click on the module name that contains the macro code.
-
Copy the macro code from the module and paste it in the Sub WorkBook_Open ().

保存已启用宏的工作簿。再次将其打开。将运行宏,并将插入带有报表结构的新工作表。
Save the macro-enabled workbook. Open it again. The macro runs and a new worksheet with the report structure is inserted.