Recording Excel Macros - 121213

(1)

Recording

Excel

Macros

Learn to Record and Edit VBA Macros

In Microsoft Excel

By Martin Green

(2)

U

n

i

v

e

r

s

i

t

y

o

f

G

r

e

n

w

i

c

h

S

t

u

d

e

n

t

s

'

E

d

i

t

i

o

n

f

o

r

E

d

u

c

a

t

i

o

n

a

l

U

s

e

O

n

l

y

U

n

i

v

e

r

s

i

t

y

o

f

G

r

e

n

w

i

c

h

S

t

u

d

e

n

t

s

'

E

d

i

t

i

o

n

f

o

r

E

d

u

c

a

t

i

o

n

a

l

U

s

e

O

n

l

y

U

n

i

v

e

r

s

i

t

y

o

f

G

r

e

n

w

i

c

h

S

t

u

d

e

n

t

s

'

E

d

i

t

i

o

n

f

o

r

E

d

u

c

a

t

i

o

n

a

l

U

s

e

O

n

l

y

Editing

Editing CodeCode ... 3636 Getting

Getting Help...Help... 3636 Using t

Using the he Help Help MenuMenu ... 3636 Context Sensitive Help with the F1 Key

Context Sensitive Help with the F1 Key ... 3636 Editing

Editing VBA Code VBA Code Practical Practical Exercises...Exercises... 3838 Step-by-Step: Experiment with

Step-by-Step: Experiment with ColoursColours ... 3838 Create

Create the the Macro...Macro... 3838 Summary

Summary ... 4444 Step-by-Step: Build a Rando

Step-by-Step: Build a Random Numbers Macrom Numbers Macro ... 4545 Create

Create the the Macro...Macro... 4545 Summary

Summary ... 5050 What

What Comes Comes Next?Next? ... 5151 Where Do

Where Do I Go I Go from Here?from Here? ... 5151 Recommended

Recommended Web Web Sites...Sites... 5151 Recommended

Recommended ReadingReading ... 5151 Notes

Notes and and Further Further Information...Information... 5252 About

About the the AuthorAuthor ... 5252 Copyright...

Copyright... 5252 Limitation

Limitation of of LiabilityLiability ... 5252

First Published in 2005 by Martin Green First Published in 2005 by Martin Green

Web Site:

(5)

U

n

i

v

e

r

s

i

t

y

o

f

G

r

e

n

w

i

c

h

S

t

u

d

e

n

t

s

'

E

d

i

t

i

o

n

f

o

r

E

d

u

c

a

t

i

o

n

a

l

U

s

e

O

n

l

y

About This Book

Who is it for?

This book is for the Microsoft Excel user

This book is for the Microsoft Excel user who wants to increase their who wants to increase their productivity andproductivity and automate tedious or repetitive tasks with the help of

automate tedious or repetitive tasks with the help of Excel Macros.Excel Macros. Anyone thinking about learning Excel VBA (Visual Basic

Anyone thinking about learning Excel VBA (Visual Basic for Applications) programming willfor Applications) programming will also find this a good place to start.

also find this a good place to start. To make the most of this

To make the most of this book you should have at least a book you should have at least a basic working knowledge ofbasic working knowledge of Microsoft Excel. You don't need to be

Microsoft Excel. You don't need to be an "advanced" or "expert" Excel user. You an "advanced" or "expert" Excel user. You don't needdon't need any prior knowledge of recording or editing macros, nor do

any prior knowledge of recording or editing macros, nor do you need any knowledge of VBAyou need any knowledge of VBA programming, or any other programming language.

programming, or any other programming language. Recorded macros are useful to Excel users of all

Recorded macros are useful to Excel users of all skill levels, from those creating or workingskill levels, from those creating or working with simple spreadsheets to those who use Excel's most

with simple spreadsheets to those who use Excel's most advanced tools. Even Exceladvanced tools. Even Excel developers, skilled at writing VBA programs, find occasional use for this valuable tool. developers, skilled at writing VBA programs, find occasional use for this valuable tool.

What's Inside?

This document is about recording macros in Microsoft Excel. It describes the

This document is about recording macros in Microsoft Excel. It describes the process ofprocess of recording a macro and illustrates different techniques with step-by-step practical exercises. recording a macro and illustrates different techniques with step-by-step practical exercises. It explains the different ways of r

It explains the different ways of r unning macros and introduces the Visual unning macros and introduces the Visual Basic Editor,Basic Editor, Excel's tool for editing the programming code behind the macros. It

Excel's tool for editing the programming code behind the macros. It concludes with someconcludes with some step-by-step exercises in editing existing macros and writing VBA code to create new ones. step-by-step exercises in editing existing macros and writing VBA code to create new ones. Each section is illustrated with helpful screenshots throughout.

Each section is illustrated with helpful screenshots throughout. The material in this

The material in this book is relevant to all recent versions of Microsoft Excel from Excel 97book is relevant to all recent versions of Microsoft Excel from Excel 97 onwards.

