VBA HOME PAGE

Menu

Recording macros

Looking at the code

Ways of running macros

Where macros are stored

Reasons to write macros

Writing macros

Procedure types

Visual Basic editor (VBE)

Rules & conventions

Excel objects

Range/Selection objects

Object hierarchy

Object browser

Chart objects

Pivot Table objects

Formulas

Visual Basic Functions

Creating Add-Ins

Variables & constants

Object variables

Arrays

Collections

Message Box

VBA Input Box

Excel Input Box

Making decisions (If)

Making decisions (Case)

Looping (Do...Loop)

Looping (For...Loop)

With...End With blocks

User defined functions

Event handling

Error handling

Debugging

Creating User Forms

DAO/ADO Objects

Input/Output Files

Other links

Example code snippets

Userform input example

Recording a macro means that you can record a task in Excel using the keyboard and mouse and then play the recording back whenever you need to perform the task.

Macros are created using VBA (Visual Basic for Applications) code.  Whenever you record a macro, the code is automatically created for you.  However, recording macros does create a lot of unnecessary code.

You can store your macros in:

1. This Workbook

2. New Workbook

3. Personal Macro Workbook (Personal.xls/xlsm/xlsb)

Personal.xls/xlsm/xlsb is a hidden workbook, which is open in memory.  This means that you can use macros stored in Personal.xls/xlsm/xlsb any time you open Excel.  Macros stored in the other 2 locations are only available in the one workbook.

There two modes when recording a macro:

 Absolute Macros

The following example will record a macro to put your company name and address into the cells A1, A2, A3, A4 and A5.  This is called an Absolute Macro because it specifies which cells are to be used.

1. Create a new blank workbook.

2. Click on the Developer tab (make sure Use Relative References is pressed up).

3. Click Record Macro icon.

4. Type in a name for the Macro e.g. Absolute

Enter a Shortcut Key, if required, e.g. Ctrl + e

Select where you would like to store the Macro e.g. This Workbook

Type in a description for the macro, e.g.
Macro to place company name and address into cells A1:A5.

5. Click on OK.

6.  Now record the macro.

Click on A1

Type “Company Name”

Click on A2

Type “Address 1”

Click on A3

Type “Address 2”

Click on A4

Type “Address 3”

Click on A5

Type “Postcode”

Click back on A1

7. Click on the Stop Recording button.

8. Now try out the macro on Sheet 2.

Click anywhere on the sheet and use the keystroke Ctrl + e.

This places the company name and address into A1:A5 from anywhere within the sheet. 

Therefore, this is called an Absolute Macro.

 Relative Macros

The following example will record a macro to put your company name and address into the ActiveCell and the 4 cells beneath it.  This is called a Relative Macro because the cells which are to be used are determined by the ActiveCell.

1. Create a new blank workbook (if necessary).

2. Click on the Developer tab (make sure Use Relative References is pressed down).

3. Click Record Macro icon.

4. Type in a name for the Macro e.g. Relative

Enter a Shortcut Key, if required, e.g. Ctrl + h

Select where you would like to store the Macro e.g. This Workbook

Type in a description for the macro, e.g. Macro to place company name and address into the active cell.

5. Click on OK.

6. Now record the macro.

Click on A1 (i.e. the ActiveCell)

Type “Company Name”

Click on A2

Type “Address 1”

Click on A3

Type “Address 2”

Click on A4

Type “Address 3”

Click on A5

Type “Address 4”

Click back on A1

7. Click on the Stop Recording button.

8. Now try out the macro on Sheet 3.

Click anywhere on the sheet and use the keystroke Ctrl + h.

This places the company name, address and phone number into the ActiveCell and the 4 cells beneath it. 

Therefore, this is called a Relative Macro.

Next Topic: Looking at the code

Note: Excel extensions will vary based on which version you are working with. Up to and including Excel 2003, the file extension is ".xls". From version 2007, you will need to save a macro enabled Excel file which has an extension ".xlsm/xlsb".

Tip: To help with locating some of the macro commands, users may want to switch on the and add the Developer tab to the Ribbon Bar. Click the Office button (top left corner icon) and choose Excel Options. From the Popular section, tick and enable Show Developer tab in Ribbon.

VBA Keywords: ActiveCell, Range, Selection, OffSet.

Note: Using a shortcut key, which is already assigned to another function, will be superseded whilst the macro’s file is open. Therefore, it is recommended to only assign keystrokes to procedures that will be used on a more frequent basis and not to borrow the more commonly used shortcut keys (i.e. Ctrl + C - copy command).

Tip: Alt + F8 function keys displays the Macro dialog box to run loaded macros.