The first step in the creation of a macro is to record one. When a macro is being recorded, Excel will store any action in the Excel window in the macro that you are creating. When you have completed all the actions that need to be recorded, you will stop recording. Now you can run this macro to repeat the recorded actions as necessary.
To record a macro, click View → Macros → Record Macro:
The Record Macro dialog should open:
In this dialog, you can choose a name for the macro that you will be recording, where this new macro will be stored, and add a description of its actions to it. When you are ready to record a macro select the OK button. Once the OK button has been selected Excel will then record all of your actions inside the Excel window.
Once you have completed all of the actions that you need included in the macro, select the Macros drop-down command again, and click Stop Recording:
At this point the new macro will be complete.
Once a macro has been recorded, you can edit it easily in Excel. First, click the Macros drop-down command, and then click View Macros:
Then the Macro dialog should open:
Select the macro that you would like to edit from the list and click the Edit command on the right:
Then the selected macro will open in Microsoft Visual Basic for Applications. We will cover how to work with this application later on in this section.
To change the name of a macro, use the following procedure.
The Visual Basic Editor opens with your macro. The macro is a sub-routine in the programming. We'll make a copy of our FillMonths() macro, or subroutine, and change both of the new macros slightly.
First, we'll make a copy of the macro.
Now we are going to change the name of the first macro.
At the top of the first subroutine, enter an R next to FillMonths, to represent, fill months row. Remember that macro names cannot contain spaces. Now we are going to change the name of the second macro. Look for the line that divides the subroutines.
Enter a C next to FillMonths, to represent fill months column. Now we are going to change the second macro so that it fills down instead of across and it uses the number representation of the months instead of the names.
ActiveCell.FormulaR1C1 = “Jan” The result should be
ActiveCell.FormulaR1C1 = 1
Once a macro has been recorded, you can run it at any time by clicking the Macros command directly on the View tab:
Selecting this will the launch the Macro dialog, in which you can choose the macro that you would like to run. After you have chosen a macro, click the Run button:
The selected macro will then complete the tasks that you have previously recorded. As you can see, this can greatly improve efficiency when working with long, repetitive tasks in Excel. With one click you can complete dozens of steps in just a few seconds!
To assign a new macro to a command key, use the following procedure.
You'll need to create a Custom Group on the Ribbon before you can assign a macro to a Ribbon tab.
Now add the macro to the group.
Now look at the selected Ribbon and see the macro command that you added.
To assign a macro to a graphical object, use the following procedure.
Now try running the macro by clicking on the object.
You can format the shape in any way desired.
To create an Auto_Open macro, use the following procedure.
To test out your auto macro, close the workbook and reopen it. The macro is performed as soon as you open the workbook.
Recording an Auto_Open macro has the following limitations:
To start a workbook without running an Auto_Open macro, hold down the SHIFT key when you start Excel.
See all Seven Institute Excel training Options