onwards.

Introducing Recorded Macros

What is a Macro?

A macro is a collection of commands written

A macro is a collection of commands written in the Visual Basic programming language.in the Visual Basic programming language. When is macro is run

When is macro is run the commands are executed and perform their the commands are executed and perform their various tasks. Macrosvarious tasks. Macros are used to carry out

are used to carry out routine, repetitive or laborious tasks automatically and with speed.routine, repetitive or laborious tasks automatically and with speed. Macros can be written by hand using the Visual Basic Editor which is included as part of most Macros can be written by hand using the Visual Basic Editor which is included as part of most Microsoft Office programs. Some Microsoft Office programs (Excel, Word, PowerPoint) have a Microsoft Office programs. Some Microsoft Office programs (Excel, Word, PowerPoint) have a Macro Recorder. This tool lets you create a

Macro Recorder. This tool lets you create a macro without requiring any knowledge of Visualmacro without requiring any knowledge of Visual Basic programming. It works by recording your actions as you perform a

Basic programming. It works by recording your actions as you perform a task in thetask in the program. You can save the recorded

program. You can save the recorded macro and play it back to have the macro and play it back to have the same tasksame task performed automatically.

performed automatically.

Although you can record and use

Although you can record and use macros without any knowledge of Visual Basicmacros without any knowledge of Visual Basic programming, with a little skill you can edit

programming, with a little skill you can edit or modify recorded macros to improve theiror modify recorded macros to improve their power and functionality.

power and functionality.

This document will show you, with the

This document will show you, with the help of some practical examples, how to record ahelp of some practical examples, how to record a macro in Microsoft Excel, how to carry out

macro in Microsoft Excel, how to carry out some simple editing of recorded macros using thesome simple editing of recorded macros using the Visual Basic Editor, and how to assign a macro to a menu item or toolbar button.

Visual Basic Editor, and how to assign a macro to a menu item or toolbar button.

What a Recorded Macro Can Do and What It Can't

A macro records commands. The commands can be made by clicking a

A macro records commands. The commands can be made by clicking a button or choosing abutton or choosing a menu item with the mouse, or

menu item with the mouse, or you can use keyboard shortcuts. It makes no you can use keyboard shortcuts. It makes no difference todifference to what is recorded or how t

what is recorded or how t he macro performs.he macro performs.

The Macro Recorder can record almost everything you do with

The Macro Recorder can record almost everything you do with the mouse or keyboard but itthe mouse or keyboard but it has its limitations...

(6)

U

n

i

v

e

r

s

i

t

y

o

f

G

r

e

n

w

i

c

h

S

t

u

d

e

n

t

s

'

E

d

i

t

i

o

n

f

o

r

E

d

u

c

a

t

i

o

n

a

l

U

s

e

O

n

l

y

•

• It can not create It can not create looping code - code which when executed will repeat looping code - code which when executed will repeat as many timesas many times

as is necessary to perform a task. as is necessary to perform a task.

•

• It can not create It can not create conditional code – in Visual Basic conditional statements are used toconditional code – in Visual Basic conditional statements are used to

determine how code is executed. determine how code is executed.

•

• It can not create It can not create code that interacts with the user. Tcode that interacts with the user. T he Visual Basic programmer canhe Visual Basic programmer can

include messages and dialogs into their programs to provide information to and include messages and dialogs into their programs to provide information to and gather information from the user.

gather information from the user.

But despite its limitations the Macro Recorder is a

But despite its limitations the Macro Recorder is a very useful tool...very useful tool...

•

• The Macro Recorder can be used very successfully without any The Macro Recorder can be used very successfully without any knowledge ofknowledge of

programming. programming.

•

• The Macro Recorder is an excellent learning tThe Macro Recorder is an excellent learning t ool for VBA programming.ool for VBA programming. •

• Even experienced developers occasionally use the Macro Even experienced developers occasionally use the Macro Recorder to quickly createRecorder to quickly create

code procedures, especially those that are quick to record but

code procedures, especially those that are quick to record but complex to write suchcomplex to write such as those involving charts or pivot tables.

as those involving charts or pivot tables.

The macro recorder records commands, not the time taken t

The macro recorder records commands, not the time taken t o carry them out so to carry them out so the lengthhe length of time it takes to

of time it takes to record a macro is largely irrelevant. Timing can record a macro is largely irrelevant. Timing can become a factor in hand-become a factor in hand-written VBA code but you can

written VBA code but you can not record time intervals or pauses.not record time intervals or pauses.

Visual Basic or VBA?

Visual Basic is a long-standing and established

Visual Basic is a long-standing and established programming language developed from anprogramming language developed from an early computer programming language called Basic. Visual Basic can be used t

early computer programming language called Basic. Visual Basic can be used t o create manyo create many different types of "standalone" programs which can f

different types of "standalone" programs which can f unction independently. There have beenunction independently. There have been many versions of Visual Basic, the last

