When you're trying to work out how to use a new technique in VBA, recording a macro is a great way to start getting help. Recording a macro allows you to manually perform actions in Excel while the VBE automatically writes the code required to repeat those actions. You can then use this code as a starting point for getting more help, or just copy and paste it into another procedure!
You don't need any files for this page.
You can click here to download a file containing the sample code.
To get started, create a new workbook in Excel. You don't need to open the VBE just yet - in this part of the lesson we're going to let the VBE write the code for us!
Beginning a Recording
You can start recording a macro in a couple of different ways. One method is to choose Developer | Record Macro from the Excel ribbon:
Choose this option on the Developer tab of the ribbon.
You can also begin recording by clicking a button in the bottom left corner of the Excel window:
Click this button to begin recording a macro.
After choosing either of these options you'll be presented with a dialog box which allows you to configure the macro:
You can change the name of the macro and provide a keyboard shortcut to run it later. We won't bother doing either of those things. We will make sure that we store the macro in This Workbook, as shown.
The dialog box provides several choices on where to store the macro, as described in the table below:
|Store macro in||Description|
|This Workbook||Inserts a module into the VBA project attached to the current workbook.|
|New Workbook||Creates a new workbook and inserts a module into the VBA project attached to it.|
|Personal Macro Workbook||Inserts a module into the Personal Macro Workbook. Creates the Personal Macro Workbook if it doesn't exist.|
The Personal Macro Workbook is automatically created the first time you store a recorded macro in it. When it exists, this workbook will open automatically each time you open Excel. This means that you will always have access to any procedures stored in it.
When you've configured the macro, click OK on the dialog box to begin recording.
Once you've started a recording you can work in Excel as normal. Each time you perform an action, the VBE will write the appropriate VBA instruction. We'll keep things simple for this example and start by typing our name into cell A1:
Type your name into cell A1 and press Enter.
Next, enter today's date into cell A2:
Enter the current date and press Enter.
Now, click on cell A2:
Select the cell containing the date.
Next, we'll change the date format of the selected cell. There are several ways to do this but here's the approach we'll take:
In the Home tab of the Excel ribbon, click this button in the lower right corner of the Number group.
On the dialog box which appears, select the Number tab and choose to create a Custom format and enter the format code in the text box shown below:
In the Type text box, enter the format dd mmm yyyy and then click OK.
Now select cells A1 and A2:
Select both cells.
Apply a fill colour:
Here we're choosing one of the Standard Colors from the Fill Color tool on the Home tab of the ribbon.
Finally, change the font colour of the selected cells:
Here we're choosing one of the Theme Colors from the Font Color tool on the Home tab of the ribbon.
At this point your workbook should resemble the example shown below:
We haven't done much, but it's enough to create some sample code to look at.
Stopping a Recording
When you've finished performing actions in Excel, you can stop recording in a couple of different ways:
You can choose Developer | Stop Recording from the Excel ribbon.
You can also use the tool in the bottom left corner of the Excel window:
Click this tool to stop recording a macro.
Seeing the Code
When you've stopped recording, you can open the VBE to look at the code that's been generated:
Double-click the module in the Project Explorer to open it and see the code that's been written for you.
It can be helpful to tidy the code to make it more readable. Here's the same procedure with a few extra blank lines and comments:
This makes the macro a little easier to read.
Getting More Information
If you see some code that you don't recognise and you want to find more information, you can use the techniques covered in part 1.4.4 Getting Help. For example, to discover what on earth the TintAndShade property does:
Click on the TintAndShade keyword so that the flashing text cursor is in contact with the word.
You can then press F1 to jump to the online help page which describes what the property does:
Pressing F1 takes you to an online help page which describes the selected keyword.
This technique doesn't work with every keyword. You can use the Object Browser (press F2) to look up the keywords manually.
Removing Unnecessary Code
You may be surprised by how much code is generated by performing simple actions in Excel. For example, the last two things we did were to change the fill and font colours of the cells. This generated all of the code shown below:
The vast majority of this code is not needed to change the fill and font colours of the selected cells.
You can edit the code to remove the parts that aren't needed. The example below shows a stripped down version of the above code:
This is all that's required to change the fill and font colours.
If you aren't sure whether a bit of code is necessary, try commenting it out rather than deleting it. That way, you can easily get the code back!
You can record almost any action using the macro-recording feature. Try recording more complex examples such as creating charts or building pivot tables to see what's possible. You may not understand all of the generated code immediately, but you can use the help features to learn more about how it works. The key is to experiment!