1Excel Macros

Excel Macros

Dave Goody

Course Rules

• Enjoy yourselves !!!

• Ask questions

• Take part, interact and make the course your own

• Coffee and comfort break around halfway through the course

2Excel Macros

What is a Macro?• A Macro is a piece of code that enables the same

processing or key-strokes to be repeated

When to use a Macro?• If you are creating a one-off spreadsheet that will be

discarded it is unlikely that there will be any benefit from using a Macro, but if the spreadsheet will be returned to and contains repetition then obviously it is worth thinking about using Macros

• This is particularly the case if the spreadsheet will be used by different people as a Macro can also reduce human error

3Excel Macros

Your first Macro• To help you to understand Macros a little better, we will

set up a very simple one

• This will introduce us to Recording Macros, which is the simplest way of creating a Macro

• Use the example workbook “Excel Macros”, which is on your desktop

Your first Macro• To create a new Macro, select Tools, Macro, Record

New Macro to display the Record Macro dialog box

4Excel Macros

Your first Macro• Enter a name for the Macro, for example “Filter”

Your first Macro• As you can see, you can specify a shortcut key to run

this Macro should you wish to

• It is best to avoid using Ctrl+ anything as this can easily interfere with Windows shortcuts, but by pressing shift and then a keyboard letter this potential ambiguity is avoided

• Just to keep everyone the same we'll use Ctrl+Shift+F

5Excel Macros

Your first Macro• It is best to store the Macro in "This Workbook" so that if

you send someone the Workbook they also get the Macro

• It can be a good idea to have your own Workbook that only contains Macros like a sort of Macro library, then if you want to re-use one just copy it to the Workbook that you are currently building

Your first Macro• Hit "OK"

• This now brings up the Macro Recording Form to confirm that you are currently in “record” mode. "Recording" should also be displayed in the status bar.

6Excel Macros

Your first Macro• Now, turn on the AutoFilter by selecting Data, Filter,

AutoFilter

• Now, stop the recording by clicking the “Stop” button on the Macro Recording Form

Your first Macro• You can now try running your Macro

• This can be done in two ways

• By using your specified shortcut (Ctrl+Shift+F)

7Excel Macros

Your first Macro• By selecting Tools, Macros, then selecting the “Filter”

macros and clicking Run

Running Macros• As well as running Macros as shown before, they can

also be run in various other ways

• From a Button

• Select View, Toolbars, Forms

8Excel Macros

Running Macros• Click on the Button icon and then click on the workbook

to place the button

• Select “Filter” and click OK

Running Macros• From a Drawing

• Select View, Toolbars, Drawing (if necessary)

• Place any drawing on the workbook

• Right-click the drawing, then choose Assign Macro

9Excel Macros

Running Macros• Select “Filter” and click OK• You can now run the Macro by clicking on the drawing

The VB Editor• To access the VB editor, select Tools, Macro, Macros,

then select a Macro and click “Edit”

10Excel Macros

The VB Editor• To access the VB editor, select Tools, Macro, Macros,

then select a Macro and click “Edit”

The VB Editor• Comments start with an apostrophe (‘)

• Keywords are in blue

• Code is in black

• Errors are in red

11Excel Macros

The VB Editor• Objects, Properties and Methods

• An Object is an object, e.g. a Table

• A Property is something about an object that tells you about it, e.g. width

• A Method is an action you can apply to an object, e.g. Wipe

The VB Editor• Applying a Method to an Object is done as follows:-

• Object.Method

• e.g. Wipe the table is done by: Table.Wipe

• The value of the Property of an Object is set in a similar way

• Object.Property = value

• e.g. To set the width of Object Table to 10 is done by: Table.Width = 10

12Excel Macros

The VB Editor• Objects can have objects within them; this is known as a

collection

• e.g. Table.leg.height = 5

• where Leg is an Object within the Table Object

• or even Table.leg(1).height = 5

• where this is leg 1 of the Table Object

• To explain this further, lets create another Macro

The VB Editor• Create a Macro to turn the spreadsheet Gridlines on and

off (remember how to record a Macro?)

• This is done by selecting Tools, Options, View, Gridlines

13Excel Macros

The VB Editor• Once you have created your Macro and checked that it

works, have a look at the code (Tools, Macro, Macros,select the Macro and click “Edit”)

• You can see from this that DisplayGridlines is a Property of ActiveWindow

The VB Editor• Unfortunately, you may notice that our Macro only turns

the Gridlines off, but not back on again

• To do this, we will need to add an “if” condition and test the current value of the Property