many versions of Visual Basic, the last being Visual Basic 6 (VB6). After VB6 being Visual Basic 6 (VB6). After VB6 Visual BasicVisual Basic became part of Microsoft's .NET programming platform in the

became part of Microsoft's .NET programming platform in the form of VB.NET although itsform of VB.NET although its form is largely unchanged.

form is largely unchanged.

Visual Basic for Applications (VBA) is a subset of

Visual Basic for Applications (VBA) is a subset of the Visual Basic programming language forthe Visual Basic programming language for use within Microsoft Office applications. Each component of Microsoft Office has it's

use within Microsoft Office applications. Each component of Microsoft Office has it's ownown version of VBA and all are

version of VBA and all are compatible with each other allowing the programmer to controlcompatible with each other allowing the programmer to control not only the host program but

not only the host program but also other Microsoft Office programs from one application.also other Microsoft Office programs from one application. Unlike Visual Basic, VBA can not

Unlike Visual Basic, VBA can not create standalone programs. VBA code has to run create standalone programs. VBA code has to run fromfrom within a Microsoft Office host application.

within a Microsoft Office host application. In practice there is little

In practice there is little difference between Visual Basic and VBA. The differences lie difference between Visual Basic and VBA. The differences lie in thein the way that the code is

way that the code is hosted and executed.hosted and executed.

About Macro Security

Opening Files Containing VBA Code

Everyone working with macros should be aware of

Everyone working with macros should be aware of the security implications of working withthe security implications of working with files containing code. Unscrupulous people can create malicious code such as viruses and files containing code. Unscrupulous people can create malicious code such as viruses and distribute it embedded in innocent-looking files such as Word documents and Excel

distribute it embedded in innocent-looking files such as Word documents and Excel workbooks. For this reason all Microsoft Office programs are equipped with security workbooks. For this reason all Microsoft Office programs are equipped with security measures to detect whether or not

measures to detect whether or not a file contains programming code at the a file contains programming code at the point at whichpoint at which the file is opened.

the file is opened.

If the program detects code in a

If the program detects code in a file a warning message is displayed (file a warning message is displayed ( Fig. 1Fig. 1). The dialog). The dialog allows you to disable the code before opening

(7)

U

n

i

v

e

r

s

i

t

y

o

f

G

r

e

n

w

i

c

h

S

t

u

d

e

n

t

s

'

E

d

i

t

i

o

n

f

o

r

E

d

u

c

a

t

i

o

n

a

l

U

s

e

O

n

l

y

Fig. 1 A macro security warning message

The fact that you see this message does not mean that malicious code has been detected, merely that code is present. If you know that there are macros in the file (perhaps you or a colleague has created it) then it should be safe to enable the macros and proceed with opening the file.

If you were not expecting the file to contain macros (perhaps it was sent to you from someone else) then you should, at least in the first instance, disable the macros. Disabling safe macros may impair the functioning of the file. If this is the case and you are still unsure of the file's safety, have your anti-virus software check the file first. Providing your anti-virus software is up-to-date and it reports that the file is "clean" then it should be safe to open with macros enabled.

Setting the Program's Security Level

If you regularly record or write and use macros the security level of t he program you are working in should be set to Medium. You can find out more about security options and set your chosen security level by visiting the Security dialog (Fig. 2) (Tools > Macro > Security).

This offers three levels of security. The highest level will automatically disable "unsigned" macros, only allowing macros from "trusted" sources to be run. A signed macro is one to which an electronic security certificate is attached and you can specify which certificate holders you trust. Certificates are issued by respected IT security organisations such as Verisign™

Fig. 2 The Security dialog

The lowest level of macro security is not recommended as it offers no protection from unsafe macros.

If you have a brand new installation of Microsoft Office you might find (depending on which version of Microsoft Office you are using) that the default security level is set to High. If this

(8)

U

n

i

v

e

r

s

i

t

y

o

f

G

r

e

n

w

i

c

h

S

t

u

d

e

n

t

s

'

E

d

i

t

i

o

n

f

o

r

E

d

u

c

a

t

i

o

n

a

l

U

s

e

O

n

l

y

is the case you will see a different message when you attempt t o open a file containing macros (Fig. 3).

Fig. 3 This message is displayed when the security is set to High.

This message does not give you the option to enable the file's macros. If you want to make use of the code in the file you must set the program's security level to Medium then close the file. When you reopen the file you will see the usual message (Fig. 1) allowing you to enable its macros.

Emailing Files Containing Code

The proliferation of viruses in recent times has resulted in increased email security measures and you must bear this in mind when sending or receiving files containing macros. Recent versions of email programs such as Microsoft Outlook can detect whether or not files attached to email messages contain code and may strip these attachments from the

message. Sometimes these measures are implemented at the email post office. Either way the user usually can not change the built-in security settings.

