Watch this video to see how to edit a macro in a special text editor called the Visual Basic Editor. Visual Basic is the programming language that macros are recorded in.
First, show the Developer tab
The DEVELOPER tab, on the ribbon, is where all of the macro commands are.
-
To have it appear on the ribbon, click FILE > Options > Customize Ribbon, check Developer, and click OK.
-
On the DEVELOPER tab, in the Code group, are the Record Macro button and some other buttons that will come in handy.
Create a macro using Visual Basic
-
Click DEVELOPER > Visual Basic.
-
If needed, in the Visual Basic Editor, on the Insert menu, click Module.
Note: Modules are automatically created for all sheets in the workbook.
-
In the code window of the module, type or copy the macro code that you want to use.
-
To run the macro from the module window, press F5.
-
In the Visual Basic Editor, on the File menu, click Close and Return to Microsoft Excel when you finish writing the macro.
Edit the macro
-
On the DEVELOPER tab, in the Code group, click Macros.
-
In the Macro name box, click the macro that you want to edit.
-
Click Edit. The Visual Basic Editor appears.
Want more?
In the first video, we saw how to record a macro, and how and why to turn on Use Relative References, so the macro behaves the way we want.
Let’s pick up where we left off with our macro: "FillDaysRelative", which enters the days of the week in cells across a row.
We’ll change it, so it enters the days a numbers instead of names, and arrange them vertically in a column, instead of horizontally in a row.
We could record a new macro, or edit it manually.
Let’s edit the macro to see what its instructions look like.
Click the Macros button. Here is the last macro I recorded, "FillDaysRelative". Select it, and then click Edit.
This opens the macro in a special text editor called the Visual Basic Editor.
Visual Basic is the programming language that macros are recorded in.
You can also create macros from scratch in this editor.
Here’s the macro. It’s called a Sub or Sub Routine and its name, "FillDaysRelative", is followed by a pair of parenthesis.
I’ll select all of this text, starting with Sub and ending with End Sub, and copy it by pressing Ctrl+C.
I’ll click here at the end, press Enter, and then Paste what I just copied.
Let’s rename the original macro "FillDaysRelativeR" for rows, and rename the copy "FillDaysRelativeC" for columns, since these two macros can’t have the same name.
I am going to change the instructions in this copy of the macro "FillDaysRelativeC".
The first thing we need to change is the first instruction; ActiveCell.FormulaR1C1 = “Sunday”.
We’ll change the day name to the number 1.
The next thing we’ll do is in the AutoFill destination; change A1 through G1 which is going across the row, to A1 through A7, which goes down the A column.
Change it in two places: here and also here.
Those are just a couple of minor changes, but they’ll change the behavior of the macro.
I’ll close the Visual Basic Editor by clicking this X in the red box, and that’ll save my changes.
And now, if I click Macros, I have three macros and here’s "FillDaysRelativeC", which is the new macro that will behave a little differently.
I’ll run it and you can see that instead of entering "Sunday", "Monday", and so on, across row 1, it enters the numbers 1 through 7 down column A.
All that, just from a couple of minor changes that we made in the macro editor.
In the next video, we’ll save your macro to your personal Excel workbook so that you can use it any time with any workbook on your computer.
Need more help?
Want more options?
Explore subscription benefits, browse training courses, learn how to secure your device, and more.
Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.
