Record Excel Macros that Work

(1)

(2)

(3)

P a g e | 3

By John Franco

IMPORTANT: As an added bonus for downloading this book, you have also access to more free Excel VBA training.

To access your bonuses go to:

(4)

P a g e | 4

How to Record Excel Macros that Work

Notice of Rights

retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher.

If you want to sell this book, use it for commercial purposes, distribute it in bulk quantities in your workplace, or a hard copy version; please Contact me

Notice of Liability

The author and publisher have made every effort to ensure the accuracy of the information herein. However, the information contained in this book is sold without warranty, either express or implied. Neither the author and

excelvba.org, nor its dealers or distributors, will be held liable for any damages to be caused either directly or indirectly by the instructions contained in this book, or by the software or hardware products described herein.

Trademark Notice

Rather than indicating every occurrence of a trademarked name as such, this book uses the names only in an editorial fashion and to the benefit of the trademark owner with no intention of infringement of the trademark.

(5)

P a g e | 5 You are enjoying a better Excel Macro book because I received the great advice and suggestions of a spontaneous/welcomed editor.

Thank you Ron S.

(6)

P a g e | 6

Purpose of this book

Hi dear Excel user,

Welcome to my book: “How to Record Excel Macros that Work: make them shorter, faster, and adaptable to variable-size arrays and workbooks”.

The purpose of this book is empowering you to delegate to the machine all the tasks that can be done by it. This way you will save tons of hours of tedious work, you will be happier and more efficient at work.

To do that, you will take full control of the recording Macro feature in Excel. After reading and practicing the concepts on this volume you will…

• Understand what a Macro is

• Understand the approach Excel uses to record Macros • Plan and record Excel Macros efficiently

• Increase your confidence to alter the VBA Macro code that is created • Integrate your Excel Macros into your workbooks with buttons, menus,

ribbons, etc.

• Get rid of fat code consistently

• Make Excel Macros usable for variable-size arrays • And more…

Keep in mind that this book does not cover Excel VBA language to its fullest. However, I guarantee that you will develop a practical Excel VBA

foundation to move to highest stages with confidence (writing Excel Macros from scratch).

Get your free Excel VBA bonus “Beyond the Excel Recorder” by visiting:

www.masterofmacros.com/blog

How to take full advantage of this book?

The book was written for Excel 2007+ users in mind, but I included Excel 2003 directions so both users groups enjoy the book.

I recommend you start from the chapter one: Introduction to Excel Macros on page 11; do it even if you are already familiar with recording Excel Macros, the reading is simple and you will refresh vital concepts. Otherwise, jump to the area that most interests you by using the detailed table of contents at the beginning on page 6 or the brief TOC below…

(9)

P a g e | 9

(10)

P a g e | 10

Brief Table of Contents

ACKNOWLEDGEMENT ...5

TABLE OF CONTENTS ...6

PURPOSE OF THIS BOOK ...8

BRIEF TABLE OF CONTENTS ... 10

1. INTRODUCTION TO EXCEL MACROS ... 11

2. WHAT IS AN EXCEL MACRO ... 14

3. HOW THE EXCEL MACRO RECORDER WORKS ... 21

4. HOW TO PLAN THE RECORDING OF AN EXCEL MACRO ... 31

5. HOW TO RECORD AN EXCEL MACRO... 41

6. HOW TO INTEGRATE AN EXCEL MACRO INTO YOUR WORKBOOKS... 54

7. HOW EXCEL VBA WORKS ... 81

8. HOW TO MAKE A MACRO MORE EFFICIENT: SHORTER, FASTER AND SMALLER ... 115

9. HOW TO MAKE AN EXCEL MACRO ADAPTABLE FOR VARIABLE-SIZED ARRAYS ... 125

10. HOW TO TROUBLESHOOT AN EXCEL MACRO ... 140

(11)

P a g e | 11 acros are for your work on Excel the same thing an Excavator for a

Building construction project; excavating a given building foundation by hand would take 12 months…

While doing it by machine, would take just 1 month…

M

=

“Computing is not about computers any more. It is

about living”

(12)

P a g e | 12 When you use manual labor, your productivity is set by your muscle power and the time you can employ your force decreases as you use it because you get bored.

Even worst…

All the important tasks (pouring concrete, raising pillars and walls, etc.) are halted during the excavation process. You cannot pour any drop of concrete until you finish the foundation hole, only occurring after 12 months.

Yes, your goal is finishing the building but you are limited to employ your force and talent on digging, for 12 bored months.

On Excel, the time you employ copying/pasting or moving cells using your mouse is manual labor. While you are doing this industrious work, you cannot employ your energy on the things that add value to your work and

organization: analyzing data, finding relationships, etc. (in other words, finishing the building).

Here is the good news…

(13)

P a g e | 13 On Excel, you are able to transform your PC from manual to machine mode.

How do you do it? By recording a Macro.

Would you excavate by hand if you know an excavator can do it?

Would you do the work with your mouse if you know the computer can do it?

Enough big pictures for now… Let’s get deeper!

(14)

P a g e | 14

2. What is an Excel Macro

Have you used a voice recording?

The main benefit of such device is that everything you spoke once can be repeated over and over again without you physically speak again. Your voice is in the tape.

You can even be heard without you been there.

The Excel Macro recorder feature is a similar device. The difference is that you store Excel actions instead of your voice. These actions can be repeated again and again without your physical intervention on Excel. For example:

• Select a given data region, insert a table, and create a Pivot Table • Select a heading of a given table and apply a particular formatting • Delete all the empty sheets

• Delete all sheets except the current one • Arrange cells in a particular layout • And more…

An audio tape is a set of recorded sounds.

A Macro is a set of recorded instructions.

What kind of instructions? Actions like: changing the color of a cell, renaming a sheet, creating a table, sorting, filtering, etc.

“All programmers are playwrights and all computers are lousy actors”

(15)

P a g e | 15 same time, your voice is recorded on a magnetic tape, while your Excel actions are recorded in a text file, specifically in a Sub procedure inside a text window called “Module”. See below…

Here is where your instructions are stored…

Your instructions are recorded here

(16)

