Excel Macros 简明教程

Creating a Macro Using VBA Editor

可以通过在 VBA 编辑器中编写代码来创建宏。在本章中，你将了解在哪里以及如何编写用于宏的代码。

You can create a macro by writing the code in the VBA editor. In this chapter, you will learn where and how to write the code for a macro.

VBA Objects and Modules

在你开始为宏编码之前，请理解 VBA 对象和模块。

Before you start coding for a Macro, understand the VBA Objects and Modules.

  1. Open the macro-enabled workbook with your first macro.

  2. Click the DEVELOPER tab on the Ribbon.

  3. Click Visual Basic in the Code group.

objects

VBA 编辑器窗口就会打开。

The VBA editor window opens.

window opens

你将看到“工程资源管理器”窗口中包含如下内容：

You will observe the following in the Projects Explorer window −

  1. Your macro enabled workbook – MyFirstMacro.xlsm appears as a VBA Project.

  2. All the worksheets and the workbook appear as Microsoft Excel Objects under the project.

  3. Module1 appears under Modules. Your macro code is located here.

  4. Click Module1.

  5. Click the View tab on the Ribbon.

  6. Select Code from the dropdown list.

code

就会出现你的宏的代码。

The code of your macro appears.

code of macro

Creating a Macro by Coding

接着，在同一工作簿中创建第二个宏 – 这一次通过编写 VBA 代码。

Next, create a second macro in the same workbook – this time by writing VBA code.

你可以通过两个步骤完成此操作：

You can do this in two steps −

  1. Insert a command button.

  2. Write the code stating the actions to take place when you click the command button.

Inserting a Command Button

  1. Create a new worksheet.

  2. Click in the new worksheet.

  3. Click the DEVELOPER button on the Ribbon.

  4. Click Insert in the Controls group.

  5. Select the button icon from Form Controls.

inserting command

  1. Click in the worksheet where you want to place the command button.

  2. The Assign Macro dialog box appears.

button1 click

Visual Basic 编辑器将出现。

The Visual Basic editor appears.

visual basic

您将观察到以下内容 −

You will observe the following −

  1. A new module – Module2 is inserted in the Project Explorer.

  2. Code window with title Module2 (Code) appears.

  3. A sub procedure Button1_Click () is inserted in the Module2 code.

Coding the Macro

一半的代码已由 VBA 编辑器本身完成。

Your coding is half done by the VBA editor itself.

例如，在子过程 Button1_Click() 中输入 MsgBox “Best Wishes to You!” 。当单击命令按钮时，将显示一个具有给定字符串的消息框。

For example, type MsgBox “Best Wishes to You!” in the sub procedure Button1_Click (). A message box with the given string will be displayed when the command button is clicked.

message box

就是这么简单！您的宏代码已准备就绪。正如您所知，VBA 代码不需要编译，因为它使用解释器运行。

That’s it! Your macro code is ready to run. As you are aware, VBA code does not require compilation as it runs with an interpreter.

Running the Macro from VBA Editor

您可以在 VBA 编辑器本身中测试您的宏代码。

You can test your macro code from the VBA editor itself.

  1. Click the Run tab on the Ribbon.

  2. Select Run Sub/UserForm from the dropdown list. The message box with the string you typed appears in your worksheet.

macro from vba

您可以看到该按钮已被选中。在消息框中单击“确定”。您将返回 VBA 编辑器。

You can see that the button is selected. Click OK in the message box. You will be taken back to the VBA editor.

Running the Macro from Worksheet

您可以从工作表多次运行您编写的宏。

You can run the macro that you coded any number of times from the worksheet.

  1. Click somewhere on the worksheet.

  2. Click the Button. The Message box appears on the worksheet.

macro from worksheet

您已通过编写 VBA 代码创建了一个宏。正如您所观察到的，VBA 编码很简单。

You have created a macro by writing VBA code. As you can observe, VBA coding is simple.