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 −

  1. Click the VIEW tab on the Ribbon.

  2. Click Macros.

  3. Click Record Macro. The Record Macro dialog box appears.

  4. Type Auto_Run for the macro name.

  5. Type a description and click OK.

auto open

  1. Start recording the macro.

  2. Stop Recording.

  3. Save the workbook as macro enabled workbook.

  4. Close the workbook.

  5. 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 −

  1. 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.

  2. An Auto_Open macro is ignored when the workbook is opened by running code that uses the Open method.

  3. 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−

  1. Double click on ThisWorkbook in Projects Explorer.

  2. In the code window, select Workbook in the left dropdown list and Open in the right dropdown list. Sub Workbook_Open () appears.

workbook open

  1. Click Modules in the Projects Explorer.

  2. Double click on the module name that contains the macro code.

  3. Copy the macro code from the module and paste it in the Sub WorkBook_Open ().

sub workbook open

保存已启用宏的工作簿。再次将其打开。将运行宏，并将插入带有报表结构的新工作表。

Save the macro-enabled workbook. Open it again. The macro runs and a new worksheet with the report structure is inserted.