Excel Macros 简明教程

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.

  1. Open the workbook MyFirstMacro.xlsm.

  2. Click the Developer tab on the Ribbon.

  3. Click Visual Basic. The Visual Basic editor opens.

  4. Open the code for Module1 (MyFirstMacro macro code) and Module2 (Button1_Click () macro code).

  5. Click the Window tab on the Ribbon.

  6. Select Tile Horizontally from the dropdown list.

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

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

copying
  1. Copy the MsgBox line in the Module2 code.

  2. Paste it above that line.

  3. Modify the string as − MsgBox “Hello World!”

  4. Copy the following code from Module1.

copy code

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

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

msgbox
  1. Click the Save icon to save the code.

  2. Click the Button in the Excel sheet. A Message box appears with the message - Hello World! Click OK.

hello world

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

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

table data

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

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.

  1. Click the VIEW tab on the Ribbon.

  2. Click Macros.

  3. Select View Macros from the dropdown list.

出现宏对话框。

The Macro dialog box appears.

  1. Click the macro name – Button1_Click.

  2. Click the Edit button.

renaming macro

宏代码将出现在 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.

renamedmacro

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

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

open macro
  1. Click RenamedMacro.

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

  1. Open the Macros dialog box.

  2. Click the macro name.

  3. Click the Delete button.

deleting macro

会显示 Delete 确认信息。

The Delete confirmation message appears.

delete confirmation

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

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