Excel VBA Edit Your Recorded Macro

Edit Your Recorded Macro

Last week was Recruit a New VBA Programmer Week on the Daily Dose of Excel blog, 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.

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.

Security Warning 01

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

Excel Macro Edit 01

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

Excel VBE 01

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 that I followed:

  1. Open the orders file, named 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 recorded code

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.

Excel VBA code edited

Test the Changes

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

Excel VBE Close

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

6 thoughts on “Excel VBA Edit Your Recorded Macro”

  1. […] the Excel VBA Edit a Recorded Macro article, you saw the steps for making changes to an Excel macro that you had […]

  2. Sub help()
    Dim i As Integer, j As Integer, k As Integer, l As String, m As Integer, n As String, o As Long, p As String, q As String
    For i = 4 To 36
    Cells(3, i).Value = j
    For k = 3 To 4100
    l = ThisWorkbook.Sheets(1).Cells(7, k)
    If (l = k) Then
    For m = 6 To 66
    n = Cells(3, m)
    For o = 4 To 4100
    p = ThisWorkbook.Sheets(1).cell(9, o)
    If (p = n) Then
    Cells(i, m).Value = “ok”
    Else
    Cells(i, m).Value = “”
    Next o
    Next m
    Next k
    Next i
    End Sub
    when i run this code it shows next without for please help.

Leave a Reply to Contextures Blog » Excel VBA: Show a Message to Users Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.