P a g e | 16 How do you record instructions in a macro?

If you are using Excel 2007 or 2010, go to: View>Record Macro>Macro name:>Ok

Or click the status bar. See below…

The Excel 2007/2010 status bar also indicates that Excel is in recording mode…

On Excel 2003

Go to: Tools>Macro>Record New Macro

And specify the Macro configuration (name, description, etc.)…

After pressing OK, the status bar indicates that Excel is in recording mode…

While this mode is activated, EVERYTHING you do on Excel will be recorded.

This is not so good as it might appears; the same way your voice recording device registers your hesitations and background noise, the Excel recorder captures all your mistakes and incidental actions.

(17)

P a g e | 17 Now I will record a simple Macro (Macro1). Proceed as above to put Excel in

recording mode.

Want to see what’s going in the background?

Split the Excel and the Excel VBA editor windows by right clicking on the Windows task bar (arrange windows vertically).

See below the code window (Module1) with the Sub “Macro1” before I do my first action on Excel…

Tip

While Excel is on recording mode, its behavior is normal, with one exception: in the background, Excel creates a Module (Module1, Module2, Module3, ModuleN) and a Sub procedure with the name you specify on the “Record Macro” dialog. By default: Macro1, Macro2, Macro3, MacroN.

Each sub is a Macro A module is created

(18)

P a g e | 18 In other words, this is “Macro1” before I start any action on Excel.

This is “Macro1” after I select the cell A1 on Excel…

No actions recorded yet

(19)

P a g e | 19 This is “Macro1” after I select the cell A1 again and delete the content:

(20)

P a g e | 20 If you have never recorded a Macro, you have understood little of what was

written on the code windows above. Don’t worry; you will learn the Excel VBA basics on the chapter: How Excel VBA works on page 81. This way you will better interpret instructions, get rid of fat code and turn specific Macros into general ones.

(21)

P a g e | 21 he Excel macro recorder is a built-in engine that traces and stores your actions in Excel; its capabilities and features must be understood so you record efficient macros.

Additionally, you will need the Macro recorder even after you learn how

to write macros from scratch; I still use it to record simple macros and as

an Excel VBA content provider. So let’s start…

3.1. The Excel recorder registers every action you

perform on Excel

On your audio recording device; while the Record button is pressed,

everything is recorded: your voice and the background noise. On Excel; the

same thing happens; while it is in recording mode every action is recorded. These actions might be: selecting a cell, writing a formula, scrolling, formatting a cell, zooming, etc.

And every action is written on a line per line basis. Give a look…

T

“Computers have lots of memory but no

imagination”

(22)

P a g e | 22 The same way a voice recording captures your hesitations and background

noise, the Macro recorder captures everything you do, including your

mistakes and incidental actions.

Tip

Overlooked actions like: minimizing a window, adding a workbook, closing the current workbook, zooming, etc. are also recorded

Action 1 Action 2 Action 3 Action 4

(23)

P a g e | 23

3.2. The Excel recorder generates a VBA instruction

after the given action has been performed on Excel

When you record your voice, you need to talk first so the recording device captures your speech; the same thing happens with Excel Macros.

You need to perform an action on Excel (see left window below) so the Excel recorder registers it. See below…

This is one of the main shortcomings to develop smart applications. A Macro does not have flow control, it starts and ends. It has no anticipation, no decision.

Selection first

Recording later Incidental action 1.

Maximizing the window

Incidental action 4

Incidental action 2. Selecting “Sheet2” to make a visual

check of a given cell

Incidental action 3. Getting back to

(24)

P a g e | 24

The unique way to build smart applications is by improving a Macro or by writing it from scratch.

www.masterofmacros.com/blog

Let’s see the next feature…

3.3. The Excel recorder changes the “properties” of

Excel objects programmatically

At first sight the produced VBA language is Greek; nothing more far from truth.

Decode the VBA language grammar quickly by reading a short introduction to objects…

On the real world, your hair has properties: length, color, type; you can cut it, change its shape, etc. Your hair behaves in specific ways: it grows, it falls, etc.

Some properties can be changed, like the color. Yes you can apply a cosmetic treatment and turn your black hair into red, blond, etc.

Some properties are read-only, you can know them but you can’t change them. For example: your hair type: curly, straight.

Let’s summarize the features of your hair…

• A hair Property is what you hair is: length, color, etc.

• A hair Method is what you can do with your hair: cut it, change its shape • A hair Event is how it behaves: it grows, it falls, etc.

Now back to Excel…

Excel has properties and methods too, and you can manipulate them by using VBA language.

You are fully aware of the way you change properties of Excel objects, you know how to change the color of a cell, the font, the name of a sheet, etc. But you are not quite aware of how to change them programmatically; a Macro preforms that very easily. Let’s see it…

(25)

P a g e | 25

On the background, Excel translates that into VBA code. For now, imagine there is one by one correspondence between everything you do on Excel and the VBA code.

(26)

P a g e | 26 Now do this action…

It would produce the following code…

(27)

P a g e | 27

3.4. The Excel recorder uses the “methods” of Excel

Objects programmatically

The same way you change the properties, you use the methods of Excel objects when you cut a cell, delete a sheet, open a workbook, etc.

Here are some examples of how the Excel recorder uses Excel methods: The following action…

(28)

P a g e | 28 Here is another example:

This action…

Would produce this code…

3.5. Using “Relative Reference” produces different

VBA code

These actions… • Cut A1 • Select B1 • Paste

are recorded differently when you set the reference of the Macro to absolute… Cut

(29)

P a g e | 29 How do you set an absolute reference?

On Excel 2007 go to View>Macros>Click on the drop down arrow; and then…

On Excel 2003, use the “Stop Recording” toolbar (displayed in recording mode)

Now if you perform the same actions above: cut, select and paste but using relative reference, you would get a code like this one…

When you run this Macro it will select the active cell instead of

Range(“A1”)

When you run this Macro it will always select the Range(“A1”)

(30)

P a g e | 30

Using relative reference is critical if you want to create Macros that work for variable-size arrays and workbooks.

How do you set the reference to Relative? See below…

On Excel 2007 go to View>Macros>Click on the drop down arrow; and then…

