Excel Macros 简明教程

Excel Macros - VBA

Excel 将宏存储为 Excel VBA(Visual Basic for Applications)代码。录制宏后,您可以查看、修改、复制部分内容等生成的代码。如果您精通 VBA 编程,您甚至可以自己编写宏代码。

Excel stores the macros as Excel VBA (Visual Basic for Applications) code. After recording a macro, you can view the code that is generated, modify it, copy a part of it, etc. You can even write a macro code yourself if you are comfortable with programming in VBA.

您将在“使用 VBA 编辑器创建宏”章节中了解如何通过编写 VBA 代码创建宏。您将在“编辑宏”章节中了解如何通过编辑 VBA 代码修改宏。您将在本章节中了解 Excel VBA 功能。

You will learn how to create a macro, by writing a VBA code, in the chapter - Creating a Macro Using VBA Editor. You will learn how to modify a macro by editing VBA code in the chapter - Editing a Macro. You will learn the Excel VBA features in this chapter.

Developer Tab on the Ribbon

您可以从功能区上的“开发人员”选项卡中从 VBA 访问宏代码。

You can access macro code in VBA from the Developer tab on the Ribbon.

developer

如果您在功能区上找不到“开发工具”选项卡,则需要按如下方式添加它 −

If you do not find the Developer tab on the Ribbon, you need to add it as follows −

  1. Right click on the Ribbon.

  2. Select Customize the Ribbon from the dropdown list.

customize ribbon

“自定义快速访问工具栏”对话框出现。

The Excel Options dialog box appears.

  1. Select Main Tabs from Customize the Ribbon dropdown list.

  2. Check the box – Developer in the Main Tabs list and click OK. The developer tab appears.

excel options

Developer Commands for Macros

您需要了解开发工具选项卡下针对宏的命令。

You need to know the commands that are for macros under the developer tab.

单击功能区上的“开发工具”选项卡。以下命令在“代码”组中可用 −

Click the DEVELOPER tab on the Ribbon. The following commands are available in the Code group −

  1. Visual Basic

  2. Macros

  3. Record Macro

  4. Use Relative References

  5. Macro Security

controls

Visual Basic 命令用于在 Excel 中打开 VBA 编辑器,宏命令用于查看、运行和删除宏。

The Visual Basic command is used to open the VBA Editor in Excel and the Macros command is used to view, run and delete the macros.

您已经在之前章节中学习过 Visual Basic 编辑器以外的命令。

You have already learnt the commands other than VBA Editor in the previous chapters.

VBA Editor

VBA 编辑器或 VBE 是 Excel 中 VBA 的开发平台。

VBA Editor or VBE is the developer platform for VBA in Excel.

打开工作簿 – MyFirstMacro.xlsm,您在本章中已将其保存 – 在本教程中创建简单宏。

Open the workbook – MyFirstMacro.xlsm that you saved earlier in the chapter – Creating a Simple Macro, in this tutorial.

您可以通过以下两种方式之一打开 VBE −

You can open the VBE in any of the two ways −

① − 单击功能区上“开发工具”选项卡下“代码”组中的“Visual Basic”。

Option 1 − Click Visual Basic in the Code group under the Developer tab on the Ribbon.

vba editor

② − 单击单击“视图”选项卡 →“宏”→“查看宏”时出现的“宏”对话框中的“编辑”。

Option 2 − Click Edit in the Macro dialog box that appears when you click VIEW tab → Macros → View Macros

view macro

VBE 在新窗口中出现。

VBE appears in a new window.

vbe

启用 Excel 宏的工作簿名称以“ - Microsoft Visual Basic for Applications”前缀出现。

The name of your Excel macro enabled workbook name appears with the prefix – Microsoft Visual Basic for Applications.

您将在 VBE 中找到以下内容 −

You will find the following in the VBE −

  1. Projects Explorer.

  2. Properties.

  3. Module window with Code.

Projects Explorer

项目资源管理器是您找到 VBA 项目名称的位置。在项目下,您将找到工作表名称和模块名称。当您单击模块名称时,相应的代码将出现在右侧的窗口中。

Project Explorer is where you find the VBA project names. Under a project, you will find Sheet names and Module names. When you click a module name, the corresponding code appears on the right side in a window.

Properties Window

属性是 VBA 对象的参数。当您具有一个对象,如命令按钮时,其属性将出现在“属性”窗口中。

The Properties are the parameters for VBA objects. When you have an object such as command button, its properties will appear in the Properties window.

Module Window with Code

宏的代码将存储在 VBA 中的一个模块中。当您选中一个宏并单击“编辑”后,宏的代码将出现在相应的模块窗口中。

The code of a macro will be stored in a module in VBA. When you select a macro and click Edit, the code of the macro appears in the corresponding module window.