Excel Macros 简明教程

Excel Macros - Overview

Excel 宏是一种操作或操作集，您可以记录、命名、保存并根据需要多次运行。当您创建宏时，您正在记录鼠标点击和按键操作。当您运行保存的宏时，将按照记录的相同顺序执行记录的鼠标点击和按键操作。

An Excel macro is an action or a set of actions that you can record, give a name, save and run as many times as you want and whenever you want. When you create a macro, you are recording your mouse clicks and keystrokes. When you run a saved macro, the recorded mouse clicks and keystrokes will be executed in the same sequence as they are recorded.

宏可帮助您节省经常需要完成的数据操作和数据报告中的重复任务的时间。

Macros help you to save time on repetitive tasks involved in data manipulation and data reports that are required to be done frequently.

Macro and VBA

您可以使用 Excel 命令或通过 Excel VBA 记录和运行宏。

You can record and run macros with either Excel commands or from Excel VBA.

VBA 全称为 Visual Basic for Applications，它是一种简单的编程语言，可通过 Excel Visual Basic 编辑器 (VBE) 使用，后者可通过功能区上的开发工具选项卡获得。当您记录宏时，Excel 将生成 VBA 代码。如果您只想记录宏并运行宏，则无需学习 Excel VBA。但如果您想修改宏，则只能通过修改 Excel VBA 编辑器中的 VBA 代码来执行该操作。

VBA stands for Visual Basic for Applications and is a simple programming language that is available through Excel Visual Basic Editor (VBE), which is available from the DEVELOPER tab on the Ribbon. When you record a macro, Excel generates VBA code. If you just want to record a macro and run it, there is no need to learn Excel VBA. However, if you want to modify a macro, then you can do it only by modifying the VBA code in the Excel VBA editor.

您将学习如何在“创建简单宏”一章中使用 Excel 命令记录简单宏并运行该宏。您将在后面的章节中进一步了解宏以及如何通过 Excel VBA 编辑器创建和/或修改宏。

You will learn how to record a simple macro and run it with Excel commands in the chapter - Creating a Simple Macro. You will learn more about macros and about creating and / or modifying macros from Excel VBA editor in the later chapters.

Personal Macro Workbook

宏可以保存到记录它的工作簿中。在这种情况下，您只能通过该工作簿运行宏，因此您应该保持该工作簿处于打开状态。Excel 为您提供另一种方式来存储您的所有宏。它是一个个人宏工作簿，您可以在其中保存宏，这使您能够从任何工作簿中运行这些宏。

A macro can be saved in the same workbook from where you recorded it. In that case, you can run the macro from that workbook only and hence you should keep it open. Excel gives you an alternative way to store all your macros. It is the personal macro workbook, where you can save your macros, which enables you to run those macros from any workbook.

在“将所有宏保存在单个工作簿”这一章节中，您将了解个人宏工作簿。

You will learn about Personal Macro Workbook in the chapter - Saving all your Macros in a Single Workbook.

Macro Security

宏将作为 VBA 代码存储在 Excel 中。与任何其他代码一样，宏代码也容易受到可在打开工作簿时运行的恶意代码的影响。这对您的计算机来说是一个威胁。Microsoft 提供了宏安全功能来帮助您保护您的计算机免受此类宏病毒的侵害。

Macros will be stored as VBA code in Excel. As with the case of any other code, macro code is also susceptible to malicious code that can run when you open a workbook. This is a threat to your computer. Microsoft provided with the Macro Security facility that helps you in protecting your computer from such macro viruses.

您将在“宏安全”这一章节中了解到更多关于此内容的信息。

You will learn more about this in the chapter - Macro Security.

Absolute References and Relative References

在录制宏时，您可以对所单击的单元格使用绝对引用或相对引用。绝对引用让宏在录制宏的单元格中运行。另一方面，相对引用使您的宏在活动单元格中运行。

While recording a macro, you can use either absolute references or relative references for the cells on which you are clicking. Absolute references make your macro run at the same cells where you recorded the macro. On the other hand, relative references make your macro run at the active cell.

您将在“对宏使用绝对引用”和“对宏使用相对引用”这一章节中了解到这些内容。

You will learn about these in the chapters - Using Absolute References for a Macro and Using Relative References for a Macro.

Macro Code in VBA

即使您不了解 Excel VBA，也可以在 Excel 中录制和运行宏。但是，如果您必须修改已录制的宏或通过编写 VBA 代码创建宏，则应学习 Excel VBA。为此，您可以参考本教程库中的 Excel VBA 教程。