On Excel 2003, use the “Stop Recording” toolbar (displayed in recording mode)

This has been all about how the Excel recorder works. Now, let me share to you everything about…

Tip

You can change the reference from absolute to relative and vice versa while you record.

(31)

P a g e | 31

Macro

ecording a Macro is very easy from the point of view of the initiation process; you launch the Record Macro command and Excel starts to record everything you do.

The Excel recorder will also record your mistakes so…

If you want to record Macros that make what you want, you need to do some extra steps. I will show them below.

But before you plan a Macro, you must answer the question…

Can this given task be done without Macros? You will know it to the extent you know the available functions and commands of Excel.

Here’s an example: not many users know how to transpose a column into a row and vice versa.

R

“Simplicity is the ultimate sophistication”

(32)

P a g e | 32 You can do this by using the Transpose option of the Paste Special command but if you don’t know this, you would intend to record a Macro.

It is not good to try to reinvent the wheel.

Overlooking available Excel commands leads you to record unnecessary Macros.

Now that you are ready to record a Macro, here’s the process to make it efficiently…

4.1. Get rid of unsystematic stuff

Imagine you have a database like the one shown below with thousands of registers in raw format on column A. All the records are separated by one row, except the first group that is separated by two rows. See below…

Tip

You will avoid recording unnecessary Macros as long as you know more about Excel available features.

(33)

P a g e | 33 The purpose of your Macro is to turn each block of data into a line (table

format). The formatting would be something like this one:

Recording a Macro in such circumstances would demand you create a Macro for two conditions. Yes you can do it, but let me ask you a question: is the

development time worth the effort? It would be better to delete that row and record a Macro for a version of the report that has one row separation between blocks of data.

Some prototyping measures include:

• Pasting all the data into the same sheet

• Leaving the same amount of space between data • Get rid of random stuff

• Any other measure to uniform the data Only this group is separated by two rows

(34)

P a g e | 34

4.2. Break the Macro into smaller chunks

Henry Ford stated that any task can be done if it is divided in enough doable parts.

Plan your keystrokes by dividing them in workable parts. You decide what is doable for you. Here are some examples:

• Formatting, editing, etc.

• Moving cells, deleting rows, writing field headers, etc. • You can be as detailed as you prefer

For the case above, you can divide the Macro in two stages: 1. Arrange data (move cells and delete the inter-block row)

And…

Tip

You can do the prototyping actions with a Macro.

Tip

Don’t try to make your Excel Macro EXCESSIVELY universal at least the benefits will pay off the development time and effort.

Delete row

(35)

P a g e | 35 Don’t worry about having separate Macros; you can run them separately or

merge them very easily. See: How to merge two or more Macros on page 50. Another idea is to perform a kind of dress rehearsal, this way you detect bottlenecks; with this information you can decide how to make the Recording Macro process more manageable.

It doesn’t hurt to try and make mistakes; given the fact that you create a backup to restore it at any time.

4.3. Define the type of Macro: specific or

generally-applicable Macro

If all your tables would have 100 rows and 5 columns, just one Macro would work all the time; but your Excel tables usually come with more or less data. Hopefully, you can record a Macro that runs on variable conditions including variable-size arrays and variable-location.

Here’s how to do define a Macro that will work for variable conditions…

Use relative reference

Imagine you want a Macro that adds field headers (Name and City) to a table. See below…

But the tables are not on the same position, see tables below…

Tip

Recording a Macro in a relaxed manner helps you to minimize incidental actions and mistakes.

(36)

P a g e | 36

The relative reference makes your Macro works based on the position of the cursor when you recorded the Macro.

Or this other way…

Choose the location of the cursor carefully so you can remember that position easily in the future when running the Macro.

How do you activate the relative reference parameter? It is very easy…

On Excel 2007/2010, go to: View>Macros>Macros You want to add

field headers here _{You want to add}

field headers here

The cursor was here before recording the

Macro

The cursor must be here before running the Macro

The cursor was here before recording the

Macro

The cursor must be here before running the Macro

(37)

P a g e | 37 On Excel 2003, activate it by using the “Stop Recording” toolbar (displayed

while you are recording):

For example: I recorded the process shown below (writing of title, date and table headings) in a Macro with relative reference…

Even I recorded the actions starting on cell “A1”, the Macro will work on any cell I run it, see below…

Activate this option

The cursor was here before I recorded the Macro

The cursor must be here before I run the Macro

(38)

P a g e | 38 Now look at how Excel records a Macro with relative references…

If you want to create the same table always at one location, whatever the position of the cursor; you need to use absolute reference…

Use absolute reference

Use this option when your Macros will be applied on the same location all the time. See below…

For example, if you record the actions that produce the table formatting below using absolute reference, the table will be always produced at that location.

Tip

Absolute or relative reference setting can be changed during the recording

Deactivate this option

The Macro does not use static ranges like Range (“A1”)

This table will be always produced at this location

(39)

P a g e | 39 See how the Macro starts…

4.4. Plan only one instance

On the example below, you want to rearrange thousands of groups of raw data into table format.

You need to plan one block because the rest is the same. The Macro is the processing of one block repeated thousands times.

Focus on having one block right, the rest is just repeating.

4.5. Finish the Macro Smartly

When you have thousands of blocks that repeat like the case shown above, you must record a block and finish the Macro at the start of the next block. In other words, the Macro will start on block 1 and will finish on the beginning of the block 2. Don’t forget to set reference to relative.

Always starts on A1

Plan this block only

These blocks are the same

(40)

P a g e | 40 If you assign a shortcut to a given Macro you can execute it thousands of times with a simple keyboard pressing. This practice is great when you don’t want to use loops or have not learned to use them yet.

4.6. Backup the file before recording

Take into account that the actions are not undoable when you are recording a Macro. In consequence, avoid headaches.

Get room for mistakes.

Use the Save As command or make a copy using the Windows Explorer. You can also make a copy of the sheet you will apply the Macro on.

Now you are ready to learn…

Tip

Record a Macro at the start of one block and finish it at the start of the next block.

(41)

