Recording macros in Excel Visual Basic - VBA Macros
Part four of a six-part series of blogs

If you're not sure what the Excel Visual Basic for Applications language is (or how it can help you) this blog attempts to explain! Learn what VBA is, and how you can record macros and then replay them again and again to automate work in Excel.

  1. Excel VBA Macros - Free On-Line VBA training
  2. What is Excel VBA, and how can you use it?
  3. Starting to Record Macros in Excel VBA
  4. Recording your Macro and Finishing (this blog)
  5. Tidying up your macro that has been recorded
  6. Playing back your macro recorded in Excel VBA

This blog is part of our Excel macros online tutorial series - complemented, of course, by Wise Owl's excellent training courses in VBA and Excel for businesses like yours!

Posted by Andy Brown on 30 June 2011

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Recording your Macro and Finishing

Once you've started recording a macro, Excel (or the Bob/Bobette inside your computer) will transcribe everything you do into Visual Basic, until you stop recording.  For this reason, you should proceed carefully!

For our example, here's what you should do. 

Step 1 - Copying each Name

Remembering that you are recording, you should follow these steps exactly to make your recorded macro do what you want:

  • First, click on the first cell to be copied (C4 on the Input Form worksheet), and copy it:
Copying cell while recording macro

Right-click on each cell to be copied, and choose to copy it using the short-cut menu (as here) or any other way

 
  • Now go to the top of the list of names in the Results sheet:
Go to top of list of names

Select the top "person" in the list of names

You now want to select the first non-blank cell in the list.  To do this, you want to record the instructions:

  • Go down to the bottom of the block; then
  • Go down one cell further

rather than the instructions:

  • Go to cell B8 (which will always take you back to the same cell, whenever you run the macro)

You therefore need to switch from absolute to relative recording.

Switching to Relative Recording

To change the way Excel records macros:

  1. Choose the Developer tab on the ribbon (if you can't see this, see below).
  2. Choose to use relative references.
Switching to relative recording

If you can't see the Developer tab, see below

The Excel 2003 recording toolbar

In Excel 2003, you can accomplish the same thing by clicking on the tool shown (this only appears once you have started recording).

 

If you can't see the Developer tab on your ribbon, you need to enable it:

  • For how to do this in Excel 2010, see this separate blog
  • For how to do this in Excel 2007, read on!

If you're using Excel 2007, first choose to show Excel options:

Selecting Excel Options
 

You can now show the Developer tab in the ribbon:

Showing the Developer tab

Tick the box shown to display the Developer tab on your Excel ribbon

Switching between absolute and relative recording is one of the few things for which Excel will not record commands.

Pasting the Names

You can now paste in the name you've copied:

 Go to the top of the column Go to the bottom cell
Go to the top of the column of names Press CTRL + the down arrow to go to the bottom of the block, then press the down arrow again to go to the first blank cell

Formatting the Results

If you patiently repeat the steps above, you will eventually build up a row of copied data:

Copied data, ready for formatting

The results of copying and pasting - the right data, but with the wrong formatting

You can now format the cells using the standard formatting tools so that:

  • the first cell is in a larger font;
  • the other cells are vertically aligned centrally; and
  • the whole row has an outline border.

Here's what the final result could look like:

The results copied and formatted

The final result, correctly formatted

You can now stop recording!

Stopping Recording

To stop recording, click on the tool shown below (in Excel 2003 this tool is on its own toolbar, which appears when you start recording).

The stop recording button

Click on the button shown to stop recording

You should now have a macro which copies a contestant's score to the results sheet.  It's time now to tidy it up!

You could omit this last stage, but it's likely that you made several mistakes in the recording process which you'll need to rectify, and it can't do any harm to check what you've done!

 

This blog has 0 threads Add post