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.
Performing Actions
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!