P a g e | 41 ecording a Macro is a linear process, you cannot move forward/backward and you cannot undo the recorded actions. Assuming you have planned your Macro, here you will learn how to record it.

In a nutshell…

To record a Macro, proceed this way…

On Excel 2007 or 2010 go to: View>Record Macro>Macro name:>Ok or click the Macro icon at the status bar…

After you click the Ok button, Excel turns to recording mode. The status bar always indicates that Excel is in recording mode, see below…

On Excel 2003, go to: Tools>Macro>Record New Macro>Macro name:>Ok. Excel 2003 shows “Recording” at the status bar to let you know it is in recording mode…

This is just the beginning of the recording process. Let’s go into details now…

R

“Man is still the most extraordinary computer of

all”

(42)

P a g e | 42

5.1. Where to place the cursor before recording a

Macro?

This step is critical. It defines the reusability of the Macro.

The starting location of the cursor does not matter when you are recording Macros using absolute reference.

On the other hand; the location of the cursor is critical when you are recording Macros using relative reference. Before you record a Macro, place the cursor at “the start” of any given sequence of actions. Choose a place that is

reproducible later when running the macro. See below…

Always place your cursor at the right place before you launch the Record Macro command.

5.2. Where to place the cursor before stopping a

Macro?

The last position of the cursor does not matter when you are recording Macros using absolute reference.

Tip

Place your cursor at a border location you will easily remember later. Place the cursor here

(43)

P a g e | 43 cursor at “the start” of any given NEXT sequence of actions. See below…

What happens when you do this? Your Macro will move across blocks of data on each run.

5.3. How to set up a Macro

You should set all the parameters of a Macro: Name, Shortcut, Store Macro in, and Description. Here I will show you how to do it…

Use a descriptive name

You create Macros for reusing them; so a good name increases the usability of a Macro. It allows you to pick the right Macro on the dialog or on the code window.

Tip

Assign a shortcut to a Macro to execute it with ease Place the cursor here before

(44)

P a g e | 44 For example: compare these names “Macro1”, “OpenWorkbook”. See below…

You can discern what the Macro does by looking at the name. Additionally, you will remember Macros weeks or months later.

A good practice is to start a Macro name with a verb followed by the name of the object it affects (start each section in uppercase), for example:

• OpenWorkbook • CloseExcel • DeleteFormat • ChangeColorCell • ChangeColorFont Set a shortcut

This also increases the usability of a Macro. You run a Macro from two keystrokes instead of searching the Macro on the Macro dialog list.

On the Record Macro dialog, specify the shortcut key by just typing the letter you want to assign to the Macro. The shortcut key text box is case sensitive…

(45)

P a g e | 45 You can change the shortcut and description later by going to the Macro dialog and click the Options button. See below…

Define where to store the Macro

There are two options

• Storing the Macro on a workbook. This forces you to open the file to run the Macro

(46)

P a g e | 46 • Storing the Macro on a binary file workbook (PERSONAL workbook). This

enables you to run the Macro without opening any file. In other words, a given Macro will be available for all workbooks of a given Excel session

Learn more about where to store your macros on the chapter: How to Integrate an Excel Macro into your Workbooks on page 54

Set a description

You forget what a Macro does hours later, so provide a description about its purpose, and specify any detail needed for the correct use.

This is more important if the Macro will be used by other users.

The description can be edited later using the “Options…” button of the Macro dialog

5.4. How to record a generally-applicable Macro

As explained above, this must be done when you want the Macro runs on different positions from where it was recorded and for different array sizes.

6.

Learn more about how to develop adaptable Macros by going to: Make your

Macro valid for starting everywhere in the sheet on page 125.

Press the toolbar button: Use Relative References. The button is then highlighted.

(47)

P a g e | 47

5.6. How to start the recording

Assuming that your cursor is on the correct position (for generally-applicable Macros) you just need to hit the Ok button and record the actions you have planned.

Relax please, if things go wrong, you have a backup copy to try it again.

5.7. How to modify the Macro while you record

Split the windows (Excel VBA editor and Excel) so you know what lines are added as you execute movements on Excel. This way, you get awareness of the way each action is turned into VBA code.

Tip

(48)

P a g e | 48 See below…

If you commit mistakes or perform incidental actions, you can delete them “on the go” and keep recording. See below…

Take care to not delete the last Selection statement. e.g. Range(“A2”).Select ; when the next instruction depends on it.

Take care to not delete the “End Sub” statement. It is required for Excel to recognize where the macro ends.

5.8. How to stop the recording of a Macro?

Once you have performed the last operation, you are ready to stop the recording of the Macro.

Tip

The new Macro code will be always written at the bottom of your last line in the Sub procedure.

Delete these lines of code and keep recording

(49)

P a g e | 49 Additionally, you can take notes of the portions that need to be

deleted/modified and keep going.

If you are finished or you want to stop the Macro because you made huge mistakes, do it this way…

By clicking the stop icon on the status bar (Excel 2007 and 2010)

Or by going to: View>Macros>Stop Recording

On Excel 2003, the “Stop Recording” toolbar is shown automatically after you start recording. See toolbar below…

Sometimes, you close this toolbar by mistake and don’t know how to stop the recording of a Macro; you can do it by going to Tools>Macro>Stop Recording.

Or you can show the toolbar again by right clicking on the toolbar area and activate the “Stop Recording” toolbar. See below…

(50)

P a g e | 50

5.9. How to modify the code after you record a Macro

If you are not comfortable with the code being created it is time to improve it. To make even minor modifications you need at least a basic foundation of Excel VBA knowledge. See: How Excel VBA works on page 81 for a quick introduction. If the modifications are big enough, there is no alternative than record the Macro again or write it from scratch (out of the scope of this book).

www.masterofmacros.com/blog

5.10. How to merge two or more Macros

Not all Macros are recorded in one sitting; you can enhance an existing Macro or record new lines apart and add them to an existing Macro.

How do you do it?

Just open the module that contains the Sub procedure (Macro), place the cursor on the desired location and paste instructions recorded in other Macros.

(51)

P a g e | 51 Here is an example of how to merge code…

This Macro writes “Hello world” on cell A1 Open the module

(52)

