Excel Macros 简明教程

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 −

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 −

  1. Click the VIEW tab on the Ribbon.

  2. Click Macros in the Macros group.

  3. Select Record Macro from the dropdown list.

record

[ Record Macro 对话框出现。

The Record Macro dialog box appears.

  1. Type MyFirstMacro in the Macro name box.

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

record macro

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

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

  1. Click in the cell B2.

  2. Create the table.

  3. Click in a different cell in the worksheet.

  4. Click the VIEW tab on the Ribbon.

  5. Click Macros.

  6. Select Stop Recording from the dropdown list.

stop recording

宏录制完成。

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 −

  1. Click on a new worksheet.

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

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

  1. Click the VIEW tab on the Ribbon.

  2. Click Macros.

  3. Select View Macros from the dropdown list.

view

出现宏对话框。

The Macro dialog box appears.

macro dialog box

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

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

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

  2. Click the Run button. The same table that you have created while recording the macro will appear in just a split of a second.

macros list

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

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

  1. Though the active cell before running the macro was A1, the table is placed in the cell B2 as you have recorded.

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

  1. Storing a macro

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

  1. This Workbook.

  2. New Workbook.

  3. Personal Macro Workbook

store macro

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.

this workbook

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.

  1. Click No in the warning message box.

  2. Select Excel Macro-Enabled Workbook (*.xlsm) in the Save as type.

  3. Click Save.

save file

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

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