When sending files containing macros it is a wise precaution to pack them into a .zip file. This has the result of effectively "hiding" the code from the email security and also rendering the file "safe" for the recipient at Inbox level. If you are emailing a file containing macros to someone you should always warn them that this is the case. You should always virus-check any file that you receive from someone else which contains macros, even if they are trusted by you.

If you find that even "zipping" your code-containing files is not enough (some email security software has the ability to look inside .zip files) make use of your zipping program's option to encrypt the file, then include the password for decrypting the file in the email message.

Starting and Stopping the Macro Recorder

The Macro Recorder works the same way in all the Microsoft Office programs in which macro recording is available. To start the Macro Recorder open the Tools menu and choose Macro > Record New Macro. This opens the Record Macro dialog box (Fig. 4) where you can choose various macro options. The Macro Recorder does not start recording your actions until you click the OK button on the Record Macro dialog.

(9)

U

n

i

v

e

r

s

i

t

y

o

f

G

r

e

n

w

i

c

h

S

t

u

d

e

n

t

s

'

E

d

i

t

i

o

n

f

o

r

E

d

u

c

a

t

i

o

n

a

l

U

s

e

O

n

l

y

Fig. 5 The Stop Recording toolbar.

Although the general principles of macro recording are the same in each of the programs in which it is available there are some differences between them.

• The appearance of Record Macro dialog box differs in each program to reflect that

programs abilities and requirements.

• The Stop Recording toolbar in Excel displays a button for specifying the recording of

relative or absolute cell references. In Word there is a Pause button. PowerPoint has only the Stop button.

• In Excel the word Recording is displayed in the Status Bar whilst recording is in

progress. Neither Word nor PowerPoint do this but in Word the appearance of the mouse pointer is different during recording.

The Visual Basic Toolbar

If you are a regular user of the Macro Recorder you might find it useful to display the Visual Basic Toolbar (Fig. 6) (open the View menu and choose Toolbars > Visual Basic). This toolbar carries several useful buttons saving the effort of multi-click visits to the Tools menu.

(10)

U

n

i

v

e

r

s

i

t

y

o

f

G

r

e

n

w

i

c

h

S

t

u

d

e

n

t

s

'

E

d

i

t

i

o

n

f

o

r

E

d

u

c

a

t

i

o

n

a

l

U

s

e

O

n

l

y

Recording Macros

In this section the principles of recording and editing macros a illustrated with a few simple examples.

NOTE: The VBA code recorded in the following exercises, and how to view and edit it, is explained in the section Editing Recorded Macros later in this document.

About the Exercises

My experience of training IT in a classroom has taught me t hat even the most intelligent of people can get confused when following instructions in a subject new to them. When doing an exercise from a book such a s this it can be even more confusing since individual authors have their own way of saying things. So here is an explanation of the conventions I have used for the Step-by-Step exercises in this document. Please take a moment to read it

before embarking on the exercises to make sure you understand exactly what I'm asking you to do.

Follow the instructions exactly. Read the instructions carefully and do exactly what they say. Take care when typing. Remember that the computer takes all your instructions

literally.

Things you have to do are highlighted grey. I explain and illustrate the process as the exercise proceeds so to distinguish my text from your practical instructions everything you need to do has a grey background like this.

Instructions are sequentially numbered. So that you can keep track of where you are in an exercise each step is numbered e.g. Step 1, Step 2 and so on.

"Click" means on the screen. If you have to "click" something it means with your mouse on the screen, such as a button on a dialog box e.g. Click OK or Click the OK button. Unless otherwise stated, mouse clicks are made with the primary (usually the left) mouse button. If it is required to use the secondary (usually the right) mouse button you will be asked to right-click .

"Press" means on the keyboard. If you have to "press" something it means a key on the keyboard e.g. Press [Enter] or Press the Enter key .

When Key presses or keyboard shortcuts are required special key names are enclosed in square brackets e.g. [Enter]. When combinations of key presses are required they are written thus [Control]+[Enter]. This means hold down t he Control key whilst pressing the Enter key. Function keys are indicated thus: [F8] , [F11] etc.

Choosing menu items is usually indicated by Open the Tools menu and choose Macro then Macros. Sometimes this is shortened to Go to... or Choose Tools > Macro > Macros.

If you don't understand an instruction don't panic! None of these exercises can harm Excel or your computer. I recommend that, at least to begin with, you always carry out the practice exercises in a new workbook. If things go wrong you can simply stop the exercise, throw away the workbook, and start again.

These exercises have been thoroughly checked and tested. If you find an error in one of them please let the author know about it. For contact details see: About the Author on page 52.

(11)

U

n

i

v

e

r

s

i

t

y

o

f

G

r

e

n

w

i

c

h

S

t

u

d

e

n

t

s

'

E

d

i

t

i

o

n

f

o

r

E

d

u

c

a

t

i

o

n

a

l

U

s

e

O

n

l

y

Step-by-Step: Record a Simple Macro