P a g e | 52 This other Macro does the same on A1 of the Sheet2

This is a new Macro: Macro 1 + Macro 11

Or macro11 nested in macro1

(53)

P a g e | 53

Tip

Keep aware that the Selection statements instructions of the pasted code are corresponding to the preceding and subsequent lines.

(54)

P a g e | 54

6. How to Integrate an Excel Macro into

your Workbooks

hen you travel, bringing your camera with you is not enough; if the camera is stored on a bag difficult to open, you will lose important shots. It is better to carry the camera on your hand.

Now your Macro lies dormant in some module. It is of no value to your productivity if you cannot access it easily and at the right time.

Here you will learn:

• Where to put your recorded Macros (recorded by you or received from others)

• How to make a Macro available to all workbooks

• How to make a Macro available to the workbook where it was created To access a Macro effectively you must first know where it is located. You specify this at the moment of recording. Macros are stored in Modules (.bas). Let’s start…

6.1. How to access a Macro from the workbook it was

created

Every workbook has its own modules where Macros can be stored. All these Macros can be accessed from the given parent workbook. See below…

W

“I do not fear computers. I fear the lack of them”

(55)

P a g e | 55 How do you make a Macro available to a workbook?

You need to put it into a module of the given workbook. How do you do that?

You have three options:

1) Specify the location at the moment of recording

Set the field “Store Macro in:” to This Workbook or New Workbook. See below…

(56)

P a g e | 56 Excel creates a module (Module1 by default) with a Sub procedure with the

name “Macro13” or the name you specify on the “Macro name:” text box

2) Paste a Macro into a workbook module

You can also create your own modules and store Macros there. See below…

Tip

Changing the name of the module or the Sub won’t affect their listing on the Macro dialog.

(57)

P a g e | 57 For editing and managing purposes, it is a good practice to locate Macros in related modules with meaningful names.

(58)

P a g e | 58 Keep in mind that those macros that contain arguments are not displayed on

the Macro dialog.

You can also store your Macros on .txt files. Later, you can copy and paste the code into a module of any given workbook.

This macro (with arguments) is not listed on

(59)

P a g e | 59 Another way to bring a Macro into a workbook is by importing a module.

On the Visual basic editor, go to: File>Import File…

Or right click on the Project Explorer (CTRL + R) and choose Import File…

Once your Macro is on a module of a given workbook you can call it using several methods…

Access a Macro from the Macro Dialog

Launch the Macro dialog by pressing ALT + F8. Then, choose “Macros in:” and select This Workbook (the Macros are listed on the pane below “Macro name:”). Then select the Macro you want to run and hit Run.

(60)

P a g e | 60

Access a Macro from a shape, picture, or graph

The above method is not so good because you need to perform some clicks to run a Macro.

If you want to run a Macro with a mouse click, then you need to run Macros from graphic Excel objects like: shapes, pictures, graphs. It is very easy…. Insert any of these objects and right click over the object and then click Assign Macro. See below…

On the Assign Macro dialog, choose the desired Macro. Now your button is clickable!

Add an extra touch…

(61)

P a g e | 61 You can create fancy buttons!

Tip

You can copy and paste a button to other workbook. The path of the Macro will be kept

(62)

P a g e | 62

Access a Macro from a shortcut

Configure the shortcut when you start recording a Macro.

If you forget to specify the shortcut at the moment of the recording, you can specify a shortcut later on the Macro dialog. Do it by going to: ALT +

F8>Options>Shortcut key: See below…

(63)

P a g e | 63 opened, etc.

You can use the events of each object. Here is how you can do it…

On the Project Explorer window (CTRL + R), double click on the object for which you want to specify the Macro…

I used Sheet1 for this example; then, on the code window, choose Worksheet. See below…

Then choose the event…

Choose object

(64)

P a g e | 64 Then paste the Macro you want to be executed when Sheet1 changes…

Or you can also call the Macro that is located in some module of the current project…

6.2. How to access a Macro from any workbook

To make a Macro available to all your workbooks and from any session of Excel you must store it in the Personal workbook. The Personal workbook is a binary file (.xlsb) saved in a central location. The Macros on this workbook are visible to all books in any Excel session. See below…

(65)

P a g e | 65 How do you do that?

Specify the location at the moment of recording

Set the field “Store Macro in:” to Personal Macro Workbook. See below…

At the moment of running the Macro, it is not necessary you open the Personal Macro Workbook. See below…

(66)

P a g e | 66 Now that your given Macro is properly stored, you can…

Access a Macro from the Ribbon (Excel 2007)

Unfortunately, Excel 2007 does not allow you to customize the Ribbon. The most you can do is to personalize the Quick Access Toolbar (QAT)…

Tip

By default the location of the personal workbook is:

Windows Vista: C:\Users\user name\AppData\Local\Microsoft\Excel\XLStart Windows XP: C:\Documents and Settings\user name\Application

Data\Microsoft\Excel\XLStart

This book is automatically shown

(67)

P a g e | 67 1. Right click over any area of the Ribbon and choose “Customize Quick

Access Toolbar…”

2. Choose Macros from the “Choose commands from:” list…

3. Select the Macro you want to add to the QAT and click on the Add>> button…

(68)

P a g e | 68 4. Click on “Modify…” and assign a meaningful name:

Your QAT is ready…

Access a Macro from the Ribbon (Excel 2010)

Now that your Macro can be called from any workbook, it is a good idea to create a dedicated Ribbon.

(69)

P a g e | 69 In 2010 you MUST create a new tab to be able to add new commands and

macros. The existing tabs can only be modified to remove commands, new commands or macros cannot be added.

Create a new tab with groups and commands (Macros) by proceeding this way…

5. Right click over any area of the Ribbon and choose “Customize the Ribbon…”

6. Click on New Tab Tab

Group

Group Commands

(70)

P a g e | 70 7. Right click on the new tab and choose Rename. Assign a meaningful

name

8. Right click on the default group created and choose Rename. Assign a name. See below…

(71)

P a g e | 71 11. Right click over the recently added Macro and choose Rename. Assign a

meaningful name and icon. See below…

(72)

P a g e | 72

