Microsoft Excel – Recording Macros
A macro is a series of commands executed from just one
command from the user. You can use Excel’s macros to automate tasks that
you find yourself doing repeatedly. There are several ways to create
macros, also called “subs,” using Visual Basic for Applications (VBA), the
programming language for Excel. You can copy some code from a friend (or a
Web site), you can record a simple macro by using the Macro Recorder from Excel’s Tools
Menu ( Macros >
Record New Macro ), or you can write the VBA code yourself.
Record a new macro
From the Tools Menu, choose Macro Record New Macro… and you
will get this dialog box:
The first step is to give your macro a
descriptive name. You cannot use spaces, dashes (hyphens) or other special
characters in your macro name, if you try then you will get an error
message - "That name is invalid". You can use underscores
(_ ) in place
of spaces/dashes. (i.e. Page_Setup or Delete_Spaces will work).
Next decide where to store the macro. You can save it in the current
workbook (ThisWorkbook). This choice will attach this macro to the current
workbook, and it will only be accessible when this workbook is open. If
you want to be able to use the macro from any workbook on that computer,
choose the "Personal Macro Workbook". Next decide if you want a shortcut
key assigned to this Macro. If you do not want a shortcut key, leave the
option blank, and click OK to begin recording your macro.
If desired, you can enter a description to better explain what this macro
will do.
Creating a Macro in your Personal.xls file
If this is a macro that you want to have available at all times, it is a
good idea to store it in a special file called Personal.xls. This is a
hidden file that opens automatically every time you open Excel. (It’s
hidden in the sense that you don’t really see it, but it’s there.) You
might or might not have a Personal.xls file on your PC, but it’s easy to
create one, just in case. Open a new workbook, select from the Tools Menu,
Macro > Record New Macro, and fill it in as shown below. The
important part is that you should choose Personal Macro Workbook as the
place to store your macro. This will place your new macro in your Personal.xls file, if it already exists, or it will create this file if it
doesn’t yet exist.
Follow the steps in the next section to create the Macro. You now have a
PERSONAL.xls file.
Recording a Macro
After you’ve named your macro, Excel takes you back into your spreadsheet.
You will now have a new Toolbar floating on your screen.
If you want you
can move the Toolbar, as with any other Toolbar, without affecting the
macro, by clicking and dragging the blue title bar of the Toolbar. This new
Toolbar only has two buttons, Stop and Use Relative Reference. If you
click the X to close this menu, Excel will stop recording the macro. You
can also stop recording by clicking Stop.
The Use Relative Reference button determines whether the Macro recorder
uses relative or absolute references to the cells on a worksheet. The
button is set to Absolute when it's first shown. This means that Excel
will record the exact address. Absolute references do not automatically
adjust when you insert rows and columns. When the button is pushed, Excel
will keep track of a Relative Reference. This means Excel will record the
macro, relative to where you start. It is possible to switch between modes
while recording your macro.
Once you have completed all the steps that you wanted to automate, stop
the macro. You should test the macro to make sure that it works, if you
assigned a keyboard shortcut, use that, otherwise go through the menu,
Tools>Macros>Macros… (Alt-F8), choose the macro and Run.
If you Edit the macro you will actually see the Visual Basic code, in fact
it is often advisable to do so.
This is also the window where you can delete a macro. 
By saving your macro in the PERSONAL.XLS as the macro shown above was,
you will not be able to delete the macro yet, you will have to unhide the
personal workbook.
Window->Unhide->Personal.xls->OK
Once you are in this Worksheet, you can delete the macros saved here, just
be sure to hide this worksheet again when you are done. (Window->Hide)
Keyboard Shortcuts
To run a macro by pressing a keyboard shortcut key, enter a
letter in the Shortcut key box, either when you create the macro, or later
from the Options button on the Macro Window. You can use CTRL+ letter or
CTRL+SHIFT+ letter, where letter is any letter key on the keyboard. The
shortcut key will override any default Microsoft Excel shortcut keys while
the workbook that contains the macro is open.
The Visual Basic Editor
To view a recorded macro, you can access the Visual Basic Editor by
pressing the Alt-F11 key combination. On the left you will see the Project
window, which lists the contents of Personal.xls. (Click on the plus signs
to expand the contents, if necessary.) Yours will differ from mine,
depending on what you already have in your Personal.xls file, but you
should have a Modules folder with a Modules Folder (probably Module1)
item. By double-clicking on this item, you see the code you just recorded
in the code window to the right. You can actually edit the macro directly
here and as you become more proficient with VBA write complete macros.
Another way is to simple copy and paste code samples from various sources.
Adding Toolbars and Buttons
You can run your macros by adding a button to an existing
Toolbar, or even create your own Toolbar. Before going any further, it’s
important to realize that any changes you make to menus or toolbars are
stored in a secret file on your hard drive. This means that they are
remembered when you close Excel. So if you create a new menu item, say,
and then close Excel and open it the next day, that new menu item will
still be there. The name of the secret file is Excelxx.xlb. (For example,
the file is Excel11.xlb on my PC that’s running Excel 2003. It’s stored
somewhere in the Documents and Settings folder. You can find it most
easily by doing a search in Windows Explorer.) If you modify toolbars
and/or menus on one PC and want these same changes to occur on another PC,
you can copy this .xlb file to the other PC. Of course, if the modified
menu items or toolbar buttons run macros from your Personal.xls file, you
should copy this file to your other PC as well. Again, you can do a search
in Windows Explorer to find where Microsoft automatically stores your
Personal.xls file. It’s also a good idea to locate and create a copy of
the .xlb file, that way you can restore you settings easily if anything
goes wrong.
To add a toolbar, choose
Tools > Customize, click on the Toolbar tab. Click New; Change the Toolbar
name to your choice e.g. MyToolbar.
Click OK and you will create a blank
Toolbar like this:
. To add buttons to the toolbar switch to the Commands
tab, on the Customize Window If the button you want already exists you can
click and drag the button to the toolbar. As you drag the button to the
toolbar, you will see an I marker on the toolbar to indicate where it's
placing the button.
If you would like to add the buttons for your macros, scroll to the bottom
of the categories list. Choose Macros and drag over the Custom Button that
looks like a happy face. To assign the button to your macro, make sure the
button is highlighted (it has a box around it), from the Customize Window
choose Modify Selection. Choose Assign Macro…, Select the macro you want
for this button, and click OK.
To change the way the button looks, so you don't have several happy face
buttons, click on the Modify Selection button again. You can Change the
Button Image, or give the button an appropriate Name and tell Excel to use
Text Only or Image and Text. You can also copy and paste an existing
button image and even Edit the Button Image to create your own graphical
button.