1.3 Recording Macros

Beginning VBA

Previous Part: 1.2 Using the Visual Basic Editor

As we learnt in chapter 1.0, VBA is the programming language developed by Microsoft to control various parts of their applications.  It is possible to type or copy code into the Visual Basic Editor (VBE), then to run that code, just as we did in chapter 1.2.  But it is also possible to use the Macro Recorder, which automatically enters code into the VBE for you.

The basic principles of the Macro Recorder are:

(1) Start the recording
(2) Carry out some actions in Excel
(3) Stop the recording
(4) Excel interprets mouse clicks and keystrokes and converts them into VBA code in the VBE

Why not record every Macro?

You may be wondering, if it is possible to record a Macro, then why do we need to learn VBA code at all, surely we could just record every Macro.  Well, we will soon learn that the Macro Recorder creates VBA code which is very specific. If we clicked on Cell A10 whilst recording the Macro, then every time we run the Macro it will select Cell A10.  If we insert a row at the top of the worksheet the Macro will still select A10, even though we now want it to select Cell A11.  So, it is often necessary to adapt the recorded code to be more flexible.

Secondly, the recorded code may not capture every action carried out.  If we copied text from Word into Excel the Macro will not record the text being copied, but will record the paste into Excel.  So, it will actually try paste the last thing in the Clipboard, whatever that may be.

A recorded Macro can be very inefficient.  If we accidentally scrolled down the page, then scrolled up again, the Macro Recorder will capture this.  Every time the Macro runs, the action of scrolling down and up again will be repeated, because the Macro Recorder has no idea that it was a mistake.

Finally, we can achieve a lot more with VBA code that the Macro Recorder can capture.  If we want to carry out an action on every sheet in a workbook we cannot achieve this on a Macro Recorder, it will only perform the actions on the workbooks we have selected.  Any new sheets will be ignored by the Macro Recorder, as they were not selected during the recording process.

Whilst the Macro Recorder is useful, learning VBA is still significantly more beneficial.

The best use of the Macro Recorder

The best use the Macro Recorder is for learning the structure of VBA code.  If we want to learn how to turn a cell yellow; start the Macro Recorder, turn the cell yellow, then stop the Macro Recorder. We can now look at the code in the VBE, and can adapt this code to be used within our own VBA code.

Start the Macro Recorder

To start, click the Record Macro from the Developer tab (which we turned on in 1.1 Enable the Developer Ribbon).

Developer -> Code -> Record Macro

Developer ribbon, Record Macro

The Record Macro box will appear.

Excel's Record Macro Window

There are several options in this window:

Macro Name: By default Excel will give the Macro a name.  It is a good idea to change this to something more meaningful, as later on when we have a lot of Macros it will be much easier to find the one we want to use.

Shortcut key: (optional) Macros can be run through a number of methods, such as pressing a button, when a workbook opens or pressing F5 in the VBE window.  It is also possible to select a shortcut key so that every time that combination of keys is pressed the Macro will run. We must be careful though, as if we select an existing short cut, such as Ctrl + p the Macro will run instead of the normal actions for Ctrl + p. We should always check to see if our shortcut key does anything before we use it.

Store macro in: (optional) The default option is This Workbook.  The dropdown list will give us a variety of options of where to store our code. For the time being lets just select This Workbook, we can use other options in the future.

Description: (optional) This can be useful if we need to remember what a Macro is for.

Now that we are ready, click “OK”.

Carry out some actions, like typing your name or changing the color of a cell.

When you are ready stop the Macro Recorder.

Developer -> Code -> Stop Recording (or press the stop symbol in the bottom left of the status bar).

Developer ribbon, stop macro

Running the recorded Macro

There are various ways to run the recorded Macro. Some options you could use now are:

Developer -> Code -> Macros (or press ALT + F8). Select your Macro, Click Run.

If you assigned a shortcut, then you can press the keys now

The Macro should now run. You may need click on a new sheet, or clear the actions you undertook last time, so that you can see the Macro run again.

Previous Part: 1.2 Using the Visual Basic Editor

Next Part:1.4 Understanding the code

Leave a Reply

Your email address will not be published. Required fields are marked *