vba 宏录制_Excel VBA编辑您录制的宏

vba 宏录制

Last week was Recruit a New VBA Programmer Week, so we pitched in by recording an Excel macro, and running it. I've updated that article, by adding a video, so you can see all the gory details, step-by-step. This week we'll look at the Excel VBA code that the Macro Recorder created, and make a minor change, to edit your recorded macro.

上周是“ 招募新的VBA程序员周” ，因此我们通过记录并运行Excel宏来进行学习。 我已经通过添加视频更新了该文章，因此您可以逐步查看所有详细信息。 本周，我们将查看宏记录器创建的Excel VBA代码，并进行较小的更改以编辑您的记录宏。

打开宏工作簿 (Open the Macro Workbook)

When you recorded the macro, you selected a workbook to store the macro. We'll open that workbook, and find the Excel VBA code.

录制宏时，您选择了一个工作簿来存储宏。 我们将打开该工作簿，并找到Excel VBA代码。

1. In Excel, open the workbook where you stored the Excel VBA code. If you stored the macro in the Personal Macro workbook, it should already be open, and hidden from view.
   在Excel中，打开存储Excel VBA代码的工作簿。 如果您将宏存储在“个人宏”工作簿中，则该宏应该已经打开并从视图中隐藏了。
2. If a security warning appears at the top of the worksheet, click the Options button.
   如果工作表顶部显示安全警告，请单击“选项”按钮。
3. 
4. Then click Enable This Content, to allow the workbook's macros to run.
   然后单击“启用此内容”，以允许工作簿的宏运行。

查找Excel VBA代码 (Find the Excel VBA Code)

Next, we'll go to the recorded code.

接下来，我们将转到记录的代码。

1. On the Ribbon, click the Developer tab, then click Macros.
   在功能区上，单击“开发人员”选项卡，然后单击“宏”。
2. In the Macro dialog box, click on the name of your macro.
   在“宏”对话框中，单击您的宏的名称。
3. • 
4. The Excel Visual Basic Editor (VBE) opens, showing the code that you recorded. Your code might look different from the sample shown below.
   将打开Excel Visual Basic编辑器(VBE)，显示您记录的代码。 您的代码可能与以下示例不同。

Excel Visual Basic编辑器 (The Excel Visual Basic Editor)

• At the right in the VBE is the Code Window. The cursor is flashing near the top of the code for your recorded macro.

  VBE的右侧是“ 代码窗口” 。 光标在所录制的宏的代码顶部附近闪烁。

• At the left, you should see a list of files, in the Project Explorer Window.

  在左侧，您应该在Project Explorer窗口中看到文件列表。

In the Code Window, you can edit the text, just as you would in Microsoft Word, or Notepad.

在“代码窗口”中，可以像在Microsoft Word或记事本中一样编辑文本。

In the Project Explorer Window, you can select an object and see any code that it contains. In the screen shot above, Module 1 is highlighted, in the VBA Project for our workbook, named MacroCopyProduct.xlsm.

在项目资源管理器窗口中，您可以选择一个对象并查看其包含的任何代码。 在上面的屏幕快照中，在工作簿的VBA项目中，名为MacroCopyProduct.xlsm的模块1被突出显示。

检查记录的代码 (Check the Recorded Code)

The Excel Macro Recorder created some code, while we performed the steps in our process. In my example, these were the steps:

在执行过程中的步骤的同时，Excel Macro Recorder创建了一些代码。 在我的示例中，这些步骤是：

1. Open the orders file, named StationeryShort2007.xlsx
   打开订单文件，名为StationeryShort2007.xlsx
2. Filter the list on the Data sheet, to show only the Binder orders
   筛选数据表上的列表，以仅显示活页夹订单
3. Copy the Binder orders
   复制活页夹订单
4. Create a new workbook
   创建一个新的工作簿
5. Paste the Binder orders into the new workbook.
   将活页夹订单粘贴到新的工作簿中。

Here's how those steps look, when written in Excel VBA by the Macro Recorder.

通过宏记录器以Excel VBA编写时，这些步骤的外观如下。

更改记录的代码 (Change the Recorded Code)

The Excel Macro Recorder is a great tool for getting started with Excel VBA. Sometimes you can leave the code exactly as is, and it will run fine every time you need it. Most times though, the recorded code needs to be modified, and we'll start with a simple change.

Excel Macro Recorder是入门Excel VBA的绝佳工具。 有时您可以完全照原样保留代码，并且每次需要时它都能正常运行。 不过，大多数情况下，需要修改记录的代码，我们将从简单的更改开始。

When recording the code, I selected a specific range, "A1:J50", which is used in two lines of the code. If new rows of data are added, the code won't include them.

记录代码时，我选择了一个特定范围“ A1：J50”，该范围在代码的两行中使用。 如果添加了新的数据行，则代码将不包括它们。

To accommodate for an increase in rows, we could change the 50 to 500. Then, if rows are added, they'll be included in the filter. There are more sophisticated ways to deal with a range that changes size, but this works for now.

为了适应行数的增加，我们可以将50更改为500。然后，如果添加了行，它们将被包含在过滤器中。 有更复杂的方法来处理可更改大小的范围，但这目前仍然有效。

测试变更 (Test the Changes)

After you have changed the recorded code, close the VBE.

更改录制的代码后，关闭VBE。

Then, run the macro again, to test the changed code.

然后，再次运行宏，以测试更改的代码。

1. On the Ribbon, click the Developer tab, then click Macros.
   在功能区上，单击“开发人员”选项卡，然后单击“宏”。
2. In the Macro dialog box, click on the name of your macro.
   在“宏”对话框中，单击您的宏的名称。
3. At the right of the dialog box, click Run.
   在对话框的右侧，单击“运行”。

If the revised macro worked well, you can save the workbook that stores the macro. _______________

如果修改后的宏效果很好，则可以保存存储宏的工作簿。 _______________

翻译自: https://contexturesblog.com/archives/2009/11/27/excel-vba-edit-your-recorded-macro/

vba 宏录制