Excel Macros Handout - Prospects

Excel Macros

1

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

Excel Macros

2

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

Excel Macros

3

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

Excel Macros

4

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

Excel Macros

5

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.

Excel Macros

6

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)

Excel Macros

7

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

Excel Macros

8

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

Excel Macros

9

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”

Excel Macros

10

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

Excel Macros

11

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

Excel Macros

12

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

Excel Macros

13

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

Excel Macros

14

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)

Excel Macros

15

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…

Excel Macros

16

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

Excel Macros

17

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

Excel Macros

18

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!”)

Excel Macros

19

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

Excel Macros

20

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

Excel Macros

21

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

Excel Macros

22

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

Excel Macros

23

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

Excel Macros

24

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

Excel Macros

25

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?