Access a Macro from a toolbar (Excel 2003)

Now that your Macro can be called from any workbook, it is a good idea to create a dedicated toolbar.

Just to refresh your knowledge of the old Excel toolbar system, let’s familiarize with the components again…

You can create a new toolbar with commands (Macros). Proceed this way…

1. Right click over any toolbar and choose Customize from the contextual menu.

Tip

You can export your Ribbon personal configuration and load it on any other PC.

Toolbar

(73)

P a g e | 73 2. Click on New… in the Toolbars tab and assign a meaningful name. See

below…

3. Click on Commands tab>Categories: and go to Macros. Then drag and drop a Custom Button (Commands: area) to the recently created toolbar. See below…

(74)

P a g e | 74 4. Right click over the recently created button and click “Assign Macro…”.

(75)

P a g e | 75 5. Modify the appearance of the button by right clicking on it and choosing:

Edit Button Image, Name, etc.

-

Now your new toolbar is ready

You may also place a macro command on an existing toolbar.

Access a Macro from a menu (Excel 2003)

Now that your Macro can be called from any workbook, it is a good idea to create a dedicated menu.

(76)

P a g e | 76 Just to refresh your knowledge of the old Excel menu, let’s familiarize with the components again…

Proceed this way…

1. Right click over any toolbar and choose Customize from the contextual menu.

2. Click on Commands tab>Categories: and select “New menu”. Then drag and drop the “New Menu” button (Commands: area) to the desired location in the menu area. See below…

Menu

Command

(77)

P a g e | 77 A new menu is created

3. Click on Macros and then drag and drop a “Custom Button” to the recently created menu.

(78)

P a g e | 78 4. Right click on the recently created button and then click assign Macro.

(79)

P a g e | 79 Proceed the same way as explained above in Access a Macro from a toolbar

(Excel 2003) on page 72.

You may also place a macro on an existing menu.

6.3. Where to put a Macro you receive from others

First, open the workbook that contains the Macro, go to the module and copy the procedure, then open the destination workbook and open or create a module and paste the Sub procedure.

You can also import any given module.

6.4. How to stop the execution of a Macro

You know how to run a Macro but what happens when the Macro is taking too much time or the Macro don’t do what you want?

You can stop a Macro while it is running by pressing the Esc key. And then, by pressing the End button. See below…

(80)

P a g e | 80

6.5. What to do when your Macro don’t do what you

want

If a Macro doesn’t do what you want, you have three lines of actions: • Recording it again,

• Fine-tuning it or • Writing it from scratch

Writing a Macro from scratch is out of the scope of this book but now I will show you how to improve your Excel Macros.

www.masterofmacros.com/blog

Press the End

(81)

P a g e | 81 magine you are going to spend your next vacation on Japan, you don’t

know Japanese but you equip with a 3-phrase vocabulary:

• How much is it?

• Where do I find cheap hotels?

• How can I reach the airport?

After landing, your trip is going wonderful but suddenly your little son feels sick at the zoo…How do you ask for help? The three phrases serve too little. To succeed in capricious situations, you need a wider lexicon.

Recording Macros without knowing Excel VBA language is the same situation, you soon face disorientation because you don’t know what the VBA code does and how to adapt your script to new situations like making your Macros work for variable-size arrays and workbooks.

You can only be efficient on real social environment by understanding the language grammar so you can construct new expressions as new needs arise. At the same time, to get ahead on your automation requirements you must understand the basic principles of Excel VBA so you record Macros with confidence and alter its code to suit your needs.

This book enables you to record efficient Macros and to gain a reasonable Excel VBA grammar awareness so you deal with new situations with more confidence. Let’s understand the Excel VBA language better…

I

“Those parts of the system that you can hit with a hammer (not advised) are

called hardware; those program instructions that

you can only curse at are called software.”

(82)

P a g e | 82 Have you played Mario Bros videogame?

I bet you did! If not, you are somehow familiar with it because your children play it or because you have seen some TV commercial.

Anyway…

Let’s imagine for a moment you are a skilled video game developer (a coder) and you are creating the Mario character (the tiny guy on red dress below) for a new version of the game…

As the developer, you must define his behavior and unique characteristics so players can manipulate Mario using a joystick.

So by the means of game designing tools you set all his features… You first define his properties (physical and non-physical):

• Hair color • Dress color • Height • Score

(83)

P a g e | 83 • Walk

• Run • Jump

Finally, you define the consequences of the actions Mario executes (events); for example: if an enemy kills Mario, he will die.

You cannot create a command to make it die because nobody dies by

command. Dying is a consequence of being killed, being ill, etc. The events you would create are:

• Height increase (when Mario eats a mushroom). Mario has two height modes: short (by default) and tall (after he eats a mushroom)

• Height reducing (when he is hit by an enemy while he is in tall mode) • Die (when he is hit by an enemy while he is in short mode)

Mario is ready.

He is now an object with behavior and characteristics. By using the same developing means you must specify how the properties, methods and events interact with the user. In other words, how Mario would interact when a user uses a joystick. Any given player can change Mario height property by eating a mushroom or he can make him run, jump, etc.

Let’s imagine you write the instructions in plain English…

If Mario eats a mushroom, then change the height of Mario to 5 pixels, or you can use a more structured syntax:

IF Mario eats a mushroom THEN Mario height = 5 pixels In VBA language this would be…

(84)

P a g e | 84 In Excel VBA you do it this way…

• Range("A1").ColumnWidth = 30 • Range("A1").Font.Size = 12

• Worksheets("Sheet2").Name = "Data" • Etc

Now let’s talk about the methods…

When you give instructions in real life, you provide further specifications so the given action is completed adequately. For example: bring me a hamburger without tomato, stop the car before the white line, etc.

For the above case, the arguments for the command “bring me” are: what (hamburger) and how (without tomato).

If a player uses joystick buttons to make Mario run slowly to the left, you would say in plain English…

Run Mario at slow speed to the left. In VBA language this would be… Mario.Run (slow, left)

“slow” and “left” are arguments for the Run method.

Some methods do not require any argument like: jump Mario. Using VBA language this would be…

Mario.Jump

