Excel VBA Edit Your Recorded Macro

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 the code.

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.

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.

If a security warning appears at the top of the worksheet, click the Options button.

Then click Enable This Content, to allow the workbook’s macros to run.

Find the Excel VBA Code

Next, we’ll go to the recorded code.

On the Ribbon, click the Developer tab, then click Macros.

In the Macro dialog box, click on the name of your macro.

At the right of the dialog box, click Edit.

The Excel Visual Basic Editor (VBE) opens, showing the code that you recorded. Your code might look different from the sample shown below.

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.

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

In the Code Window, you can edit the text, just as you would in Microsoft Word, or Notepad.
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.

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:

Open the orders file, named StationeryShort2007.xlsx

Filter the list on the Data sheet, to show only the Binder orders

Copy the Binder orders

Create a new workbook

Paste the Binder orders into the new workbook.

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

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

Test the Changes

After you have changed the recorded code, close the VBE.
Then, run the macro again, to test the changed code.

On the Ribbon, click the Developer tab, then click Macros.

In the Macro dialog box, click on the name of your macro.

At the right of the dialog box, click Run.

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