Excel Macros 简明教程

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 −

  1. Running a Macro from the View Tab

  2. Running a Macro by pressing Ctrl plus a shortcut key

  3. Running a Macro by clicking a button on the Quick Access Toolbar

  4. Running a Macro by clicking a button in a Custom Group on the Ribbon

  5. Running a Macro by clicking on a Graphic Object

  6. Running a Macro from Developer Tab

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

  1. Click the VIEW tab on the Ribbon.

  2. Click Macros.

  3. Select View Macros from the dropdown list.

active cell

出现宏对话框。

The Macro dialog box appears.

  1. Click the macro name.

  2. Click the Run button.

dialog box

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

  1. Click the VIEW tab.

  2. Click Macros.

  3. Select Record Macro from the dropdown list.

“创建宏”对话框出现。

The Create Macro dialog box appears.

  1. Type a macro name

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

adding

Adding a Shortcut Key in Macro Options

  1. Click the VIEW tab.

  2. Click Macros.

  3. Select View Macros from the dropdown list.

出现宏对话框。

The Macro dialog box appears.

  1. Select the macro name.

  2. Click the Options button.

shortcut key

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

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

type a letter

要使用快捷键运行宏,请同时按 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 −

  1. Right click on the Quick Access Toolbar.

  2. Select Customize Quick Access Toolbar from the dropdown list.

quick access

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

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

commands

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

A list of macros appears under Macros.

  1. Click PERSONAL.XLSB!MyMacro.

  2. Click the Add button.

list

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

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

要更改宏按钮图像,请执行以下操作:

To change the macro button image, proceed as follows −

  1. Click the macro name in the right box.

  2. Click the Modify button.

modify

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

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

icon

将指针放在快速访问工具栏上的按钮图像时显示的“显示名称”修改为一个有意义的名称,例如本示例中的“运行 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.

mymacro

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

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

symbol

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

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

pointer

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

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.

  1. Right click on the Ribbon.

  2. Select Customize the Ribbon from the dropdown list.

custom group

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

The Excel Options dialog box appears.

  1. Select Main Tabs under Customize the Ribbon.

  2. Click New Tab.

excel option

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

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

  1. Click New Tab (Custom).

  2. Click the New Group button.

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

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

  1. Click New Tab (Custom).

  2. Click the Rename button.

custom

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

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.

rename

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

  1. Click New Group (Custom).

  2. Click the Rename button.

new group

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

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

display name

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

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

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

Click Macros in the left pane under Choose commands from.

commands from
  1. Select your macro name, say – MyFirstMacro from the macros list.

  2. Click the Add button.

macro list

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

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

personal macros
  1. Click My Macros (Custom) in the list.

  2. Click the arrows to move the tab up or down.

arrows

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

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

position

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

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.

click tab

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.

  1. Click the Developer tab on the Ribbon.

  2. Click Macros.

developer tab

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

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

click run

Running a Macro from VBA Editor

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

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

  1. Click the Run tab on the Ribbon.

  2. Select Run Sub/UserForm from the dropdown list.

userform