Microsoft Office Development and Consultancy

Excel

Step 1 - Rehearse what you want to Record

The Macro Recorder can be used to record simple macros.

Recording macros is very useful even if you are a very experienced programmer, although there are some limitations that you need to be aware of.

If you are relatively new to macros it is worth rehearsing your steps first before recording them.

This will help you to understand the code that is generated and to help make your code as efficient as possible.

When you record a macro Excel is basically recording the keystrokes, menu commands exactly as they are pressed.

After you have recorded a few single steps you can run the macro to perform the required task again and again.

Once you start the Macro Recorder all your keystrokes will be recorded and converted into VBA code.

Step 2 - Record your Macro

Select (Tools > Macro > Record New Macro) to display the Record Macro dialog box.

Alternatively you could press the "Record Macro" button on the Visual Basic toolbar.

 (Tools > Macro > Record New Macro) dialog box

The name of your macro will automatically default to "Macro1", "Macro2", etc depending on the number of macros that have been recorded in that specific workbook.

Macro names must begin with a letter and cannot resemble cell addresses (i.e. A1, B5, BT100 etc).

Macro names cannot include spaces although the underscore character can be used (e.g. "macroname_2"). You can also include numbers in your macro names.

It is sometimes easier to accept the default name and then change the name of the procedure later.

The shortcut key and description are optional so do not worry about these at this point, however it is very important to remember where your macro will be stored.

Step 3 - Where to store your macro

There are three possible workbooks where you can store your macros:

This Workbook - This is the default location and is often the best place if you are relatively new to macros. A macro that has been saved into a specific workbook is only available when that particular workbook is open. The currently active workbook is also referred to as the current workbook or active workbook.

New Workbook - A completely new workbook will be opened for you automatically. This can be useful for quickly examining the generated code but remember that the workbook you store the macro in must be open in order for the macro to be run.

Personal Macro Workbook - Storing your macros here will mean that they are available every time Excel is open and are not reliant on any one particular workbook.

Once you press OK a small floating toolbar will appear. This is the Stop Recording toolbar and indicates that any keystrokes you make will be recorded.

The status bar also displays the message "Recording".

You can now perform the keystrokes that you would like to be automated.

A new code module is often inserted into the active workbook automatically to contain the generated VBA code.

Step 4 - Using Absolute References

Excel will default to recording all the cell references as Absolute. These are recorded in A1 notation.

This means that any cell references that are selected are explicitly referred to and you will always get exactly the same result when the macro is played back.

Absolute references are useful when you want to perform the same action in an identical place every time.

The second button on this toobar will toggle between recording in Absolute mode and recording in Relative mode.

When this button is pressed, relative references are used and when it is not pressed absolute references are used.

Step 5 - Using Relative References

In some cases you may want the recorded macro to work with cell references that are Relative to a particular cell. These are recorded in R1C1 notation.

For example you might want to enter some data into the active cell and also enter some data into the cell directly below.

Relative references are useful when you want to perform an action anywhere on a worksheet.

You can change to relative recording by pressing the button on the right hand side of the Stop Recording toolbar. This button is a toggle.

You can change between absolute and relative references at any time while you are recording your macro.

Be aware that there is no indication given other than the button being pushed in, as to which mode you are currently in. The first recorded macro in a workbook will always start by using absolute references.

If you stop recording when you have been using relative references the next recorded macro will start by using relative references.

Using relative references will generate code that always refers to the cell currently selected before you start recording and then uses the "Offset" method to obtain the relative cell address.

Step 6 - Stop Recording

You can stop recording at any time by selecting (Tools > Macro > Stop Recording).

Alternatively you can use the first button on the Stop Recording toolbar. The toolbar will then disappear.

There is also a Stop Recording button on the Visual Basic toolbar that can be used as well.

It is very important to stop recording once you have finished otherwise Excel will carry on recording your keystrokes indefinitely.

Once you have finished recording you can cut and paste the code (via the clipboard) to different code modules.

Step 7 - Viewing the Code

The only way to view the VBA code is through the Visual Basic Editor window.

This window can be displayed by selecting (Tools > Macro > Visual Basic Editor) or by pressing (Alt + F11).

 Visual Basic Editor, Project Explorer window

You will need to locate the Project in the Project Explorer window. This should appear in the top left corner. If you cannot see it, select (View > Project Explorer).

There should be a project there called VBAProject followed by the name of the workbook in brackets. Expand this project.

Select the Modules node and expand it. There should be a module called "Module 1". Double click this module to display the code window.

Your macro will appear here and all the VBA code is contained within the "Sub" and "End Sub" statements.

You will often find that the Macro Recorder will generate more code than you really need although it is easy to remove any unnecessary code.

Step 8 - Changing Macro Security

The macro security only comes into play when you open workbooks that contain existing macros.

It is possible to record (and run) a macro in any open workbook even when your security setting is set to Very High or High.

A lot of books and other websites give you the impression that you need to change the macro security level before you can record a macro. This is not the case.

You will however need to change the security level to Medium if you want to run any macros.

Step 9 - Things to Remember

The code that is generated is normally very specific and often dependent on local settings or options.

It is not possible to record a macro and have the generated code placed within an existing macro. You will have to cut and paste the lines of code manually.

The code generated by the macro recorder often contains a lot of unnecessary arguments and lines and should really be cleaned up afterwards.

Be aware that the Macro Recorder cannot create any of the following: Loops, Variables, Error Handling or Conditional statements.

The code generated by the macro recorder can often be quite inefficient and will run quite slowly compared to code that has been optimised.