You can record and run macros from Excel even if you do not know Excel VBA. However, if you have to modify a recorded macro or create a macro by writing VBA code, you should learn Excel VBA. You can refer to the Excel VBA tutorial in this tutorials library for this

但是，您应该知道如何查看宏代码。您可以在“Excel VBA”这一章节中了解如何在 Excel 中访问 VBA 编辑器以及 VBA 编辑器的不同部分。

However, you should know how to view the macro code. You can learn how to access VBA editor in Excel and about the different parts of the VBA editor in the chapter – Excel VBA.

您可以在 Excel VBA 编辑器中了解如何查看宏代码，并且可以在“了解宏代码”这一章节中了解宏代码。

You can learn how to view the macro code in Excel VBA editor and you can understand the macro code in the chapter - Understanding Macro Code.

Assigning Macros to Objects

您可以将宏分配给某个对象，例如形状、图形或控件。然后，您可以通过单击该对象来运行宏。您将在“将宏分配给对象”这一章节中了解到此内容。

You can assign a macro to an object such as a shape or a graphic or a control. Then, you can run the macro by clicking on that object. You will learn about this in the chapter - Assigning Macros to Objects.

Running Macros

Excel 提供运行宏的多种方法。您可以选择您想运行宏的方法。您将在“运行宏”这一章节中了解这些运行宏的不同可能方法。

Excel provides several ways to run a macro. You can choose the way you want to run a macro. You will learn about these different possible ways of running a macro in the chapter - Running a Macro.

Creating a Macro Using VBA Editor

如果您决定编写宏代码，则可以在“使用 VBA 编辑器创建宏”这一章节中学习。但是，前提是您应该具备 Excel VBA 知识。

If you decide to write the macro code, you can learn it in the chapter - Creating a Macro Using VBA Editor. However, the prerequisite is that you should have Excel VBA knowledge.

Editing a Macro

您可以在 Excel VBA 编辑器中修改宏代码。如果您想进行广泛的更改，则您应该具备 Excel VBA 知识。但是，如果您只想对代码进行一些小的更改，或者如果您想将 VBA 代码从一个已录制的宏复制到另一个宏，则可以参考“编辑宏”这一章节。

You can modify macro code in Excel VBA editor. If you want to make extensive changes, you should have Excel VBA knowledge. But, if you want to make only minor changes to the code or if you want to copy the VBA code from a recorded macro to another macro, you can refer to the chapter - Editing a Macro.

您可以重命名宏，甚至可以将其删除。您还将在同一章节中了解到此内容。

You can rename a macro and even delete it. You will learn about this also in the same chapter.

User Forms

窗体通常用于收集所需的信息。它不言自明，使任务变得简单。由 Excel VBA 编辑器创建的 Excel 用户窗体具有相同的功能，并提供熟悉的选项，如文本框、复选框、单选按钮、列表框、组合框、滚动条等作为控件。

A Form is normally used to collect required information. It will be self-explanatory making the task simple. Excel User Forms created from Excel VBA editor serve the same purpose, providing the familiar options such as text boxes, check boxes, radio buttons, list boxes, combo boxes, scroll bars, etc. as controls.

您将在“用户窗体”这一章节中学习如何创建用户窗体以及如何使用不同的控件。

You will learn how to create a User Form and how to use the different controls in the chapter – User Forms.

Debugging Macro Code

有时，宏可能无法按预期运行。您可能已经创建了宏，或者您可能正在使用某人提供给您的宏。您可以像调试任何其他代码一样调试宏代码，以发现缺陷并对其进行更正。您将在“调试宏代码”这一章节中了解到此内容。

At times, a macro may not run as expected. You might have created the macro or you might be using a macro supplied to you by someone. You can debug the macro code just as you debug any other code to uncover the defects and correct them. You will learn about this in the chapter - Debugging Macro Code.

Configuring a Macro to Run on Opening a Workbook

当您打开工作簿时，您可以让您的宏自动运行。您可以通过创建 Auto_Run 宏或为工作簿打开事件编写 VBA 代码来执行此操作。您将在“配置宏在打开工作簿时运行”这一章节中学习此内容。

You can make your macro run automatically when you open a workbook. You can do this either by creating an Auto_Run macro or by writing VBA code for workbook open event. You will learn this in the chapter - Configuring a Macro to Run on Opening a Workbook.