This simple exercise demonstrates how the Macro Recorder works. Later you will find out how you can view and modify the code it creates. Take care to follow the instructions exactly!

Step 1: Start Microsoft Excel and in a new, empty workbook type a piece of text (such as your name) in a cell. Any cell will do.

Step 2: Press [Enter] to accept your typing then click on the cell to select it. Your worksheet will look something like this:

A cell containing text is selected

Step 3: Open the Tools menu and choose Macro > Record New Macro.

Step 4: When the Record Macro dialog box appears click its OK button to dismiss it. When you do so a small toolbar with just two buttons (the Stop Recording toolbar) appears...

The Stop Recording toolbar.

Step 5: On Excel's formatting toolbar click the Bold and Center buttons, then choose a Fill Color and a Font Color.

Bold, Center, Fill Color and Font Color toolbar buttons

Step 6: Click the Stop button (the one on the left) on the Stop Recording toolbar.

The formatted cell

Step 7: Save the workbook.

This completes this Step-by-Step exercise.

The Macro Recorder recorded all your actions within E xcel from the point at which you clicked the OK button on the Record Macro dialog box until the point when you clicked the Stop button on the Stop Recording toolbar.

(12)

U

n

i

v

e

r

s

i

t

y

o

f

G

r

e

n

w

i

c

h

S

t

u

d

e

n

t

s

'

E

d

i

t

i

o

n

f

o

r

E

d

u

c

a

t

i

o

n

a

l

U

s

e

O

n

l

y

Whilst the Macro Recorder was active the word Recording was displayed in the Status Bar at the bottom of the Excel window (Fig. 7 ). If you can still see this message it means that you did not switch off the Macro Recorder – you may have closed the toolbar by mistake. If this is the case open the Tools menu and choose Macro > Stop Recording.

Fig. 7 The Status bar indicates that recording is in progress.

The action of saving the workbook in this case also saves the macro, because i n the Record Macro dialog box the option was to store the macro in This Workbook . The appropriate place to store macros is covered later (see: Where Should the Macro be Stored? on page 26). In the next exercise you test the macro by playing it back. This is called "running" the macro.

(13)

U

n

i

v

e

r

s

i

t

y

o

f

G

r

e

n

w

i

c

h

S

t

u

d

e

n

t

s

'

E

d

i

t

i

o

n

f

o

r

E

d

u

c

a

t

i

o

n

a

l

U

s

e

O

n

l

y

Step-by-Step: Test the Macro

You should always test your macros in several different circumstances to make sure they work as you intended. This exercise tests the macro you recorded in the previous section: Step 1: In the same workbook as you recorded the macro in the previous exercise, select

a block of cells and fill them with text.

HINT: A quick way to do this is to select the cells and type your text (you will see the text appear in just the "active" cell of the selection) then press [Control]+[Enter]. This is the Block Fill command and will fill all the selected cells with your typing.

Step 2: Select a different block of cells, including some of those containing the text you just entered.

Select a different block of cells

Step 3: From the Tools menu choose Macro then Macros to open the Macro dialog box. You will see the name of the macro that you recorded in the previous exercise ("Macro1"). Click on it to select it and click Run.

Select the macro name and click Run

All the selected cells have now been formatted by the macro.

