Excel VBA Edit and Test a Macro

In the Excel VBA Edit a Recorded Macro article, you saw the steps for making changes to an Excel macro that you had recorded.

The original range was 50 rows, and I increased both references, to make them each 500 rows.

Video: Edit & Test Excel Macro

I’ve uploaded a new video that shows the steps for editing the Excel macro, and stepping through the macro code by using the F8 key.

The full transcript is below the video

Video Transcript

I recorded a macro in Excel and now we’d like to make a little change to that macro and test it.

Here’s the workbook where I store the macro

And to get started, there’s a security warning here.

I’m going to click Options and click, Enable this content, and OK

Now the macros will run.

See Macro Code

To see that macro, I’ll go to the Developer tab, click Macros

Select my macro in the list, and click Edit

That opens the Visual Basic Editor, and I can see all the workbooks that are open.

Most of these are hidden and just working in the background, but here’s the workbook where I recorded and stored the code.

And those are the sheets, and here’s the module where the code is stored

See Recorded Macro Steps

In this macro, I can see all the steps that were recorded.

• It opens a workbook
• Goes to a sheet
• Range A1 on that sheet
• Turns on an AutoFilter
• Filters for Binders
• Selects range A1 to J50
• Copies it
• Adds a new workbook
• And pastes what we copied

The change I want to make is just making this a bit bigger range.

• Right now, it’s going to row 50, which is where the orders stop now
• I know that I’ll be adding more orders every month, so I’m going to change the 50 to 500

Change the Code and Save

I’ll change it there, and in the next line of code, that also had a 50

I’ve made the change, and to save that, I’ll go up to the toolbar, and click the Save button

Test Revised Macro

Now, to test this revised macro:

• I’m going to just click somewhere between the Sub, and the End Sub row
• I’ll click here, and on the toolbar, I’ll click Debug
• There’s a Step Into command, and the keyboard shortcut is F8
• I’ll click Step Into, and it highlights the first row in the macro
• I’ll press F8 on the keyboard, and that takes me down to the first line of code, which is Workbooks.Open

It’s highlighting the row in the code that it’s about to run

• When I press F8, it opens that workbook, and moves down to the next line of code

Here’s the workbook in the background now

• And it will go to the Data sheet
• I press F8, and it was already on the Data sheet, so nothing happened
• Then it’ll go to range A1, and turn on the AutoFilter
• I press F8 again, and you can see the filter arrow there now

The next line of code tells it to filter for Binder orders

• So F8, and now you can see the row numbers are blue, and some rows are hidden, so the filter has been applied
• Now it will select A1 to J500, when I press F8
• Then it copies, and F8 again, to add a new workbook

And you can see the blank sheet in the background now

• And now it will paste when, I press F8
• And I’m at the last row here, so when I press F8 again, it’ll just take me out of the macro

We have made a revision to the macro, we saved it, tested the macro, and it works fine.

We can just close our macro workbook now, and know that our revised macro works fine

____________