In Excel VBA you do it this way… • Range(“A1”).Select

• Range(“A1”).ClearComments • Worksheets(“Sheet1”).Delete • Etc

Now you know the nature of objects and the rudiments of VBA syntax.

So then, what is an Excel Macro? In a nutshell…

A Macro is a set of instructions that gets/modifies properties of Excel objects and executes their methods.

(85)

P a g e | 85

7.1. VBA syntax

To give an instruction in real world you use English, to give an instruction to Excel you use VBA language.

And the syntax is as follows…

To change an object property, use the following syntax…

Objectname.PropertyNameHere = value assigned to property

To get the value of an object property, use the following syntax… Value gotten from property = Objectname. PropertyNameHere Now let me show you a real VBA syntax example…

I assign 8.5 width to the column A… Columns("A:A").ColumnWidth = 8.5

To execute an object method, use the following syntax…

Objectname.MethodNameHere (argument1 of the method, argument2 of

the method, argumentN of the method)

Here I show you an example that executes the Select method of the range "A1:D1":

Range("A1:D1").Select

Where do you find the full syntax for a particular object and their members (properties, methods and events)?

Go to: How to find Excel Objects’ properties and methods? On page 88 This has been a quick and practical introduction to Excel VBA grammar. Do you still find Excel VBA as Greek?

Tip

You cannot record Macros for object Events but you can use Excel object events when you write Macros from scratch.

(86)

P a g e | 86 You have a hard time because you try to match the VBA syntax to English

grammar. Keep in mind that languages are arbitrary assignments of meaning to symbols. Give a look at these representations of a “building” in different

languages:

Can you find a trace of a building in the words: edificio or KTÍpio? I bet you didn’t…

Human languages work because a group of people accepts a set of symbols and give them unique meaning.

So if you want to fully master Excel VBA grammar, you need to accept its arbitrariness the same way you accept the conventions of English.

7.2. Excel objects’ properties and methods

An Excel Macro is a group of instructions written in VBA language. A Macro modifies properties and executes methods of Excel objects (range, worksheet, workbook, etc.).

If you want to alter the code of a Macro to get different results, change the

way Excel modifies the properties of objects and the way it executes objects’ methods.

Tip

In English there is no a unique way to convey meaning, for example “Susan, paint the wall”, “please Susan paint the wall”, etc.

Also, in VBA language, you can convery meaning in several correct ways. Building (English)

Edificio (Spanish)

(87)

P a g e | 87 • What Excel Object property do I want to change?

• What Excel Object method do I want to execute?

Now you are wondering what properties and methods you can use to modify Excel programmatically?

Let me ask you three more questions…

• Have you changed the name of a sheet? • Have you opened a book?

• Have you delete the content of a cell?

You have answered yes to all the above questions so…you are already familiar with the behavior of many of Excel objects: Cells, Sheets, Columns, Charts, Pivot Tables, etc.

See below an outline of some common properties and methods of common objects…

(88)

P a g e | 88

7.3. How to find Excel Objects’ properties and

methods?

What happens when you learn how to modify and access properties and methods?

You acquire a key VBA competency. You need this knowledge to make

Macros do what you want.

So then if properties and methods are so important, where do I start?

Properties and methods are related to any given object so first identify the

object you want to manipulate:

• Range • Worksheet • Workbook, etc.

The names of the members (properties and methods) are in English and are meaningful. For example:

• Borders

• ClearComments • ActiveCell • SpellingOptions • MergeCells, etc.

Use these handy techniques to find properties and methods…

Record instructions in a new Macro and then copy and paste them on the Macro you are modifying

Use the Macro recorder as a VBA code provider.

As simple as it sounds, if you want to add a line of code that write a formula in a given cell, record that action in a Macro and then copy and paste the

produced code.

If you want to change the font of a cell, record that action and then copy and paste that code.

Get assistance from the “Auto List Members” command while you write

I love this way of knowing the properties and methods.

Avoid writing code from scratch, type CTRL + J to launch the “Auto List

(89)

P a g e | 89 See below…

What is shown on the “Auto List Members” list?

You see the available members at the current slot. See below…

Use the up/down arrows to navigate through members and the Tab/Enter key to accept a choice.

The Auto List member feature is automatically displayed after entering a point

So you can choose the desired property or method…

Methods for Range(“A1”)

Properties for Range(“A1”) Press CTRL + J while

(90)

P a g e | 90 And complete the expression…

For any given method, its arguments are provided after you type the space bar or write a parenthesis…

Use the VBA editor Help section

For Excel 2007 users…

While you are on the VBA editor, press F1 or go to the Help menu. Then choose: Excel Object Model Reference, then pick the object and then browse the members list…

Tip

Identify object methods with this symbol Identify object properties with this symbol Identify object events with this symbol

(91)

P a g e | 91 For Excel 2003 users…

While you are on the VBA editor, press F1 or go to the Help menu. Then type the name of the object on the search box…

And then choose the desired topic from the results… Model 2) Pick the object 3) Pick members 4) Browse the members

(92)

P a g e | 92 Or you can use the help to navigate through…

Use the Object browser

The object browser is a pane that contains all the objects and their members organized.

While you are on the Visual Basic editor, press F2 to launch it. Then, choose the VBA library…

(93)

P a g e | 93 After that, pick any given object on the left pane and explore its members

(properties, methods and events) on the right pane…

You can copy the object’s syntax from the pane and paste it on any given code window. Use right click or CTRL + C. See below…

Use the Local Windows

This is a more advanced technique… Create this Macro:

Objects

Members: properties, methods and events

(94)

P a g e | 94 And then, to the right of the (=) sign, write the object you want to know more about, for example: a Range object…Now execute the Macro step by step this way:

1. Place the cursor inside the “storeobject” procedure and then

2. Press F8, each press will run a line, do it until you execute the “Set myobject” line.

3. Then launch the local windows by going to View>Locals Window and explore the members. You can change the properties and see the impact on Excel. Split vertically the VBA editor and the Excel windows for better understanding

The above methods are great to get the full syntax and help about any object property and method.

If you want to go deeper in the Excel Objects universe, come with me, I will show you the…

Click the object to see its