Step 4: Select one of the empty cells that you had selected (you can see that the macro has correctly formatted the cell's fill color). Type some text into the cell.

This completes this Step-by-Step exercise.

The text you type appears bold, centred and coloured showing that the macro has formatted all the selected cells correctly.

The macro formatted all the selected cells even though some of the formatting was not evident until the cells contained data (Fig. 8).

Fig. 8 The result of running the macro.

Since the selection of the cells was not recorded, the macro works on any cell or cells that are selected when it is run.

(14)

U

n

i

v

e

r

s

i

t

y

o

f

G

r

e

n

w

i

c

h

S

t

u

d

e

n

t

s

'

E

d

i

t

i

o

n

f

o

r

E

d

u

c

a

t

i

o

n

a

l

U

s

e

O

n

l

y

Step-by-Step: Record and Test a More Complex Macro

The previous example was a very simple one and intended to show the principles of recording macros. This example is more complex and requires more steps. It shows how useful and time-saving a macro can be.

Acknowledgement: This macro was inspired by an example in Excel 97 Visual Basic Step by Step by Reed Jacobson (Microsoft Press ISBN: 1-57231-318-8). I used this excellent book when I started learning VBA and highly recommend it for anyone embarking on this

fascinating subject.

Fig. 9 Sample data for the More Complex Macro

The data on this worksheet contains a number of empty cells. Whilst the data is easy to read and understand, these cells must be filled before t he data can be analysed with Excel.

To you and I the figure of £1,949.00 in cell C4 clearly belongs to the South region and is part of the January data.

But as far a Excel is concerned it is simply a number sitting on its own in row 4. Excel

understands that it is probably related to the rest of the numbers in column C but there isn't any other information in the same row to tell Excel how it relates to the rest of the data.

The solution is to fill each empty cell with data from the cell above. So each empty cell under January should also contain the text January , the

empty cells under South should also contain the text South and so on.

Each row of data will then contain all the information it needs.

The macro you record in this exercise will fill the empty cells in a block of data of any size or shape using the rule t hat each empty cell should hold the same data as the first non-empty one above it.

First, prepare a worksheet containing data similar to that shown in the screenshot above (Fig. 9). You might also find it useful to copy the data on to another sheet so that you can test the macro later. Then follow these step-by-step instructions to record the macro... Step 1: Select any cell that contains some data (i.e. not an empty cell).

Step 2: Open the Tools menu and choose Macro > Record New Macro.

Step 3: In the Record Macro dialog box enter the name FillEmptyCells in the Macro name text box.

Choose Personal Macro Workbook from the Store macro in drop-down list. Type a brief description of the macro in the Description text box, for example: Fills each empty cell in a selection with data from the cell above.

(15)

U

n

i

v

e

r

s

i

t

y

o

f

G

r

e

n

w

i

c

h

S

t

u

d

e

n

t

s

'

E

d

i

t

i

o

n

f

o

r

E

d

u

c

a

t

i

o

n

a

l

U

s

e

O

n

l

y

Enter the macro options

Step 4: Click the OK button on the Record Macro dialog box to start recording the macro.

Step 5: Open the Edit menu and choose Go To. On the Go To dialog box click the button marked Special then in the Go To Special dialog choose Current Region and click OK.

This selects a rectangle of cells entirely enclosing the data.

HINT: You can quickly perform this sequence of actions by using the keyboard shortcut [Control]+* (or [Control]+[Shift]+8).

Step 6: Open the Edit menu and choose Go To. In the Go To dialog box click the button marked Special then in the Go To Special dialog choose Blanks and click OK. This creates a multiple selection of just the empty cells within the previous selection.

In the next step you will type a formula then use the Block Fill command to enter it into all the empty cells.

Step 7: Type an Equals Sign then click the Up Arrow key on your keyboard, then press [Control]+[Enter].

This has the effect of filling each selected cell with a formula which displays the value displayed in the cell above it.

Step 8: Press [Control]+[Shift]+8 to select the current region again.

Step 9: On the Edit menu choose Copy (or click the Copy button on the toolbar or press [Control]+C).

Step 10: On the Edit menu choose Paste Special then in the Paste Special dialog box choose Values and click OK.

Step 11: Press the [Escape] key to take Excel out of Copy Mode. Step 12: Click the Stop button on the Stop Recording toolbar.

Before testing the macro create another set of data containing gaps, similar to the one you used when recording the macro (NOTE: the block of data should contain no completely

empty columns or rows) or switch to t he worksheet containing a copy of your te st data if you made one). Then proceed to test the macro as follows...

Step 13: Select a non-empty cell within the data.

Step 14: From the Tools menu choose Macro then Macros to open the Macro dialog box. HINT: You can use the keyboard shortcut [Alt]+[F8] to open the Macro dialog box.

Step 15: Select the FillEmptyCells macro from the list.

Note that because this macro was stored in a different workbook (the Personal Macro Workbook) the workbook's name is shown in front of the macro name.

(16)

U

n

i

v

e

r

s

i

t

y

o

f

G

r

e

n

w

i

c

h

S

t

u

d

e

n

t

s

'

E

d

i

t

i

o

n

f

o

r

E

d

u

c

a

t

i

o

n

a

l

U

s

e

O

n

l

y

Select the macro from the list

Step 16: Click the Run button on the Macro dialog box. This concludes this Step-by-Step exercise.

If you have recorded the macro correctly, all the empty cells in the block of data will now be filled.

(17)

U

n

i

v

e

r

s

i

t

y

o

f

G

r

e

n

w

i

c

h

S

t

u

d

e

n

t

s

'

E

d

i

t

i

o

n

f

o

r

E

d

u

c

a

t

i

o

n

a

l

U

s

e

O

n

l

y

Tips for Recording Macros

Get More Familiar with Excel

You don't need to be an Excel expert to create useful and time-saving macros, but as you learn more about Excel you will be able to build more powerful macros.

The previous exercise used a number of tools that are very handy when recording macros but many users have never come across them. Particularly useful is the Go To Special command (Fig. 10) which lets you select cells according to certain predefined criteria, and the Paste Special command (Fig. 9) which allows you to specify different options for pasting copied data or objects.

Fig. 10 The Go To Special dialog. Fig. 11 The Paste Special dialog.

Plan the Macro

Even when recording simple macros it is easy to forget a step or to do things in the wrong order. Before recording a new macro it is a good idea to do a "practice run" first to

familiarize yourself with exactly what you have to do. If the macro contains many steps write a list of actions that you can follow when you are recording.

Generalize Your Commands

