Excel Macros 简明教程

Excel Macros - Quick Guide

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.

Excel Macros - Creation

您可以通过录制击键和鼠标点击、给宏命名并指定宏的存储方式来创建带有 Excel 命令的宏。因此，记录这样一个宏可以用 Excel 命令运行。

You can create a macro with Excel commands by recording the key strokes and mouse clicks, giving the macro a name and specifying how to store the macro. A macro thus recorded can be run with an Excel command.

假设您必须以以下格式反复收集某些结果 -

Suppose you have to collect certain results repeatedly in the following format −

您可以使用宏来处理它，而不必每次都创建表格。

Instead of creating the table each time, you can have a macro to do it for you.

Recording a Macro

要录制宏，请执行以下操作 -

To record a macro do the following −

Click the VIEW tab on the Ribbon.
Click Macros in the Macros group.
Select Record Macro from the dropdown list.

[ Record Macro 对话框出现。

The Record Macro dialog box appears.

Type MyFirstMacro in the Macro name box.
Type A Simple Macro in the Description box and click OK.

记住您执行的所有击键和鼠标点击现在都将被记录下来。

Remember that whatever key strokes and mouse clicks you do, will be recorded now.

Click in the cell B2.
Create the table.
Click in a different cell in the worksheet.
Click the VIEW tab on the Ribbon.
Click Macros.
Select Stop Recording from the dropdown list.

宏录制完成。

Your macro recording is completed.

单击特定单元格的第一步非常重要，因为它会告诉宏从哪里开始放置记录的步骤。完成录制后，您必须单击“停止录制”以避免记录不必要的步骤。

The first step to click on a particular cell is important as it tells where exactly the macro has to start placing the recorded steps. Once you are done with the recording, you have to click Stop Recording to avoid recording of unnecessary steps.

Running a Macro

您可以随时运行已录制的宏。要运行宏，请执行以下操作 -

You can run the macro you have recorded any number of times you want. To run the macro, do the following −

Click on a new worksheet.

注意活动单元格。在我们的示例中，它是 A1。

Note the active cell. In our case, it is A1.

Click the VIEW tab on the Ribbon.
Click Macros.
Select View Macros from the dropdown list.

出现宏对话框。

The Macro dialog box appears.

仅会将你录制的宏显示在“宏”列表中。

Only the macro that you recorded appears in the Macros list.

Click the macro name – MyFirstMacro in the Macro dialog box. The description you typed while recording the macro will get displayed. Macro description allows you to identify for what purpose you have recorded the macro.
Click the Run button. The same table that you have created while recording the macro will appear in just a split of a second.

你已了解 Excel 为你提供的法宝，可以节省大量时间来处理重复的任务。你将看到以下内容 −

You have discovered the magic wand that Excel provides you to save time on mundane tasks. You will observe the following −

Though the active cell before running the macro was A1, the table is placed in the cell B2 as you have recorded.
In addition, the active cell became E2, as you have clicked that cell before you stopped recording.

你在运行宏之前可以在不同的活动单元格的多个工作表中运行宏，并观察与上面给出的相同的条件。只要记下这一点，你稍后将在本教程中了解到它是为什么这样发生的。

You can run the macro in multiple worksheets with different active cells before running the macro and observe the same conditions as given above. Just keep a note of this and you will understand later in this tutorial why it has occurred so.

你还可以进行宏录制，将录制的步骤放入活动单元格。随着你在本教程中的进度，将学习如何执行此操作。

You can also have a macro recording that places your recorded steps in the active cell. You will learn how to do this as you progress in the tutorial.

Storing a Macro

你可能想知道如何保存创建的宏。在此上下文中，你需要了解以下内容 −

You might wonder how to save the macros that are created. In this context you need to know −

Storing a macro
Saving a macro enabled file

当你创建宏时，可以选择存储该特定宏的位置。你可以在 Record Macro 对话框中执行此操作。

As and when you create a macro, you can choose where to store that particular macro. You can do this in the Record Macro dialog box.

单击框 - Store macro in 。提供以下三个选项 −

Click the box - Store macro in. The following three options are available −

This Workbook.
New Workbook.
Personal Macro Workbook

This Workbook

这是默认选项。宏将存储在你创建宏的当前工作簿中。

This is the default option. The macro will be stored in your current workbook from where you created the macro.

New Workbook

尽管此选项可用，但不建议使用。你将要求 Excel 将宏存储在另一个新工作簿中，并且通常没有必要。

This option, though available, is not recommended. You will be asking Excel to store the macro in a different new workbook and mostly it is not necessary.

Personal Macro Workbook

如果你创建了在工作簿中使用的多个宏，个人宏工作簿提供了将所有宏存储在一个地方的功能。你将在下一章中了解有关此选项的更多信息。

If you create several macros that you use across your workbooks, Personal Macro Workbook provides you with the facility to store all the macros at one place. You will learn more about this option in the next chapter.

Saving a Macro Enabled File

如果你已选择 This Workbook 作为存储宏的选项，则需要连同宏一起保存工作簿。

If you had chosen This Workbook as the option for storing the macro, you would need to save your workbook along with the macro.

尝试保存工作簿。默认情况下，您需要 Excel 将工作簿另存为 .xls 文件。Excel 会显示一条消息，表示一个 Excel 功能 VB 项目无法保存为无宏工作簿，如下所示。

Try to save the workbook. By default, you would be asking Excel to save the workbook as an .xls file. Excel displays a message saying that an Excel feature VB project cannot be saved in a macro free workbook, as shown below.

Note - 如果您点击是，Excel 将把您的工作簿另存为无宏 .xls 文件，并且您使用此工作簿选项存储的宏将不会被保存。为避免这种情况，Excel 提供了一个选项，可将您的工作簿另存为具有 .xlsm 扩展名的宏启用工作簿。

Note − If you click Yes, Excel will save your workbook as a macro free .xls file and your macro that you stored with This Workbook option will not get saved. To avoid this, Excel provides you an option to save your workbook as a macro-enabled workbook that will have .xlsm extension.

Click No in the warning message box.
Select Excel Macro-Enabled Workbook (*.xlsm) in the Save as type.
Click Save.

您将在后面章节中的本教程中了解到更多这些内容。

You will learn more about these in later chapters in this tutorial.

Excel Macros - Macros in a Single Workbook

Excel 为你提供了一种工具，可以将所有宏存储在同一工作簿中。该工作簿称为个人宏工作簿 - Personal.xlsb。它是一个隐藏的工作簿，存储在你的计算机上，每当你打开 Excel 时都会打开它。这样能够让你从任何工作簿中运行宏。每台计算机将有一个个人宏工作簿，你无法在计算机之间共享它。你可以从计算机上的任何工作簿中查看和运行个人宏工作簿中的宏。

Excel provides you with a facility to store all your macros in a single workbook. The workbook is called Personal Macro Workbook - Personal.xlsb. It is a hidden workbook stored on your computer, which opens every time you open Excel. This enables you to run your macros from any workbook. There will be a single Personal Macro Workbook per computer and you cannot share it across computers. You can view and run the macros in your Personal Macro Workbook from any workbook on your computer.

Saving Macros in Personal Macro Workbook

你可以通过在录制宏时选择该工作簿作为存储选项，将宏保存到你的个人宏工作簿中。

You can save macros in your Personal Macro Workbook by selecting it as the storing option while recording the macros.

从类别 Store macro in 下面的下拉列表中选择“个人宏工作簿”。

Select Personal Macro Workbook from the drop down list under the category Store macro in.

Record your second macro.
Give macro details in the Record Macro dialog box as shown below.
Click OK.

开始录制。创建一个如下表所示的表。

Your recording starts. Create a table as shown below.

Stop recording.
Click the VIEW tab on the Ribbon.
Click Macros.
Select View Macros from the dropdown list. The Macro dialog box appears.

宏名称会显示一个 PERSONAL.XLSB! 前缀，表明宏在个人宏工作簿中。

The macro name appears with a prefix PERSONAL.XLSB! indicating that the Macro is in the Personal Macro Workbook.

保存您的工作簿。因为它没有保存在您的工作簿中，它将被保存为 .xls 文件，然后关闭 Excel。

Save your workbook. It will get saved as an .xls file as the macro is not in your workbook and close Excel.

您将收到以下有关将更改保存至个人宏工作簿的消息 −

You will get the following message regarding saving the changes to the Personal Macro Workbook −

单击“保存”按钮。您的宏保存在计算机上的 Personal.xlsb 文件中。

Click the Save button. Your macro is saved in the Personal.xlsb file on your computer.

Hiding / Unhiding Personal Macro Workbook

个人宏工作簿将默认隐藏。当您启动 Excel 时，个人宏工作簿会被加载，但您看不到它，因为它被隐藏了。您可以按以下步骤解除隐藏 −

Personal Macro Workbook will be hidden, by default. When you start Excel, the personal macro workbook is loaded but you cannot see it because it is hidden. You can unhide it as follows −

Click the VIEW tab on the Ribbon.
Click Unhide in the Window group.

将出现“解除隐藏”对话框。

The Unhide dialog box appears.

PERSONAL.XLSB 出现在“解除隐藏工作簿”框中，然后单击“确定”。

PERSONAL.XLSB appears in the Unhide workbook box and click OK.

现在您可以查看保存在个人宏工作簿中的宏。

Now you can view the macros saved in the personal macro workbook.

若要隐藏个人宏工作簿，请执行以下操作 −

To hide the personal macro workbook, do the following −

Click on the personal macro workbook.
Click the VIEW tab on the Ribbon.
Click Hide on the Ribbon.

Running Macros Saved in Personal Macro Workbook

您可以从任何工作簿运行保存在个人宏工作簿中的宏。要运行宏，个人宏工作簿是隐藏的还是解除隐藏的并不会产生任何区别。

You can run the macros saved in personal macro workbook from any workbook. To run the macros, it does not make any difference whether the personal macro workbook is hidden or unhidden.

Click View Macros.
Select the macro name from the macros list.
Click the Run button. The macro will run.

Adding / Deleting Macros in Personal Macro Workbook

通过在录制宏时将其选为“存储宏于”选项，您可以向个人宏工作簿中添加更多宏，正如您之前看到的。

You can add more macros in personal macro workbook by selecting it for Store macro in option while recording the macros, as you had seen earlier.

您可以按以下步骤删除个人宏工作簿中的宏 −

You can delete a macro in personal macro workbook as follows −

Make sure that the personal macro workbook is unhidden.
Click the macro name in the View Macros dialog box.
Click the Delete button.

如果个人宏工作簿被隐藏，您将收到一条消息“无法编辑隐藏工作簿中的宏”。

If the personal macro workbook is hidden, you will get a message saying “Cannot edit a macro on a hidden workbook”.

取消隐藏个人宏工作簿并删除选定的宏。

Unhide the personal macro workbook and delete the selected macro.

该宏将不会出现在宏列表中。但是，当您创建新宏并将其保存在个人工作簿中或删除其包含的所有宏后，系统将提示您保存个人工作簿，就像您第一次保存一样。

The macro will not appear in the macros list. However, when you create a new macro and save it in your personal workbook or delete any macros that it contains, you will be prompted to save the personal workbook just as in the case you saved it first time.

Excel Macros - Security

您在 Excel 中创建的宏将以编程语言 VBA（应用程序的可视化 Basic）编写。您将在后面的章节中了解 Excel 宏代码。如您所知，在存在可执行代码时，就会有病毒的威胁。宏也容易受到病毒的攻击。

The macros that you create in Excel would be written in the programming language VBA (Visual Basic for Applications). You will learn about the Excel macro code in later chapters. As you are aware, when there is an executable code, there is a threat of viruses. Macros are also susceptible to viruses.

What are Macro Viruses?

其中编写宏的 Excel VBA 可以访问大多数 Windows 系统调用，并且在工作簿打开时自动执行。因此，存在由宏编写并隐藏在 Excel 中的病毒，在打开工作簿时执行的潜在威胁。因此，Excel 宏可能在许多方面对您的计算机非常危险。但是，Microsoft 已采取适当措施来保护工作簿免受宏病毒侵害。

Excel VBA in which the Macros are written has access to most Windows system calls and executes automatically when workbooks are opened. Hence, there is a potential threat of the existence of a virus written as a macro and is hidden within Excel that are executed on opening a workbook. Therefore, Excel macros can be very dangerous to your computer in many ways. However, Microsoft has taken appropriate measures to shield the workbooks from macro viruses.

Microsoft 引入了宏安全性，以便您可以识别您信任的宏和您不能信任的宏。

Microsoft has introduced macro security so that you can identify which macros you can trust and which you cannot.

Macro Enabled Excel Workbooks

最重要的 Excel 宏安全功能是 - 文件扩展名。

The most important Excel macro security feature is - file extensions.

Excel 工作簿默认情况下将以 .xlsx 文件扩展名保存。您始终可以信任具有 .xlsx 文件扩展名的工作簿，因为它们无法存储宏并且不会携带任何威胁。

Excel workbooks will be saved with .xlsx file extension by default. You can always trust workbooks with .xlsx file extension, as they are incapable of storing a macro and will not carry any threat.

包含宏的工作簿将以 .xlsm 文件扩展名进行保存。它们被称为启用宏的 Excel 工作簿。在打开此类工作簿之前，您应确保它们包含的宏不是恶意的。为此，您必须确保您信任此类工作簿的来源。

Excel workbooks with macros are saved with .xlsm file extension. They are termed as Macro Enabled Excel Workbooks. Before you open such workbooks, you should make sure that the macros they contain are not malicious. For this, you must ensure that you can trust the origin of this type of workbooks.

Ways of Trusting Macro Enabled Workbook

Excel 提供了三种方法来信任已启用宏的工作簿。

Excel provides three ways to trust a macro enabled workbook.

Placing the macro enabled workbooks in a trusted folder
Checking if a macro is digitally signed
Enabling security alert messages before opening macro enabled workbooks

Placing the macro enabled workbooks in a trusted folder

这是管理宏安全最简单、最有效的方法。Excel 允许您将文件夹指定为受信任位置。将所有已启用宏的工作簿放在该受信任文件夹中。您可以在不受警告或限制的情况下打开已保存到该位置的已启用宏的工作簿。

This is the easiest and best way to manage macro security. Excel allows you to designate a folder as a trusted location. Place all your macro-enabled workbooks in that trusted folder. You can open macro-enabled workbooks that are saved to this location without warnings or restrictions.

Checking if a macro is digitally signed

数字签名可确认作者的身份。您可以将 Excel 配置为在无警告或限制的情况下，运行来自受信任人员的经过数字签名的宏。如果自作者签名后工作簿已更改，Excel 也会向收件人发出警告。

Digital signatures confirm the identity of the author. You can configure Excel to run digitally signed macros from trusted persons without warnings or restrictions. Excel will also warn the recipient if it has been changed since the author signed it.

Enabling security alert messages before opening macro enabled workbooks

当您打开工作簿时，Excel 会警告您该工作簿中包含宏，并询问您是否要启用它们。如果工作簿来源可靠，您可以单击“{s0}”按钮。

When you open a workbook, Excel warns you that the workbook contains macros and asks whether you wish to enable them. You can click the Enable Content button if the source of the workbook is reliable.

您可以在 Excel 选项中的“信任中心”中设置这三个选项中的任何一个。

You can set any of these three options in the Trust Center in the Excel Options.

如果您在组织中工作，系统管理员可能已经更改了默认设置，以防止任何人更改设置。Microsoft 建议您不要更改“信任中心”中的安全设置，因为后果可能是丢失数据、数据盗窃或您的计算机或网络上的安全泄露。

If you work in an organization, the system administrator might have changed the default settings to prevent anyone from changing the settings. Microsoft advises that you do not change security settings in the Trust Center as the consequences can be loss of data, data theft or security compromises on your computer or network.

但是，您可以在以下章节了解宏安全设置，并检查它们是否需要更改。您必须运用自己的直觉，根据上下文和您对文件来源的了解来决定这些选项。

However, you can learn the macro security settings in the following sections and check if they are to be changed. You have to use your own instinct to decide on any of these options based on the context and your knowledge of the file origin.

Macro Security Settings in Trust Center

宏设置位于 Excel 选项中的“信任中心”。要访问“信任中心”，请执行以下操作：

The macro settings are located in the Trust Center in the Excel Options. To access the Trust Center, do the following −

Click the FILE tab on the Ribbon.
Click Options. The Excel Options dialog box appears.
Click Trust Center in the left pane.
Click the Trust Center Settings button under Microsoft Excel Trust Center.

这将显示“{s3}”对话框。

The Trust Center dialog box appears.

您将在左窗格中看到“Excel 信任中心”中提供的各种选项。您将在以下章节中了解与 Excel 宏相关的选项。

You will see various options available in the Excel Trust Center in the left pane. You will learn about the options related to Excel macros in the following sections.

Macro Settings

宏设置位于“信任中心”。

Macro settings are located in the Trust Center.

在“宏设置”下，提供了四个选项。

Under Macro Settings, four options are available.

Disable all macros without notification − If this option is chosen, Macros and security alerts about macros are disabled.
Disable all macros with notification − Macros are disabled, but security alerts appear if there are macros present. You can enable macros on a case-by-case basis.
Disable all macros except digitally signed macros − Macros are disabled but security alerts appear if there are macros present. However, if the macro is digitally signed by a trusted publisher, the macro runs if you trust the publisher. If you have do not trust the publisher, you will be notified to enable the signed macro and trust the publisher.
Enable all macros (not recommended, susceptible to macro viruses) − If this option is chosen, all macros run. This setting makes your computer vulnerable to potentially malicious code.

您可以在“开发人员宏设置”下拥有一个带有复选框的附加安全选项。

You have an additional security option under Developer Macro Settings with a Check box.

Trust access to the VBA project object model. This option allows programmatic access to the Visual Basic for Applications (VBA) object model from an automation client. This security option is for code written to automate an Office program and manipulate the VBA environment and object model. It is a per-user and per-application setting, and denies access by default, hindering unauthorized programs from building harmful self-replicating code. For automation clients to access the VBA object model, the user running the code must grant access. To turn on access, select the check box.

Defining a Trusted Location

如果你认为某个启用宏的工作簿来自可靠来源，最好将该文件移动到 Excel 识别的受信任位置，而不是将默认信任中心设置更改为安全性较低的宏安全设置。

If you think that a macro-enabled workbook is from a reliable source, it is better to move the file to the trusted location identified by Excel, instead of changing the default Trust Center settings to a less-safe macro security setting.

你可以在信任中心中找到受信任的文件夹设置。

You can find the trusted folder settings in the Trust Center.

单击“信任中心”对话框中的“受信任位置”。由 Microsoft Office 设置的受信任位置显示在右侧。

Click the Trusted Locations in the Trust Center dialog box. The Trusted Locations set by Microsoft Office appear on the right side.

你可以添加新位置、删除现有位置和修改现有位置。Microsoft Office 将把识别的受信任位置视为打开文件的可靠位置。但是，如果你添加或修改位置，确保该位置是安全的。

You can add new locations, remove the existing locations and modify the existing locations. The identified trusted locations will be treated by Microsoft office as reliable for opening files. However, if you add or modify a location, ensure that the location is secure.

你还可以找到 Office 不推荐的选项，例如 Internet 上的位置。

You can also find the options that office does not recommend, such as locations on internet.

Digitally Signed Macros from Reliable Sources

Microsoft 提供了一个用于适应经过数字签名的宏的选项。但是，即使宏经过数字签名，你也需要确保它来自受信任的发布者。

Microsoft provides an option to accommodate digitally signed macros. However, even if a macro is digitally signed, you need to ensure that it is from a trusted publisher.

你可以在信任中心找到受信任的发布者。

You will find the trusted publishers in in the Trust Center.

Click Trusted Publishers in the Trust Center dialog box. A list of certificates appear on the right side with the details – Issued To, Issued By and Expiration Date.
Select a certificate and click View.

将显示证书信息。

The certificate information is displayed.

正如你在本章前面学到的，你可以设置一个仅在你信任发布者时才运行经过数字签名的宏的选项。如果你不信任发布者，系统会通知你启用已签名的宏并信任发布者。

As you have learnt earlier in this chapter, you can set an option to run a macro that is digitally signed only if you trust the publisher. If you do not trust the publisher, you will be notified to enable the signed macro and trust the publisher.

Using Warning Messages

当你打开带有宏的文件时，消息栏会显示安全警告。带有盾牌图标的黄色消息栏会警告你宏已禁用。

The Message Bar displays security alert when there are macros in the file that you are opening. The yellow Message Bar with a shield icon alerts you that the macros are disabled.

如果你知道宏或这些宏来自可靠来源，你可以在消息栏上单击“启用内容”按钮以启用宏。

If you know that the macro or macros are from a reliable source, you can click n the Enable Content button on the Message Bar, to enable the macros.

你可以禁用消息栏选项，如果你不希望收到安全警告。另一方面，你可以启用消息栏选项以提高安全性。

You can disable the Message Bar option if you do not want security alerts. On the other hand, you can enable the Message Bar option to increase security.

Enabling / Disabling Security Alerts on the Message Bar

你可以按照以下方式启用/禁用带有消息栏的安全警告：

You can enable / disable security alerts with Message Bars as follows −

Click the FILE tab on the Ribbon.
Click Options. The Excel Options dialog box appears.
Click Trust Center.
Click the Trust Center Settings button.
Click Message Bar.

会出现所有 Office 应用程序的消息栏设置。

The Message Bar Settings for all Office Applications appear.

Showing the Message Bar 下面有两个选项。

There are two options under - Showing the Message Bar.

Option 1 − 当处于活动内容（例如宏）被阻止时，在所有应用程序中显示消息栏。

Option 1 − Show the Message Bar in all applications when active content such as macros is blocked.

This is the default option. The Message Bar appears when potentially unsafe content has been disabled.
If you had selected - Disable all macros without notification in the Macro Settings of the Trust Center, this option is not selected and the Message Bar does not appear.

Option 2 − 永不显示有关被阻止内容的信息。

Option 2 − Never show information about blocked content.

如果此选项被选择，它将禁用消息栏，并且不出现关于安全问题的任何警报，无论信任中心中的任何安全设置如何。

If this option if selected, it disables the Message Bar and no alerts appear about security issues, regardless of any security settings in the Trust Center.

Excel Macros - Absolute References

Excel 宏可以使用绝对引用或相对引用两种方式记录。使用绝对引用记录的宏将记录的步骤精确地放到其记录的单元格中，而不考虑活动单元格。另一方面，使用相对引用记录的宏可以在工作表的不同部分执行记录的任务。

Excel macros can be recorded either with absolute references or relative references. A macro recorded with absolute references places the recorded steps exactly in the cells where it was recorded, irrespective of the active cell. On the other hand, a macro recorded with relative references can perform the recorded tasks at different parts on the worksheet.

您将在本章学习关于宏记录的绝对引用。您将在下一章学习关于相对引用。

You will learn about absolute references for macro in this chapter. You will learn about relative references in the next chapter.

假设您必须在每天结束时提交一份关于团队工作情况的报告，格式如下 −

Suppose you have to submit a report about your team’s work at the end of every day in the following format −

现在，报告应放在单元格 B2 中，并应采用给定的格式。

Now, the report should be placed in the cell B2 and should be in the given format.

填好的报告样例将如下所示 −

A sample filled in report will be as shown below −

除了以下单元格中的数据之外，您为项目生成的每个报告的信息都是常量。

Except for the data in the following cells, the information is constant for every report that you generate for the project.

C3 – Report for Date.
C13 – No. of Tasks Completed Today.
C14 – Total No. of Tasks Completed.
C15 – % Work Complete.

在这些单元格中，您还可以在 C3（日期报告）中放置 Excel 函数 = TODAY () ，这样可以在不需要您干预的情况下放置报告日期。此外，在单元格 C15 中，您可以输入公式 C14/C12，并将单元格 C15 的格式设置为百分比，让 Excel 为您计算完成工作百分比。

Of these also, in C3 (Report for Date) you can place the Excel function = TODAY () that places the date of your report without your intervention. Further, in cell C15, you can have the formula C14/C12 and format the cell C15 as percentage to have the % Work Complete calculated by Excel for you.

这样，只需要 C13 和 C14 这两个单元格由您每天填入。因此，每次必须创建报告时，理想情况下其余单元格都应该填好信息。这将为您节省时间，您只需几分钟就能完成例行的报告活动。

This leaves you with only two cells – C13 and C14 that need to be filled in by you every day. Hence, it would be ideal to have information for the rest of the cells, every time you have to create the report. This saves time for you and you can do the mundane activity of reporting in just few minutes.

现在，假设您必须为三个项目发送此类报告。您可以想象这样可以为您节省多少时间，让您投入更具挑战性的一天工作，当然也可以从管理部门获得赞誉。

Now, suppose you have to send such reports for three projects. You can imagine the time you can save and take up more challenging work for the day and of course get the accolades from your management.

您可以通过为每个项目录制一个宏来实现此目的，并在日常工作中运行这些宏，从而在短短几分钟内生成所需的报告。但是，每次运行宏时，报告都应如上所述显示在工作表上，而不考虑活动单元格。为此，您必须使用绝对引用。

You can achieve this by recording a macro per project and running them on a day-to-day basis to generate the required reports in a matter of just few minutes. However, every time you run the macro, the report should appear on the worksheet as given above, irrespective of the active cell. For this, you have to use absolute references.

Ensuring Absolute References

要使用绝对引用录制宏，您必须确保从需要开始执行步骤的单元格开始录制宏。这意味着，对于上一节中给出的示例，您需要执行以下操作 −

To record a macro with absolute references, you have to ensure that the macro is being recorded starting from the cell where the steps have to start. This means, in the case of the example given in the previous section, you need to do the following −

Start recording the macro.
Create a new worksheet.
Click in any cell other than B2 in the new worksheet.
Click in the cell B2.
Continue recording the macro.

这样，每次运行宏时，都会为每个新报告创建一个新工作表，并将报告格式放置在单元格 B2 中。

This will create a new worksheet for every new report and get the report format placed in the cell B2 every time you run the macro.

Note − 以上给出的前三个步骤至关重要。

Note − The first three steps given above are essential.

If you do not create a new worksheet, when you run the macro, it places whatever you recorded on the same worksheet at the same place. This is not what you want. You need to have every report on a different worksheet.
If you do not click in a different cell at the beginning of the recording, even if the active cell is B2, Excel places the recorded steps in the active cell. When you run the macro, it will place the recorded report format at any part of the worksheet based on the active cell. By explicitly clicking in a cell other than B2 and then the the cell B2, you are telling the recorder to always place your macro steps in the cell B2.

Recording a Macro

你可以使用功能区中的“视图”选项卡下的 Record Macro 命令开始录制宏→宏。你还可以单击 Excel 任务栏左侧的 Start Recording Macro 按钮。

You can start recording the macro with the Record Macro command on the Ribbon under the VIEW tab → Macros. You can also click the Start Recording Macro button present on left side of the Excel task bar.

Start recording the macro. The Record Macro dialog box appears.
Give a meaningful name to identify the macro as a report of a particular project.
Select This Workbook under Store macro in, as you will produce reports from this specific workbook only.
Give a description to your macro and click OK.

你的宏开始录制。

Your macro starts recording.

Create a new worksheet. This ensures your new report will be on a new worksheet.
Click in any cell other than B2 in the new worksheet.
Click in the cell B2. This ensures that the macro places your recorded steps in B2 always.
Create the format for the report.
Fill in the static information for the project report.
Place = TODAY () in C3 and = C14/C12 in the cell C15.
Format the cells with dates.

停止录制宏。

Stop recording the macro.

你可以使用“视图”选项卡下的功能区中的 Stop Recording 命令或者单击 Excel 任务栏左侧的“停止录制宏”按钮来停止录制宏→宏。

You can stop recording the macro either with the Stop Recording command on the Ribbon under VIEW tab → Macros or by clicking the Stop Recording Macro button present on left side of the Excel task bar.

你的项目报告宏已准备就绪。将工作簿保存为启用宏的工作簿（扩展名为 .xlsm）。

Your Project Report macro is ready. Save the workbook as a macro-enabled workbook (with .xlsm extension).

Running a Macro

你只需运行宏，就可以在几秒钟内生成任意数量的报告。

You can generate any number of reports in a few seconds just by running the macro.

Click the VIEW button on the Ribbon.
Click Macros.
Select View Macros from the dropdown list. The Macro dialog box appears.
Click the macro Report_ProjectXYZ.
Click the Run button.

工作簿中将创建一个新工作表，其中 B2 单元格内创建了报表模板。

A new worksheet will be created in your workbook, with the report stencil created in it in the cell B2.

Excel Macros - Relative References

相对引用宏记录活动单元格的偏移量。如果您必须在工作表的不同位置重复这些步骤，则此类宏非常有用。

Relative reference macros record an offset from the active cell. Such macros will be useful if you have to repeat the steps at various places in the worksheet.

假设需要分析从 280 个选区收集的选民数据。对于每个选区，将收集以下详细信息：

Suppose you are required to analyze the data of voters collected from 280 constituencies. For each constituency, the following details are collected −

Constituency name.
Total population in the constituency.
Number of voters in the constituency.
Number of male voters, and
Number of female voters.

数据按照如下所示提供给您，在工作表中。

The data is provided to you in a worksheet as given below.

无法按照上述格式分析数据。因此，按照下列方式在表格中排列数据。

It is not possible to analyze the data in the above format. Therefore, arrange the data in a table as shown below.

如果您尝试按照上述格式排列给定的数据：

If you attempt to arrange the given data in the above format −

It takes substantial amount of time to arrange the data from the 280 constituencies
It can be error prone
It becomes a mundane task not allowing you to focus on technical things

解决方案是录制一个宏，这样你可以在几秒内完成任务。宏需要使用相对引用，因为在安排数据时你需要向下移动行。

The solution is to record a macro so that you can complete the task in not more than a few seconds. The macro needs to use relative references, as you will move down the rows while arranging the data.

Using Relative References

为了让宏记录器知道它必须使用相对引用，请执行以下操作 -

In order to let the macro recorder know that it has to use relative references, do the following −

Click the VIEW tab on the Ribbon.
Click Macros.
Click Use Relative References.

Preparing the Data Format

整理上述给定数据的第一个步骤是在带有标题的表中定义数据格式。

The first step in arranging the above given data is to define the data format in a table with headers.

创建如下所示的标题行。

Create the row of headers as shown below.

Recording a Macro

录制宏如下所示 −

Record the macro as follows −

Click Record Macro.
Give a meaningful name, say, DataArrange to the macro.
Type = row ()- 3 in the cell B4. This is because the S. No. is the current row number – the 3 rows above it.
Cut the cells B5, B6, B7, B8 and B9 and paste it in the cells C4 to C8 respectively.
Now click in the cell B5. Your table looks as shown below.

第一个数据集被安排在表格的第一行中。删除行 B6 - B11，然后点击单元格 B5。

The first data set is arranged in the first row of the table. Delete the rows B6 – B11 and click in the cell B5.

您会看到活动单元格是 B5，下一个数据集将被放置在这里。

You can see that the active cell is B5 and the next data set will be placed here.

停止录制宏。用于安排数据的宏已准备就绪。

Stop recording the macro. Your macro for arranging the data is ready.

Running a Macro

您需要反复运行宏才能完成表格中的数据排列，如下所示。

You need to run the macro repeatedly to complete the data arrangement in the table as given below.

活动单元格是 B5。运行宏。第二个数据集将被安排在表格的第二行，活动单元格将成为 B6。

The active cell is B5. Run the macro. The second data set will be arranged in the second row of the table and the active cell will be B6.

再次运行宏。第三个数据集将被安排在表格的第三行，活动单元格将成为 B7。

Run the macro again. The third data set will be arranged in the third row of the table and the active cell will become B7.

每次运行宏时，活动单元格都会前进到下一行，从而使能够在适当位置重复记录的步骤。这是因为宏中的相对引用。

Each time you run the macro, the active cell advances to the next row, facilitating the repetition of recorded steps at the appropriate positions. This is possible because of the relative references in macro.

运行宏，直到所有 280 个数据集被排列成表格中的 280 行。此过程只需要几秒钟，并且由于步骤是自动化的，所以整个练习没有错误。

Run the macro until all the 280 data sets are arranged into 280 rows in the table. This process takes a few seconds and as the steps are automated, the entire exercise is error free.

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.

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

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

Right click on the Ribbon.
Select Customize the Ribbon from the dropdown list.

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

The Excel Options dialog box appears.

Select Main Tabs from Customize the Ribbon dropdown list.
Check the box – Developer in the Main Tabs list and click OK. The developer tab appears.

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 −

Visual Basic
Macros
Record Macro
Use Relative References
Macro Security

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.

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

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

VBE 在新窗口中出现。

VBE appears in a new window.

启用 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 −

Projects Explorer.
Properties.
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.

Excel Macros - Understanding Codes

当你记录宏时，Excel 会将其保存为 VBA 代码。你可以在 VBA 编辑器中查看此代码。如果你对 Excel VBA 有丰富的了解，则可以理解此代码并对其进行修改。你可以参考本教程库中的 Excel VBA 教程来掌握此语言。

When you record a macro, Excel stores it as a VBA code. You can view this code in the VBA editor. You can understand the code and modify it if you have substantial knowledge of Excel VBA. You can refer to the Excel VBA tutorial in this tutorials library to obtain a grasp on the language.

但是，你仍然可以在 Excel VBA 编辑器中查看宏代码，并将它与你在宏中记录的步骤相匹配。在本教程中，你将学习如何查看和理解你创建的第一个宏（MyFirstMacro）的代码。

However, you can still view the macro code in Excel VBA editor and match it to the steps that you recorded in macro. You will learn how to view the code and understand it for the first macro that you created in this tutorial – MyFirstMacro.

Viewing a Macro Code in VBA Editor

若要查看宏代码，请执行下列步骤：

To view a macro code, do the following −

Open the workbook in which you stored the macro.
Click VIEW tab on the Ribbon.
Click Macros.
Select View Macros from the dropdown list.

出现宏对话框。

The Macro dialog box appears.

Click MyFirstMacro in the macros list.
Click the Edit button.

VBA 编辑器将打开，而宏“MyFirstMacro”的代码将出现。

The VBA editor opens and the code of the macro MyFirstMacro appears.

Understanding the Recorded Actions as Parts of Code

你可以浏览宏代码，并将它们映射到你的记录步骤。

You can browse through the macro code and map them to your recorded steps.

Start reading the code.
Map the code to the recorded steps.

向下滚动代码以查看更多代码。或者，你可以扩大代码窗口。

Scroll down the code to view more code. Alternatively, you can enlarge the code window.

请注意，代码很简单。如果你学习了 Excel VBA，则可以通过在 VBA 编辑器中编写代码来创建宏。

Observe that the code is simple. If you learn Excel VBA, you can create the macros by writing the code in the VBA editor.

你将在“使用 VBA 编辑器创建宏”章节中学习如何编写 VBA 代码来创建宏。

You will learn how to write a VBA code to create a macro in the chapter - Creating a Macro Using VBA Editor.

Excel Macros - Assigning Macros to Objects

假设您已经创建了一个需要多次执行的宏。举例来说，已为绝对引用和相对引用创建宏。然后，如果您能单击鼠标运行宏，这会让您更轻松。可以通过将宏分配给对象（例如形状、图形或控件）来实现这一点。

Suppose you have created a macro that you need to execute several times. For example, the macros that you have created for absolute references and relative references. Then, it would be easy for you if you can run the macro using a mouse click. You can accomplish this by assigning the macro to an object such as a shape or a graphic or a control.

在本节中，您将学习如何将对象包含在工作簿中，并给它赋予一个宏。

In this chapter, you will learn how to include an object in your workbook and assign a macro to it.

回想起您使用相对引用创建的宏。该宏将一列中的数据整理成一个表，以便于数据分析。

Recall the macro that you created using relative references. The macro arranges the data given in one column into a table to facilitate data analysis.

Assigning a Macro to a Shape

您可以在工作表中插入一个有意义的形状，带有不言自明的文本，当单击该形状时运行分配给它的宏。

You can insert a shape in your worksheet that is in a meaningful form with self-explanatory text, which when clicked runs the macro assigned to it.

Click the INSERT tab on the Ribbon.
Click Shapes in the Illustrations group.
Select any of the ready-made shapes that appear in the dropdown list. For example, the Flowchart shape – Preparation, as you are in the process of preparing the data.

绘制形状并设置其格式。

Draw the shape and format it.

Right click on the shape and select Edit Text from the dropdown list.
Type text inside the shape - Run Macro.
Format the text.

Right click on the shape.
Select Assign Macro from the dropdown list.

将宏分配对话框显示。单击宏名，即 RelativeMacro，然后单击确定。

The Assign Macro dialog box appears. Click the macro name i.e. RelativeMacro and click OK.

宏被分配给形状。

The macro is assigned to the shape.

Click in the cell where you have to run the macro say B4.
Move the cursor (pointer) onto the shape. The cursor (pointer) changes to finger.

现在单击形状。宏将运行。重复单击鼠标即可多次运行宏，则可以在几秒钟内完成将数据整理成表格的任务。

Now click the shape. The macro will run. Just repeat the mouse clicks to run the macro several times and you are done with the task of arranging the data into a table in a matter of a few seconds.

Assigning a Macro to a Graphic

您可以在工作表中插入图形并为其分配宏。可以将图形选为宏的可视化效果。例如，您可让一张表格图形表示该宏会将数据整理成表格。

You can insert a graphic in the worksheet and assign a macro to it. The graphic can be chosen to visualize your macro. For example, you can have a graphic of table representing that the macro will arrange the data into a table.

Click the INSERT tab on the Ribbon.
Click Pictures in the Illustrations group.
Select a file that contains your graphic.

其余步骤与前一节中所述形状的步骤相同。

The rest of the steps are the same as those of shape given in the previous section.

Assigning a Macro to a Control

插入 VBA 控件并为其分配宏可使得您的工作看起来更专业。您可以从功能区上的开发工具卡插入 VBA 控件。

Inserting a VBA control and assigning a macro to it makes your work look professional. You can insert VBA controls from the Developer tab on the Ribbon.

Click the DEVELOPER tab on the Ribbon.
Click Insert in the Controls group.

如下图所示，从下拉列表中选择 Form Controls 下的按钮图标。

Select the Button icon under Form Controls from the dropdown list as shown in screenshot given below −

Click the cell on the worksheet where you want to insert the Button control. The Assign Macro dialog box appears.
Click the macro name and click OK.

将插入分配有宏的控件按钮。

The control button with the assigned macro will be inserted.

Right click on the button.
Click Edit Text.
Type – Run Macro.
Format Text and resize Button.

单击按钮多次可多次运行宏。

You can run the macro any number of times by just clicking the Button repeatedly.

使用窗体控件是一种与用户交互的简单有效的途径。您将在这一章学习更多关于此内容 – 与用户交互。

Using Form Controls is an easy and effective way of interacting with the user. You will learn more about this in the chapter – Interacting with the User.

Excel Macros - Running a Macro

可以在工作簿中执行宏的几种方法。宏可能会保存在启用了宏的工作簿中，或者保存在您的个人宏工作簿中，您可以在任何工作簿中访问它，如您之前了解的那样。

There are several ways of executing a macro in your workbook. The macro would have been saved in your macro enabled workbook or in your Personal macro workbook that you can access from any workbook as you had learnt earlier.

您可以通过以下方式运行宏 −

You can run a macro in the following ways −

Running a Macro from the View Tab
Running a Macro by pressing Ctrl plus a shortcut key
Running a Macro by clicking a button on the Quick Access Toolbar
Running a Macro by clicking a button in a Custom Group on the Ribbon
Running a Macro by clicking on a Graphic Object
Running a Macro from Developer Tab
Running a Macro from VBA Editor

Running a Macro from View Tab

您已经了解了从功能区上的视图选项卡运行宏。快速回顾 −

You have already learnt running a macro from the View tab on the Ribbon. A quick recap −

Click the VIEW tab on the Ribbon.
Click Macros.
Select View Macros from the dropdown list.

出现宏对话框。

The Macro dialog box appears.

Click the macro name.
Click the Run button.

Running a Macro with Shortcut Key

您可以为宏分配快捷键（Ctrl + 键）。您可以在 Create Macro 对话框中录制宏时执行此操作。否则，您可以在 Macro Options 对话框中稍后添加此操作。

You can assign a shortcut key (Ctrl + key) for a macro. You can do this while recording the macro in the Create Macro dialog box. Otherwise, you can add this later in the Macro Options dialog box.

Adding a Shortcut Key While Recording a Macro

Click the VIEW tab.
Click Macros.
Select Record Macro from the dropdown list.

“创建宏”对话框出现。

The Create Macro dialog box appears.

Type a macro name
Type a letter, say q, in the box next to Ctrl + under Shortcut key.

Adding a Shortcut Key in Macro Options

Click the VIEW tab.
Click Macros.
Select View Macros from the dropdown list.

出现宏对话框。

The Macro dialog box appears.

Select the macro name.
Click the Options button.

出现“宏选项”对话框。在“快捷键”下方的框中键入一个字母，例如 q。单击“确定”。

The Macro Options dialog box appears. Type a letter, say q, in the box next to Ctrl + under Shortcut key. Click OK.

要使用快捷键运行宏，请同时按 Ctrl 键和 q 键。宏将运行。

To run the macro with the shortcut key, press the Ctrl key and the key q together. The macro will run.

Note − 可以使用任何小写或大写字母作为宏的快捷键。如果使用作为 Excel 快捷键的任意 Ctrl + 字母组合，将覆盖它。例如包括 Ctrl+C、Ctrl+V、Ctrl+X 等。因此，在选择字母时请自行斟酌。

Note − You can use any lowercase or uppercase letters for the shortcut key of a macro. If you use any Ctrl + letter combination that is an Excel shortcut key, you will override it. Examples include Ctrl+C, Ctrl+V, Ctrl+X, etc. Hence, use your jurisdiction while choosing the letters.

Running a Macro through Quick Access Toolbar

可以向快速访问工具栏添加宏按钮，并通过单击它来运行宏。当你将宏存储在个人宏工作簿中时，此选项会非常有用。添加的按钮将显示在你打开的任何工作簿中的快速访问工具栏中，从而使你能够轻松运行宏。

You can add a macro button to the Quick Access Toolbar and run the macro by clicking it. This option would be useful when you store your macros in personal macro workbook. The added button will appear on the Quick Access Toolbar in whatever workbook you open, thus making it easy for you to run the macro.

假设你的个人宏工作簿中有一个名为 MyMacro 的宏。

Suppose you have a macro with the name MyMacro in your personal macro workbook.

要向快速访问工具栏添加宏按钮，请执行以下操作：

To add the macro button to the Quick Access Toolbar do the following −

Right click on the Quick Access Toolbar.
Select Customize Quick Access Toolbar from the dropdown list.

将出现“Excel 选项”对话框。在类别 Choose commands from 下方的下拉列表中选择“宏”。

The Excel Options dialog box appears. Select Macros from the dropdown list under the category- Choose commands from.

“宏”下方将显示宏列表。

A list of macros appears under Macros.

Click PERSONAL.XLSB!MyMacro.
Click the Add button.

宏名称显示在右侧，带有一个宏按钮图像。

The macro name appears on the right side, with a macro button image.

要更改宏按钮图像，请执行以下操作：

To change the macro button image, proceed as follows −

Click the macro name in the right box.
Click the Modify button.

将出现 Modify Button 对话框。选择一个符号将其设置为按钮的图标。

The Modify Button dialog box appears. Select one symbol to set it as the icon of the button.

将指针放在快速访问工具栏上的按钮图像时显示的“显示名称”修改为一个有意义的名称，例如本示例中的“运行 MyMacro”。单击“确定”。

Modify the Display name that appears when you place the pointer on the Button image on the Quick Access Toolbar to a meaningful name, say, Run MyMacro for this example. Click OK.

右窗格中将更改宏名称和图标符号。单击“确定”。

The Macro name and the icon symbol change in the right pane. Click OK.

宏按钮显示在快速访问工具栏上，宏显示名称将显示在你将指针放在按钮上时。

The macro button appears on the Quick Access Toolbar and the macro display name appears when you place the pointer on the button.

要运行宏，只需单击快速访问工具栏上的宏按钮即可。

To run the macro, just click the macro button on the Quick Access Toolbar.

Running a Macro in Custom Group

可以在功能区上添加一个自定义组和自定义按钮，并将宏分配给该按钮。

You can add a custom group and a custom button on the Ribbon and assign your macro to the button.

Right click on the Ribbon.
Select Customize the Ribbon from the dropdown list.

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

The Excel Options dialog box appears.

Select Main Tabs under Customize the Ribbon.
Click New Tab.

新选项卡（自定义）显示在“主选项卡”列表中。

The New Tab (Custom) appears in Main Tabs list.

Click New Tab (Custom).
Click the New Group button.

新组（自定义）显示在“新选项卡（自定义）”下方。

The New Group (Custom) appears under New Tab (Custom).

Click New Tab (Custom).
Click the Rename button.

将出现“重命名”对话框。输入要在功能区的“主选项卡”中显示的自定义选项卡的名称，比如 - 我的宏，然后单击“确定”。

The Rename dialog box appears. Type the name for your custom tab that appears in Main tabs on the Ribbon, say - My Macros and click OK.

Note − 功能区上的所有“主选项卡”均为大写字母。你可以自行选择使用大写字母还是小写字母。我选择了首字母大写的形式，以便在标准选项卡中显示出来。

Note − All the Main tabs on the Ribbon are in uppercase letters. You can use your discretion to use uppercase or lowercase letters. I have chosen lowercase with capitalization of words so that it stands out in the standard tabs.

新选项卡名称更改为“我的宏（自定义）”。

The new tab name changes to My Macros (Custom).

Click New Group (Custom).
Click the Rename button.

将出现 Rename 对话框。在“显示名称”对话框中键入组名，然后单击“确定”。

The Rename dialog box appears. Type the group name in the Display name dialog box and click OK.

新组名称更改为“个人宏（自定义）”。

The new group name changes to Personal Macros (custom).

单击 Choose commands from 下的左窗格中的“宏”。

Click Macros in the left pane under Choose commands from.

Select your macro name, say – MyFirstMacro from the macros list.
Click the Add button.

该宏将添加到“个人宏（自定义）”组下方。

The macro will be added under the Personal Macros (Custom) group.

Click My Macros (Custom) in the list.
Click the arrows to move the tab up or down.

选项卡在“主选项卡”列表中的位置决定了它在功能区中的位置。单击“确定”。

The position of the tab in the main tabs list determines where it will be placed on the Ribbon. Click OK.

你的自定义选项卡 - 我的宏出现在功能区上。

Your custom tab – My Macros appears on the Ribbon.

单击选项卡 - 我的宏。个人宏组出现在功能区上。MyFirstMacro 出现在个人宏组中。要运行宏，只需单击“个人宏”组中的“MyFirstMacro”。

Click the tab - My Macros. Personal Macros group appears on the Ribbon. MyFirstMacro appears in the Personal Macros group. To run the macro, just click on MyFirstMacro in the Personal Macros group.

Running a Macro by Clicking an Object

你可以在工作表中插入对象，例如形状、图形或 VBA 控件，并为其分配宏。要运行宏，只需单击该对象。

You can insert an object such as a shape, a graphic or a VBA control in your worksheet and assign a macro to it. To run the macro, just click the object.

有关使用对象运行宏的详细信息，请参阅章节——将宏分配给对象。

For details on running a macro using objects, refer to chapter – Assigning Macros to Objects.

Running a Macro from the Developer Tab

您可以从“开发工具”选项卡运行宏。

You can run a macro from the Developer tab.

Click the Developer tab on the Ribbon.
Click Macros.

“宏”对话框出现。单击宏名称，然后单击“运行”。

The Macro dialog box appears. Click the macro name and then click Run.

Running a Macro from VBA Editor

您可以按照如下方式从 VBA 编辑器运行宏 -

You can run a macro from the VBA editor as follows −

Click the Run tab on the Ribbon.
Select Run Sub/UserForm from the dropdown list.

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.

Open the macro-enabled workbook with your first macro.
Click the DEVELOPER tab on the Ribbon.
Click Visual Basic in the Code group.

VBA 编辑器窗口就会打开。

The VBA editor window opens.

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

You will observe the following in the Projects Explorer window −

Your macro enabled workbook – MyFirstMacro.xlsm appears as a VBA Project.
All the worksheets and the workbook appear as Microsoft Excel Objects under the project.
Module1 appears under Modules. Your macro code is located here.
Click Module1.
Click the View tab on the Ribbon.
Select Code from the dropdown list.

就会出现你的宏的代码。

The code of your macro appears.

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 −

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

Inserting a Command Button

Create a new worksheet.
Click in the new worksheet.
Click the DEVELOPER button on the Ribbon.
Click Insert in the Controls group.
Select the button icon from Form Controls.

Click in the worksheet where you want to place the command button.
The Assign Macro dialog box appears.

Visual Basic 编辑器将出现。

The Visual Basic editor appears.

您将观察到以下内容 −

You will observe the following −

A new module – Module2 is inserted in the Project Explorer.
Code window with title Module2 (Code) appears.
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.

就是这么简单！您的宏代码已准备就绪。正如您所知，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.

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

您可以看到该按钮已被选中。在消息框中单击“确定”。您将返回 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.

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

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

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

Excel Macros - Editing

您已在上一章节学习了如何在 VBA 编辑器中编写宏代码。您可以编辑宏代码、重命名宏以及删除宏。

You have learnt how to write macro code in VBA editor in the previous chapter. You can edit the macro code, rename a macro and delete a macro.

如果您精通 Excel VBA，为宏编写代码或修改代码是一项简单任务。您可以根据需要编辑宏代码。如果您只想在宏代码中进行少数简单更改，您甚至可以将宏代码从一个地方复制到另一个。

If you master Excel VBA, writing code or modifying code for a macro is a trivial task. You can edit the macro code however you want. If you want to make only few simple changes in the macro code, you can even copy macro code from one place to another.

Copying a Macro Code

您已在启用宏的工作簿 MyFirstMacro.xlsm 中创建了两个宏 - MyFirstMacro 和 Button1_Click。您通过记录步骤已创建第一个宏，并通过编写代码已创建第二个宏。您可以将代码从第一个宏复制到第二个宏。

You have created two macros – MyFirstMacro and Button1_Click in the macro enabled workbook MyFirstMacro.xlsm. You have created the first macro by recording the steps and the second macro by writing code. You can copy code from the first macro into the second macro.

Open the workbook MyFirstMacro.xlsm.
Click the Developer tab on the Ribbon.
Click Visual Basic. The Visual Basic editor opens.
Open the code for Module1 (MyFirstMacro macro code) and Module2 (Button1_Click () macro code).
Click the Window tab on the Ribbon.
Select Tile Horizontally from the dropdown list.

您可以在平铺窗口中查看两个宏的代码。

You can view the code of the two macros in the tiled windows.

Copy the MsgBox line in the Module2 code.
Paste it above that line.
Modify the string as − MsgBox “Hello World!”
Copy the following code from Module1.

将它粘贴到模块 2 代码中两行 MsgBox 代码行之间。

Paste it in the Module2 code in between the two MsgBox lines of code.

Click the Save icon to save the code.
Click the Button in the Excel sheet. A Message box appears with the message - Hello World! Click OK.

将会出现表格数据（根据您复制的代码），并会弹出一个包含信息“祝你好运”的对话框。

The table data appears (according to the code that you copied) and message box appears with message - Best Wishes to You!

您可以仅通过几个步骤修改代码。对于初学者而言，这是最简单的任务。

You can modify the code in just a few steps. This is the easiest task for a beginner.

Renaming a Macro

假设您想从包含命令按钮的工作表之外的任何工作表中运行已编辑的宏。您可以通过重命名宏，而不管是否点击按钮，来完成操作。

Suppose you want to run the edited macro from any worksheet other than the one that has the command button. You can do it irrespective of button click by renaming the macro.

Click the VIEW tab on the Ribbon.
Click Macros.
Select View Macros from the dropdown list.

出现宏对话框。

The Macro dialog box appears.

Click the macro name – Button1_Click.
Click the Edit button.

宏代码将出现在 VBA 编辑器中。

The macro code appears in the VBA editor.

将 Sub 行中出现的名称从 Button1_Click 更改为 RenamedMacro。保持 Sub 和括号不变。

Change the name that appears in the Sub line from Button1_Click to RenamedMacro. Leave Sub and parenthesis as they are.

打开宏对话框。宏名称将显示为您重命名的名称。

Open the Macro dialog box. The macro name appears as you renamed.

Click RenamedMacro.
Click the Run button. The macro runs. Now a button click is not necessary.

Deleting a Macro

您可以删除您记录或编码的宏。

You can delete a macro that you have recorded or coded.

Open the Macros dialog box.
Click the macro name.
Click the Delete button.

会显示 Delete 确认信息。

The Delete confirmation message appears.

如果您确定要删除宏，请点击 Yes 。否则，请点击否。

Click Yes if you are sure to delete the macro. Otherwise, click No.

Excel Macros - UserForms

有时，您可能需要反复收集他人的信息。Excel VBA 为您提供了一种处理此任务的简单方法 - UserForm 。与您填写的任何其他表单一样，用户窗体让您可以轻松了解要提供哪些信息。用户窗体非常友好，因为它提供的控件不言自明，必要时还附有其他说明。

At times, you might have to collect information repeatedly from others. Excel VBA provides you with an easy way of handling this task- UserForm. As any other form that you fill up, UserForm makes it simple to understand, what information is to be provided. UserForm is user friendly in the way that the controls provided are self-explanatory, accompanied by additional instructions where necessary.

用户窗体的主要优点是，您可以节省您花在信息填充方式和内容上的时间。

Major advantage of UserForm is that you can save on time that you spend on what and how the information is to be filled.

Creating a UserForm

要创建用户窗体，请按照以下步骤进行 −

To create a UserForm, proceed as follows −

Click the DEVELOPER tab on the Ribbon.
Click Visual Basic. A Visual Basic window for the workbook opens.
Click Insert,
Select UserForm from the dropdown list.

用户窗体出现在窗口右侧。

The UserForm appears on the right side of the window.

Understanding the UserForm

最大化 UserForm.xlsx – UserForm1 窗口。

Maximize the UserForm.xlsx – UserForm1 window.

你现在处于设计模式。您可以在用户窗体上插入控件，并为相应的操作编写代码。控件在工具箱中提供。用户窗体的属性在属性窗口中。UserForm1（用户窗体的标题）在项目资源管理器中的“窗体”下给出。

You are in the design mode now. You can insert controls on the UserForm and write code for the respective actions. The controls are available in the ToolBox. Properties of UserForm are in the Properties window. UserForm1 (caption of the UserForm) is given under Forms in the Projects Explorer.

Change the caption of the UserForm to Project Report – Daily in the properties window.
Change the name of the UserForm to ProjectReport.

这些更改反映在用户窗体、属性和项目资源管理器中。

The changes are reflected in the UserForm, properties and project explorer.

Controls in the ToolBox

用户窗体将具有不同的组件。当您单击任何组件时，系统会指示您提供什么信息以及如何提供信息，或者您会看到可供选择的选项。所有这些都是通过用户窗体的工具箱中的 ActiveX 控件提供的。

A UserForm will have different components. As and when you click on any of the components, either you will be provided with instructions on what and how the information is to be provided or you will be provided with options (choices) to select from. All these are provided by means of ActiveX controls in the ToolBox of the UserForm.

Excel 提供两种类型的控件——窗体控件和 ActiveX 控件。您需要了解这两种类型的控件之间的区别。

Excel provides two types of controls – Form controls and ActiveX controls. You need to understand the difference between these two types of controls.

Form controls

窗体控件是与较早版本的 Excel（从 Excel 5.0 版本开始）兼容的 Excel 原始控件。窗体控件还设计为在 XLM 宏表上使用。

Form controls are the Excel original controls that are compatible with earlier versions of Excel, starting with Excel version 5.0. Form controls are also designed for use on XLM macro sheets.

您可以使用窗体控件运行宏。您可以向控件分配现有宏，或编写或记录新宏。单击控件时，宏将运行。您已了解如何从窗体控件中插入命令按钮到工作表中来运行宏。然而，这些控件无法添加到用户窗体中。

You can run macros by using Form controls. You can assign an existing macro to a control, or write or record a new macro. When the control is clicked, the macro. You have already learnt how to insert a command button from Form controls in the worksheet to run a macro. However, these controls cannot be added to a UserForm.

ActiveX controls

ActiveX 控件可用于 VBA 用户窗体上。ActiveX 控件具有丰富的属性，您可以使用这些属性来自定义它们的外观、行为、字体和其他特性。

ActiveX controls can be used on VBA UserForms. ActiveX controls have extensive properties that you can use to customize their appearance, behavior, fonts and other characteristics.

您在用户窗体工具箱中拥有以下 ActiveX 控件 −

You have the following ActiveX controls in the UserForm ToolBox −

Pointer
Label
TextBox
ComboBox
ListBox
CheckBox
OptionButton
Frame
ToggleButton
CommandButton
TabStrip
MultiPage
ScrollBar
SpinButton
Image

除了这些控件外，Visual Basic 还为您提供了 MsgBox 函数，该函数可用于显示消息和/或提示用户采取操作。

In addition to these controls, Visual Basic provides you with MsgBox function that can be used to display messages and/or prompt the user for an action.

在接下来的几节中，您将了解这些控件和 MsgBox。然后，您将能够选择需要哪些控件来设计您的用户窗体。

In the next few sections, you will understand these controls and MsgBox. Then, you will be in a position to choose which of these controls are required to design your UserForm.

Label

您可以通过显示描述性文本（例如标题、说明和/或简要说明）来使用标签进行标识目的。

You can use Labels for identification purpose by displaying descriptive text, such as titles, captions and / or brief instructions.

Example

TextBox

您可以使用文本框（一个矩形框）来键入、查看或编辑文本。您还可以将文本框用作呈现只读信息的静态文本字段。

You can use a TextBox that is a rectangular box, to type, view or edit text. You can also use a TextBox as a static text field that presents read-only information.

Example

List Box

可以使用列表框来显示一个或多个用户可以选择文本项目列表。使用列表框显示数量或内容不同的众多选择。

You can use a List Box to display a list of one or more items of text from which a user can choose. Use a list box for displaying large numbers of choices that vary in number or content.

Insert a ListBox on the UserForm.
Click on the ListBox.
Type ProjectCodes for Name in the Properties window of the ListBox.

列表框有三种类型 −

There are three types of List Boxes −

Single-selection List box − A single-selection List Box enables only one choice. In this case, a list box resembles a group of option buttons, except that a list box can handle a large number of items more efficiently.
Multiple selection List Box − A multiple selection List Box enables either one choice or contiguous (adjacent) choices.
Extended-selection List Box − An extended-selection List Box enables one choice, contiguous choices and noncontiguous (or disjointed) choices.

可以在属性窗口中选择这些列表框类型之一。

You can select one of these types of List Boxes, from the Properties window.

Right click on the UserForm.
Select View Code from the dropdown list. The code window of UserForm opens.
Click Initialize in the top right box of the code window.
Type the following under Private Sub UserForm_Initialize().

ProjectCodes.List = Array ("Proj2016-1", "Proj2016-2", "Proj2016-3", "Proj20164", "Proj2016-5")

Click the Run tab on the Ribbon.
Select Run Sub/UserForm from the dropdown list.

接下来，你可以编写在列表中选择某项后执行操作的代码。否则，你可以仅仅显示选中的文本，例如在报表中填写项目代码的情况亦是如此。

Next, you can write code for actions on selecting an item in the list. Otherwise, you can just display the text that is selected, which is the case for filling the Project Code in the Report.

ComboBox

你可以使用 ComboBox ，它将文本框与列表框组合起来以创建下拉列表框。与列表框相比，组合框更紧凑，但要求用户单击向下箭头以显示项列表。使用组合框从列表中仅选择一项。

You can use ComboBox that combines a text box with a list box to create a dropdown list box. A combo box is more compact than a list box but requires the user to click the down arrow to display the list of items. Use a combo box to choose only one item from the list.

Insert a ComboBox on the UserForm.
Click the ComboBox.
Type ProjectCodes2 for Name in the Properties window of the ComboBox.

Right click on the UserForm.
Select View Code from the dropdown list.
The code window of UserForm opens.

按下方所示键入以下内容。

Type the following as shown below.

ProjectCodes2.List = Array ("Proj2016-1", "Proj2016-2", "Proj2016-3", "Proj20164", "Proj2016-5")

Click the Run tab on the Ribbon.
Select Run Sub/UserForm from the dropdown list.

单击向下箭头显示项目列表。

Click the down arrow to display the list of items.

单击所需的项目，例如 Project2016-5。选择框中将显示已选项。

Click on the required item, say, Project2016-5. The selected option will be displayed in the combo box.

CheckBox

您可以使用复选框选中单击框中显示的一个或多个选项。这些选项会有标签，您可以清晰地看到已选选项。

You can use check boxes to select one or more options that are displayed by clicking in the boxes. The options will have labels and you can clearly visualize what options are selected.

复选框可以有两种状态 −

A check box can have two states −

Selected (turned on), denoted by a tick mark in the box
Cleared (turned off), denoted by a clear box

在组合框中使用复选框选择选项可以节省空间。在此情况下，复选框还可以有第三个状态 −

You can use check boxes for selection of options in a combo box to save space. In such a case, the check box can have a third state also −

Mixed, meaning a combination of on and off states, denoted by a black dot in the box. This will be displayed to indicate multiple selections in the combo box with check boxes.
Insert check boxes in the UserForm as shown below.

Click the Run tab on the Ribbon.
Select Run Sub/UserForm from the dropdown list.
Click in the boxes for your selected options.

OptionButton

您可以使用选项按钮（也称为单选按钮），在有限的互斥选项集中进行单选。选项按钮通常包含在组框或框架中。

You can use an option button, also known as the radio button to make a single choice within a limited set of mutually exclusive choices. An option button is usually contained in a group box or a frame.

选项按钮用一个小圆圈表示。选项按钮可以有以下两种状态之一 −

An option button is represented by a small circle. An option button can have one of the following two states −

Selected (turned on), denoted by a dot in the circle
Cleared (turned off), denoted by a blank

Frame

您可以使用框架控件（也称为组框），将相关控件组合到一个可视化单元中。通常，选项按钮、复选框或密切相关的控件会被分组到一个框架控件中。

You can use a frame control, also referred to as a group box to group related controls into one visual unit. Typically, option buttons, check boxes or closely related contents are grouped in a frame control.

框架控件由一个带有可选标签的矩形对象表示。

A frame control is represented by a rectangular object with an optional label.

Insert a frame with caption “Choice”.
Insert two option buttons with captions “Yes” and “No” in the frame control. The options Yes and No are mutually exclusive.

Click the Run tab on the Ribbon.
Select Run Sub/UserForm from the dropdown list.
Click on your selected option.

ToggleButton

你可以使用一个切换按钮指示一个状态，比如是或否，或一个模式，比如开或关。这个按钮在被点击时会在启用和禁用状态之间切换。

You can use a toggle button to indicate a state, such as Yes or No, or a mode, such as on or off. The button alternates between an enabled and a disabled state when it is clicked.

如下图所示在 UserForm 中插入一个切换按钮 −

Insert a toggle button on UserForm as shown below −

Click the Run tab on the Ribbon.
Select Run Sub/UserForm from the dropdown list. The toggle button will be in enabled state by default.

点击切换按钮。切换按钮将被禁用。

Click the toggle button. The toggle button will be disabled.

如果你再次点击切换按钮，它将被启用。

If you click the toggle button again, it will be enabled.

CommandButton

你可以使用一个命令按钮运行一个宏，当用户点击它时执行一些操作。你已经学会如何在工作表中使用一个命令按钮来运行一个宏。

You can use a command button to run a macro that performs some actions when the user clicks on it. You have already learnt how to use a command button on a worksheet to run a macro.

命令按钮也被称为推送按钮。如下图所示在 UserForm 中插入一个命令按钮 −

Command button is also referred to as a push button. Insert a command button on the UserForm as shown below −

Right click on the command button.
Type the following code in the sub Commandbutton1_click ().

ProjectCodes2.DropDown

Click the Run tab on the Ribbon.
Select Run Sub/UserForm from the dropdown list.

点击命令按钮。组合框的下拉列表打开，因为这是你在代码中编写的操作。

Click the command button. The dropdown list of combo box opens, as it is the action that you have written in the code.

TabStrip

你可以在 UserForm 中插入一个类似于 Excel 标签页的标签页控件。

You can insert a tab strip that resembles Excel tabs on the UserForm.

ScrollBar

你可以使用滚动条通过点击滚动箭头或拖动滚动框来浏览一系列的值。

You can use a scroll bar to scroll through a range of values by clicking on the scroll arrows or by dragging the scroll box.

通过在所需位置绘制并调整滚动条的长度，在 UserForm 中插入一个滚动条。

Insert a scroll bar on the UserForm by drawing it at the required position and adjust the length of the scroll bar.

Right click on the scroll bar.
Select View Code from the dropdown list. The Code window opens.
Add the following line under sub ScrollBar1_Scroll().

TextBox2.Text = "Scrolling Values"

Click the Run tab on the Ribbon.
Select Run Sub/UserForm from the dropdown list.

拖动滚动框。文本 - 滚动值将显示在文本框中，就像你指定为滚动条滚动操作一样。

Drag the scroll box. The Text – Scrolling Values will be displayed in the text box as you specified it as the action for scroll bar scroll.

MsgBox ()

你可以在点击某物时使用 MsgBox() 函数显示一条消息。这可以是一条指导或一些信息、一个警告或一个错误警报。

You can use the MsgBox () function to display a message when you click on something. It can be a guideline or some information or a warning or an error alert.

例如，当开始滚动滚动框时，你可以显示一条消息来提示值正在滚动。

For example, you can display a message that values are being scrolled when you start scrolling the scroll box.

Message Box Icon Displays

你可以使用消息框图标显示来描绘具体的消息。你有各种消息框图标，以满足你的目的 −

You can use message-box icon displays that portray the specific message. You have the multiple message box icons to suit your purpose −

Type the following code under ScrollBar1_scroll.

MsgBox "Select Ok or Cancel", vbOKCancel, "OK  - Cancel Message"
MsgBox "It's an Error!", vbCritical, "Run time result"
MsgBox "Why this value", vbQuestion, "Run time result"
MsgBox "Value Been for a Long Time", vbInformation, "Run time result"
MsgBox "Oh Is it so", vbExclamation, "Run time result"

Click the Run tab on the Ribbon.
Select Run Sub/UserForm from the dropdown list.
Drag the scroll box.

你将依次收到以下消息框。

You will get the following message boxes successively.

Designing UserForm

现在，你已了解可以在 UserForm 上使用不同的控件。选择控件，在需要时对它们进行分组，并按照一定的合理顺序排列在 UserForm 上。将需要的操作作为代码编写，对应于各个控件。

Now, you have an understanding of the different controls that you can use on a UserForm. Select the controls, group them if required and arrange them on the UserForm as per some meaningful sequence. Write the required actions as code corresponding to the respective controls.

有关 UserForm 的示例，请参阅本教程库中的 VBA 教程。

Refer to the VBA tutorial in this tutorials library for an example of UserForm.

Excel Macros - Debugging a Code

您已经了解到宏存储为 Excel 中的 VBA 代码。您还了解到可以在 VBA 编辑器中直接编写代码来创建宏。但是，与任何代码一样，即使是宏代码也可能存在缺陷，而且宏可能无法按预期运行。

You have learnt that the macro is stored as VBA code in Excel. You have also learnt that you can directly write code to create a macro in VBA editor. However, as with the case with any code, even the macro code can have defects and the macro may not run as you expected.

这需要检查代码以查找缺陷并修复它们。在软件开发中，用于这种活动的术语是调试。

This requires examining the code to find the defects and correct them. The term that is used for this activity in software development is debugging.

VBA Debugging

VBA 编辑器允许您暂停代码的执行并执行任何必需的调试任务。以下是您可以执行的一些调试任务。

VBA editor allows you to pause the execution of the code and perform any required debug task. Following are some of the debugging tasks that you can do.

Stepping Through Code
Using Breakpoints
Backing Up or Moving Forward in Code
Not Stepping Through Each Line of Code
Querying Anything While Stepping Through Code
Halting the Execution

这些只是您可能在 VBA 调试环境中执行的某些任务。

These are just some of the tasks that you might perform in VBA’s debugging environment.

Stepping Through the Code

对于调试，您首先要做的就是逐步执行代码。如果您知道哪一部分代码可能导致缺陷，则可以跳到代码的那一行。否则，您可以逐行执行代码，在代码中向前或向后移动。

The first thing that you have to do for debugging is to step through the code while executing it. If you have an idea of which part of the code is probably producing the defect, you can jump to that line of the code. Otherwise, you can execute the code line by line, backing up or moving forward in the code.

您可以从工作簿中的宏对话框或 VBA 编辑器本身进入代码。

You can step into the code either from Macro dialog box in your workbook or from the VBA editor itself.

Stepping into the code from the workbook

若要从工作簿进入代码，请执行以下操作 −

To step into the code from the workbook, do the following −

Click the VIEW tab on the Ribbon.
Click Macros.
Select View Macros from the dropdown list.

出现宏对话框。

The Macro dialog box appears.

Click the macro name.
Click the Step into button.

VBA 编辑器打开，宏代码出现在代码窗口中。宏代码中的第一行将以黄色突出显示。

VBA editor opens and the macro code appears in the code window. The first line in the macro code will be highlighted in yellow color.

Stepping into the code from the VBA editor

若要从 VBA 编辑器进入代码，请执行以下操作 −

To step into the code from the VBA editor, do the following −

Click the DEVELOPER tab on the Ribbon.
Click Visual Basic. The VBA editor opens.
Click the module that contains the macro code.

宏代码出现在代码窗口中。

The macro code appears in the code window.

Click the Debug tab on the Ribbon.
Select Step into from the dropdown list.

宏代码中的第一行将被高亮。代码处于调试模式，并且“调试”下拉列表中的选项将变为活动状态。

The first line in the macro code will be highlighted. The code is in the debugging mode and the options in the Debug dropdown list will become active.

Backing Up or Moving Forward in the Code

您可以通过选择“单步执行”或“步出”在代码中向前或向后移动。

You can move forward or backward in the code by selecting Step Over or Step Out.

Not Stepping Through Each Line of Code

如果您识别到需要讨论的代码的潜在部分，则可以选择“运行到光标”以避免逐行执行代码。

You can avoid stepping through each line code, if you identify a potential part of the code that needs to be discussed, by selecting Run to Cursor.

Using Breakpoints

或者，您可以在代码的特定行设置断点并执行代码，在每个断点处观察结果。您可以根据需要切换断点并清除所有断点。

Alternatively, you can set breakpoints at specific lines of code and execute the code, observing the results at each breakpoint. You can toggle a breakpoint and clear all breakpoints if and when required.

Using Watch

在调试过程中，您可以添加一个监视器来计算一个表达式，并在变量达到特定值时停止执行。这意味着您配置一个监视器表达式，它将被监视，直到为真，然后宏将停止并让您进入断点模式。VBA 为您提供了多种监视器类型以供选择，以便实现您的目标。

You can add a watch while debugging, to evaluate an expression and stop the execution when a variable attains a specific value. This means that you configure a watch expression, which will be monitored until it is true and then the macro will halt and leave you in break mode. VBA provides you with several watch types to select from, in order to accomplish what you are looking for.

Halting the Execution

在调试过程中，在任何时间点，如果您发现了问题所在，您可以停止执行以进一步破译。

During debugging, at any point of time, if you have found a clue on what is going wrong, you can halt the execution to decipher further.

如果您是一位经验丰富的开发人员，则调试术语对您来说并不陌生，并且 VBA 编辑器调试选项使您的生活变得轻松。即使没有经验，如果您学习过 VBA 并理解代码，掌握这项技能也不会花费太多时间。

If you are an experienced developer, the debugging terminology is familiar to you and VBA editor debugging options make your life simple. Even otherwise, it will not take much time to master this skill if you have learnt VBA and understand the code.

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.