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.
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”
John F. Kennedy
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 before recording
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 stopping the Macro
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…
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
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.
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
You can change reference setting while recording.
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
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…
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).
Get your free Excel VBA bonus “Beyond the Excel Recorder” by visiting:
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.
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
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
You can also paste code to the Macro while recording.
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.
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”
Isaac Asimov
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…
What happens when you use this option?
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.
P a g e | 57 For editing and managing purposes, it is a good practice to locate Macros in related modules with meaningful names.
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
the Macro dialog
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.
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…
Right click over the button and choose “Edit text”…
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
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…
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
Choose event
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…
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…
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
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…
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.
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
Commands
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…
9. Choose Macros from the “Choose commands from:” list…
P a g e | 71 11. Right click over the recently added Macro and choose Rename. Assign a
meaningful name and icon. See below…
Your new Tab is ready to use.
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
Commands
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…
P a g e | 74 4. Right click over the recently created button and click “Assign Macro…”.
Choose the Macro to link to the button.
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.
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
Command
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.
P a g e | 78 4. Right click on the recently created button and then click assign Macro.
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…
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.
Get your free Excel VBA bonus “Beyond the Excel Recorder” by visiting:
www.masterofmacros.com/blog
Press the End button
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
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