Try to generalize your commands wherever possible. This makes the resulting macro flexible and suitable for use in different circumstances. Commands like Go To > Special > Current Region and Go To > Special > Blanks, as used in one of the earlier exercises, are good examples of non-specific commands. If you had manually selected the block of data or the empty cells, that specific selection would have been recorded into the macro (see: Relative or Absolute References? on page 17).

Movement keys (the Up, Down, Left and Right Arrow keys) either on their own or in conjunction with the [Control] key (for moving to the end of a row or column of data) and/or the [Shift] key (for selecting cells as you move) are particularly useful in this respect.

Correcting Mistakes

Remember that the macro recorder does not record time so there is no need to hurry through a procedure when recording a macro. Take your time and try to be as accurate as possible.

Nevertheless, it is easy to make a mistake whilst recording a macro. If this happens, don’t panic! If you can immediately undo your mistake (using t he Undo button on the toolbar or with the keyboard shortcut [Control]+Z) you should do so and then carry on as normal. If you immediately undo an action this way you will probably find that t he Macro Recorder does not include it in t he final code. Sometimes it is unable to do this and your resulting

(18)

U

n

i

v

e

r

s

i

t

y

o

f

G

r

e

n

w

i

c

h

S

t

u

d

e

n

t

s

'

E

d

i

t

i

o

n

f

o

r

E

d

u

c

a

t

i

o

n

a

l

U

s

e

O

n

l

y

macro carries out your mistakes then undoes t hem just as you did when recording it . If this happens you can edit out the unnecessary lines of code later (see: Editing Recorded Macros on page 29).

Sometimes you will just get into a mess and have to stop recording and start again. When you do this, and specify the same name for the macro that you just used, Excel will ask you if you want to replace the existing macro (Fig. 12). Answer Yes to discard the original macro and replace it with the new one.

Fig. 12 Excel warns you if a macro name already exists.

Relative or Absolute References?

When recording macros it is usually advisable to generalise commands wherever possible so that they refer, for example, to the selected cell or cells rather than to a specific cell or

selection of cells. Doing this means that the macro will behave in a similar way whenever it is used and in different circumstances. But sometimes you will want to specify a particular cell (or range of cells, column, row or worksheet).

This distinction between using generalisations or specifics is referred to as using Relative or Absolute references and the principle is similar to that of writing formulas on a worksheet.

For example, if you were to write a formula in cell A1 that read: =B1+C3 and then copied that cell and pasted it on to cell D5 you would see that the formula changed automatically to =E5+F7. This is because cell references in formulas are by default relative and change when a formula is moved or copied so t hat the formula refers to the same cells relative to its new position. What the formula really says is " the cell one column to my right in the same row plus the cell two columns to my right and two rows down".

If you want the formula always to refer to the same cells wherever it occurs you must use absolute cell references. When writing formulas absolute cell references are denoted by a dollar sign before the row and/or column part of the reference. So, if you were to write a formula in cell A1 that read: =$B$1+$C$3 it would remain unchanged wherever it was copied or moved to. Often, just part of a formula needs to be "frozen" in this way so writing the formula =$B$1+C3 in cell A1 then copying it to cell D5 would result in =$B$1+F7. This formula really says "the cell B1 plus the cell two columns to my right and two rows down". The same principle applies to recording macros with an important difference. By default all cell references are recorded as absolute unless you tell Excel otherwise. If whilst recording your macro you click on, for example, cell B5 the macro will record that you want cell B5 to be selected. If, however, you have told Excel that you want to record relative references then the macro will record that you wanted to select a cell a certain number of rows and columns away from the current selection.

To determine whether or not the macro records relative or absolute references there is a button on the Stop Recording toolbar (Fig. 13). When you start recording your macro the button is not pressed in and the macro is expecting to record absolute references. If you click the button the macro will record relative references until it is clicked again. Sometimes you will want all the macro's references to be relative and sometimes only certain ones. Use the Relative References button to switch the feature on and of as required.

(19)

U

n

i

v

e

r

s

i

t

y

o

f

G

r

e

n

w

i

c

h

S

t

u

d

e

n

t

s

'

E

d

i

t

i

o

n

f

o

r

E

d

u

c

a

t

i

o

n

a

l

U

s

e

O

n

l

y

Step-by-Step: Compare Relative and Absolute References

The following exercise uses a couple of simple examples to demonstrate how this works... Step 1: Start Excel and open a new, empty workbook.

Step 2: Select cell C10.

Step 3: From the Tools menu choose Macro then Record New Macro.

Step 4: In the Record Macro dialog box enter the name AbsoluteReferences, choose to store the macro in This Workbook and then click the OK button.

Step 5: Select cell B5.

Step 6: Click the Stop button on the Stop Recording toolbar. Step 7: Select cell C10 again.

Step 8: From the Tools menu choose Macro then Record New Macro.

Step 9: In the Record Macro dialog box enter the name RelativeReferences, choose to store the macro in This Workbook and then click the OK button

