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.
- 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.
- NOTE: 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 that I followed:
- 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.
_______________
[…] Excel VBA: Edit Your Recorded Macro […]
[…] the Excel VBA Edit a Recorded Macro article, you saw the steps for making changes to an Excel macro that you had […]
June 21, 2013
Dear Sirs,
Your tutorial is very fine. I like it very much.
Regards,
Kingston.
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.
You need an End If after this line: Cells(i, m).Value = “”