(95)

P a g e | 95 of an application. Now, you will create a new frame of mind to understand this concept.

Imagine the earth is composed of one surface and many trees…

Now imagine for a moment you are the creator of this earth. The inventory of everything on it would be this one…

Now, imagine you give a command to a human to collect the fruit 1 from the trees.

The human would ask you: the fruit 1 of which tree?

So you need to narrow down your instruction. You would say, bring me the fruit 1 of the tree 2. Surface Trees Fruits 1 ₂ 1 2 3 1 2

(96)

P a g e | 96 Now the human is enabled to follow your instruction.

To identify a fruit you must first identify the tree in the collection of trees. Only after that the fruit “Id” makes sense. In other words, the collection of fruits is associated to a tree of the collection of trees.

Let’s talk more about collections…

The earth has three collections (notice the “s” at the end, collections are always plural):

• Trees • Branches • Fruits

The collection of trees would be something like this one…

The collection of fruits would be something like this one…

(97)

P a g e | 97 But associated to any given tree…

(98)

P a g e | 98 And some objects don’t belong to any collection but they also has properties

and methods…

An object model is the hierarchy of collections and objects of a whole system. Here you have a detailed object model of the earth…

(99)

(100)

P a g e | 100 Here is a summarized Excel Object model…

So…

In Excel to access a range object, you must specify which one from which worksheet and from which workbook.

(101)

P a g e | 101 While you are working on Tree1, you can refer to Fruit1, Fruit2, etc. of that tree without additional identification.

On Excel, you don’t need to use the workbook/sheet qualifier all the time. For example, while the Sheet1 is active, the following code write “Hello world” on the cell A1 of that sheet.

But, if you want to write “Hello world” on the cell A1 of the Sheet2 (while Sheet1 is active), you must write the sheet qualifier. See below…

Now let’s explore the…

7.5. Popular Excel VBA Objects

Here’s a list of the most prominent Excel objects and their most used members (properties and methods).

This list is not exhaustive (additionally, I have not included events).

Range

Here is the list of the 57 (out of 97) most used Range Properties:

Name Description1

Activate Activates a single cell, which must be inside the current selection. To select a range of cells, use the Select method. AddComment Adds a comment to the range.

Address Returns a String value that represents the range reference in the language of the Macro. AutoFilter Filters a list using the AutoFilter.

AutoFit Changes the width of the columns in the range or the height of the rows in the range to achieve the best fit. BorderAround Adds a border to a range and sets the Color, LineStyle, and Weight properties for the new border. Variant.

(102)

P a g e | 102

Borders Returns a Borders collection that represents the borders of a style or a range of cells (including a range defined as part of a conditional format). Cells Returns a Range object that represents the cells in the specified range. Clear Clears the entire object.

ClearComments Clears all cell comments from the specified range. ClearContents Clears the formulas from the range.

ClearFormats Clears the formatting of the object.

ClearHyperlinks The description for this item will appear in the final release of Office 14. Column Returns the number of the first column in the first area in the specified range. Read-only Long. Columns Returns a Range object that represents the columns in the specified range. Comment Returns a Comment object that represents the comment associated with the cell in the upper-left corner of the range. Copy Copies the range to the specified range or to the Clipboard.

Count Returns a Long value that represents the number of objects in the collection.

CurrentRegion

Returns a Range object that represents the current region. The current region is a range bounded by any combination of blank rows and blank columns. Read-only.

Cut Cuts the object to the Clipboard or pastes it into a specified destination. Delete Deletes the object.

End

Returns a Range object that represents the cell at the end of the region that contains the source range. Equivalent to pressing END+UP ARROW, END+DOWN ARROW, END+LEFT ARROW, or END+RIGHT ARROW. Read-only Range object.

Find Finds specific information in a range.

FindNext

Continues a search that was begun with the Find method. Finds the next cell that matches those same conditions and returns a Range object that represents that cell. Doesn’t affect the selection or the active cell. Font Returns a Font object that represents the font of the specified object. FormatConditions Returns a FormatConditions collection that represents all the conditional formats for the specified range. Read-only. Formula Returns or sets a Variant value that represents the object's formula in A1-style notation and in the language of the Macro.

FormulaArray

Returns or sets the array formula of a range. Returns (or can be set to) a single formula or a Visual Basic array. If the specified range doesn't contain an array formula, this property returns null. Read/write Variant. FormulaLocal Returns or sets the formula for the object, using A1-style references in the language of the user. Read/write Variant.

Record Excel Macros that Work

Table of contents

Purpose of this book

www.masterofmacros.com/blog

Brief Table of Contents

M

=

2. What is an Excel Macro

3.1.

The Excel recorder registers every action you

perform on Excel

T

3.2.

The Excel recorder generates a VBA instruction

after the given action has been performed on Excel

www.masterofmacros.com/blog

3.3.

The Excel recorder changes the “properties” of

Excel objects programmatically

3.4.

The Excel recorder uses the “methods” of Excel

Objects programmatically

3.5.

Using “Relative Reference” produces different

VBA code

Macro

R

4.1.

Get rid of unsystematic stuff

4.2.

Break the Macro into smaller chunks

4.3.

Define the type of Macro: specific or

generally-applicable Macro

4.4.

Plan only one instance

4.5.

Finish the Macro Smartly

4.6.

Backup the file before recording

R

5.1.

Where to place the cursor before recording a

Macro?

5.2.

Where to place the cursor before stopping a

Macro?

5.3.

How to set up a Macro

5.4.

How to record a generally-applicable Macro

6.

5.6.

How to start the recording

5.7.

How to modify the Macro while you record

5.8.

How to stop the recording of a Macro?

5.9.

How to modify the code after you record a Macro

www.masterofmacros.com/blog

5.10. How to merge two or more Macros

6. How to Integrate an Excel Macro into

your Workbooks

6.1.

How to access a Macro from the workbook it was

created

W

6.2.

How to access a Macro from any workbook

6.3.

Where to put a Macro you receive from others

6.4.

How to stop the execution of a Macro

6.5.

What to do when your Macro don’t do what you

want

www.masterofmacros.com/blog

I

7.1.