Step 10: Click the Relative References button on the Stop Recording toolbar. Step 11: Select cell B5.

Step 12: Click the Stop button on the Stop Recording toolbar.

You now have two macros which recorded identical actions (the selecting of cell C5) but in different ways. The first recorded absolute references and the second recorded relative references. Now test the two macros t o see how each works...

Step 13: Select cell I15.

Step 14: Use the keyboard shortcut [Alt]+[F8] to open the Macro dialog box. Choose the AbsoluteReferences macro and click the Run button.

Which cell did the macro select? Step 15: Select cell I15 again.

Step 16: Use the keyboard shortcut [Alt]+[F8] to open the Macro dialog box. Choose the RelativeReferences macro and click the Run button.

Which cell did the macro select?

Step 17: Select cell E2 and run the AbsoluteReferences macro. Which cell did the macro select?

Step 18: Select cell E2 again and run the RelativeReferences macro. You got an error!

Step 19: Click the End button on the Microsoft Visual Basic error message box. This concludes this Step-by-Step exercise.

The way the two macros b ehaved when they were run demonstrates the difference between recording absolute and relative references.

The AbsoluteReferences macro noted the address of the cell B5 when you clicked on it so when you ran that macro it always selected cell B5.

But when you clicked on cell B5 when you were recording the RelativeReferences macro it noted the cell's location relative to the previous selection (i.e. one column to the left and five rows up) so when you ran this macro from cell I15 it selected cell H10. But when you tried to run the macro from cell E2 there was an Error . Macro errors occur when for some reason a macro can not execute a line of VBA code. The code in this macro told Excel to select a cell five rows above row 2 and since there is only one row above this row Excel couldn't do as it

(20)

U

n

i

v

e

r

s

i

t

y

o

f

G

r

e

n

w

i

c

h

S

t

u

d

e

n

t

s

'

E

d

i

t

i

o

n

f

o

r

E

d

u

c

a

t

i

o

n

a

l

U

s

e

O

n

l

y

was instructed and an error resulted. Clicking the End button aborted the macro at that point.

When writing VBA code conscientious programmers rigorously test their work in an effort to ensure that errors will not occur when the code is being used, and they include error

(21)

U

n

i

v

e

r

s

i

t

y

o

f

G

r

e

n

w

i

c

h

S

t

u

d

e

n

t

s

'

E

d

i

t

i

o

n

f

o

r

E

d

u

c

a

t

i

o

n

a

l

U

s

e

O

n

l

y

Running Macros

Different Ways to Run a Macro

There are several different ways to run a macro. Choose the method that is the most convenient to you.

The Macro Dialog

Using the macros dialog requires no prior preparation. Open the Macro dialog (Fig. 14) from the Tools menu by choosing Macro then Macros (or use the keyboard shortcut

[Alt]+[F8]) then select a macro from the list and click the Run button.

Fig. 14 Running a macro from the Macro dialog box.

The Macro dialog box lists all the macros currently available. The names of macros stored in workbooks other than the current one are prefixed by the name of their host workbook. In the illustration above (Fig. 14) you can see that the FillEmptyCells macro is located in the PERSONAL.XLS workbook (the Personal Macro Workbook ).

If there are a lot of macros in the list, you can refine it by choosing to show Macros in: All Open Workbooks (the default setting), This Workbook or PERSONAL.XLS.

Note that The Description area at the bottom of the dialog box displays any description that you might have entered in the Record Macro dialog box prior to recording the macro. If you did not add a description at the time of recording your macro you can do this later (see: Changing Macro Options on page 28).

Toolbar Button

Excel's Customize tool offers the ability to create a custom toolbar button to which you can assign a macro. Access the tool from the Tools menu by choosing Customize or right-click on any existing toolbar and choose Customize from the context menu.

In the Customize dialog box select the Commands tab then scroll down the Categories list in the left-hand list box until you find the Macros entry (it is near the bottom - the list items are not in alphabetical order!). Click on Macros to reveal a list of commands on the right-hand list box (Fig. 15 ). One of the commands is Custom Button.

(22)

U

n

i

v

e

r

s

i

t

y

o

f

G

r

e

n

w

i

c

h

S

t

u

d

e

n

t

s

'

E

d

i

t

i

o

n

f

o

r

E

d

u

c

a

t

i

o

n

a

l

U

s

e

O

n

l

y

Fig. 15 The Customize dialog box.

To add a button drag Custom Button from the dialog box to a position on one of your

toolbars then release the mouse button to place the new button on the toolbar (Fig. 16). You can rearrange and edit your toolbars and menus as much as you want when the Customize dialog is open...

Fig. 16 Placing a custom button on a toolbar.

To remove a custom button from a toolbar open the Customize dialog and simply drag the button off the toolbar. When the mouse pointer displays an X release the mouse to remove the button (Fig. 17 ). Alternatively right-click on the button you want to remove and choose Delete from the context menu.