• Try running your new Macro

14Excel Macros

The VB Editor• We are now going to create a Macro that enters values

into a cell

• Record a Macro named “Name”, click on cell A13 and enter your name

• Stop recording and have a look at the code

The VB Editor• To make this Macro work anywhere, remove the line

• Range(“A13”).Select

• which positions the Active Cell

• We are now going to create a Macro by entering the code, rather than recording it

• If your VB editor is not active, start it now (Alt+F11 is the shortcut)

15Excel Macros

The VB Editor• Enter the code as follows:-

• This sets the font name and size, but saves doing

• Selection.font.name = “letter gothic 12”

• Selection.font.size = 15.5

• Try running your new Macros

The VB Editor• A Macro can also be made to prompt the User for input

• From within the VB Editor, create a new Macro “InputName” with the following code:-

• When you run this Macro, you should see…

16Excel Macros

Moving Around Within The Spreadsheet• There are two ways of referencing cells within a

spreadsheet…

• Absolute Referencing

• Relative Referencing

• Record a new Macro called “Absolute” and click on cell A1.

• The Macro you have created should contain the line

• When this Macro is run, the cursor goes to cell A1

Moving Around Within The Spreadsheet• Now, record a Macro named “Relative”, but before doing

anything, click the “Relative Referencing” button on the Macro Form

• Now, click on the cell one below the current active cell, then click the “Relative Referencing” button again, followed by “Stop”

• Now have a look at the Macro code in the VB Editor

17Excel Macros

Moving Around Within The Spreadsheet• Your code should look something like this

• “ActiveCell.offset(1,0)” moves the cursor down one line and across zero lines

• In this instance, “Range(“A1”)” does nothing, as we are using Relative Referencing – it can be left out

• Negative values can be used to navigate up and to the left on the spreadsheet

Moving Around Within The Spreadsheet• By using a simple loop, along with relative referencing,

we can create a Macro to find the next available free cell in a column

• This Macro starts in the current cell and moves down one line at a time until an empty cell is located

18Excel Macros

Moving Around Within The Spreadsheet• We can now combine two of our Macros together, by

running the FindFree Macro, then the InputName Macro

• This finds the first free cell in the current column, then prompts for a name to be entered

Error Handling• We should ensure that if an error occurs when running a

macro, we handle it rather than letting the macro crash and grind to a halt

• On your “Filter” macro, enter a new first line

• On error goto MyError

• Then add the following before the End Sub

• Exit Sub

• MyError:

• MSGBox(“Pick a cell!”)

19Excel Macros

Error Handling• Try selecting a range of cells outside the data entered,

and run the “Filter” macro

• Rather than getting an error message, you should see the message you have specified

Auto Open and Auto Close Macros• There are two reserved macros that run when a

spreadsheet is opened and when it is closed

• These can be used to varying effects – these two examples are not particularly useful, but they should give you the idea of what can be achieved

• WARNING – These macros can be used by people who write viruses, as they run automatically when opening a macro

• This is why there are macro security settings within Excel, which often stop macros being run

20Excel Macros

Auto Open and Auto Close Macros• On Sheet2 of your workbook, add a shape and name it

“message” (use the Name Box)

• Then add this new macro via the VB Editor

Auto Open and Auto Close Macros• Also, enter this new macro

• Save your workbook, close it and then re-open it

21Excel Macros

Functions• The VB Editor also gives you the opportunity to write

your own functions

• For instance, you could write one that converts Centigrade to Fahrenheit, or perhaps to do currency conversion

• In the VB Editor, enter the following…

Functions• To use your new function, enter a value into a cell and

then enter your function into another

22Excel Macros

Functions• Functions can have more than one variable passed to

them

• Try creating another function called Exchange

Functions• You run this in the same way as the Fahrenheit function,

but you pass it two variables

23Excel Macros

Further Message Box Options• The MsgBox can be used to get a response back from

the User

Further Message Box Options• Look at the various components of your code when the

box is displayed

24Excel Macros

Further Message Box Options• There are many options available for the MsgBox

command – it is best to check the Excel Help to see what you can do with it

• Position the cursor on the MsgBox command in the macro code and press F1

Multiple Cell Selection• If you wish to run a macro against a range or cells, you

need to use a Dim command (dimension)

• Enter some numbers into a range of cells, select the cells and then run your new macro

25Excel Macros

Multiple Cell Selection• When you run the macro, each cell in the selection has

the macro applied to it

The End

Thank you for your time any attention. Any questions?