Tutorial 6: Recording and Storing a Simple Excel Macro

Tutorial 6: Recording and storing a simple Excel macro

This tutorial takes you through basic macro operations such as recording and viewing a macro, assigning a shortcut key, and storing macros in different locations.

Step 1: Start recording

To start recording a macro, from the menu, select

Tools/Macro/Record New Macro…

This opens the Record Macro dialog box pictured below.

Record Macro dialog box

To give the macro a name other than the one Microsoft Excel suggests type a name in the Macro Name box. Use meaningful names for macros, as you may finish up with many macros and they need to be recognisable. It is also advisable to add a description.

The name can contain letters, numbers and underscores, but it must begin with a letter. The name can’t contain spaces or punctuation marks.

Step 2: Assign a shortcut key

In the Shortcut Key box, you can:

type a letter or,
press Shift and a letter.

Be careful not to use a shortcut key that has already been assigned, such as Ctrl + S (Save).

One way of avoiding this problem is to use ALT + key rather than CTRL + key.

Note that Excel does not warn you that a combination of keystrokes has been assigned to another command or macro in the way that Microsoft Word does.

Step 3: Decide where to store the macro

The choices are:

Personal Macro Workbook (Macros stored here are available to all workbooks on that machine. This is equivalent to Normal.dot in Microsoft Word)
New Workbook
This Workbook.

When you have selected one of the above workbooks, click OK.

Step 4 Carry out the macro actions

Carry out the actions you want to record. Note that as soon as you start recording the macro, the Stop Recording toolbar appears, as shown below.

Stop Recording toolbar

Step 5: Stop the macro

Click the square Stop button when finished.

Feedback

Don’t forget to press Stop, especially if you’ve made a mistake.
Make sure to press Stop and not the close box (X)for this toolbar.
Practice the steps for the procedure before you turn on the macro recorder.

Viewing your spreadsheet macro

To view the code just recorded:

from the Tools menu, select Macro/Macros…
select the name of your macro
click the Edit button.

Recording to other locations

Recording into a new workbook

To record macros into a new workbook:

1From the menu, select:

Tools/Macro/Record New Macro

2Click New Workbook under Store macro in.

You must set this option each time you record a macro.

Recording into the Personal Macro Workbook

To record macros that you always want available:

1From the menu, select Tools/Macro/Record New Macro

2Click Personal Macro Workbook under Store macro in.

You must set this option each time you record a macro.

Note that the Personal Macro Workbook is a hidden workbook that is always open.
Remember to give macros that you record into this workbook meaningful names and descriptions.
You may also want to assign frequently used macros to menu items or shortcut keys.

Assigning macros in a spreadsheet

You can assign a macro to run when a particular key combination is pressed, or when you click a particular graphic object, menu item or toolbar button.

Microsoft Excel provides many ways to assign your macro including:

a keyboard shortcut
a Tools menu item
a button or graphic object
a toolbar button.

Assign a macro to a keyboard shortcut

You can assign keyboard shortcuts when a macro is recorded, as you have already seen.

You can also assign a keyboard shortcut after a macro has been created by:

1Selecting from the menu Tools/Macro/Macros…

2Selecting the macro you want to deal with, and

3Clicking the Options button.

You can type a letter in the Shortcut key input box or press Shift and a letter.

Practice activity

In this activity you’ll create a simple spreadsheet macro called MyTitle that will:

turn gridlines off
record your name in C3
format C3.

To create the macro:

from the Tools menu, select Macro/Record New Macro
in the Macro Name box type: MyTitle
in the Description box type: Enters a Title into cell C3, and formats the sheet
assign the shortcut key: Ctrl + Shift + T
press OK.

The macro is now recording and you should see the Stop Macro button appear on your screen on its own toolbar.

1Turn off gridlines:

from the Tools menu, select Options, then select the View tab
from the Windows options, clear the Gridlines box, press OK.

2Enter title:

select cell C3, enter YOUR FULL NAME, then press the Enter key.

3Format the cell C3:

reselect C3
from the Format menu, select Cells, then select the Font tab
select a different Font, change the style to Bold, size to 18
select the Border tab, select the double line and change the border option to Outline
press OK
from the Format menu, select Column and AutoFit Selection.

4Click the square Stop Recording button or select Macro/Record New Macro from the Tools menu then select Stop Recording.

Your worksheet should look something like the spreadsheet below.

Sample Spreadsheet

5Run the macro you just created using the menu

select a new worksheet in your workbook
from the Tools menu, select Macro/Macros…
in the Macro Name list, select: MyTitle
click the Run button.

6Now use the shortcut key to run the macro

select a new worksheet in your workbook
type Ctrl + Shift + T.

Feedback

You should now have at least three sheets with your name in C3 and formatted without gridlines.
You should run your new macros from the Tools menu first. This ensures that it has been created and is ready for use.
Note that you are asked to test the macro in new sheets so that the changes can be clearly seen.

2005