Automating Tasks with the Macro Recorder - First Steps
Excel (and most other Office products) come bundled with what’s called Visual Basic for Applications (VBA). It’s a subset of the powerful Visual Studio.NET programming language that Microsoft uses to write Office applications and they allow us to access/harness some of that power through VBA. Most other product providers don’t let you do that, which is one of the reasons that Excel is so much more powerful than other applications. For instance, Google Spreadsheets have no internal programming capability, so you can’t automate any tasks, while Lotus 1-2-3 only gives you the ability to record keystrokes. In that sense you have more automation ability with Lotus than Google, but it is strictly limited to what you can do within the Lotus interface itself; you can’t get behind the scenes like you can with Excel, and it can make a huge difference to your daily work.
Case in Point: some time ago, when I first joined a business unit in San Diego, our assistant spent 8+ hours every Monday calling our satellite offices for their weekly sales numbers (by sales rep), which they then read to her over the phone and she entered into a weekly leaderboard report in Excel. We had a computer order entry system, so realizing that the data had to exist in an electronic environment, I was able to set up a simple query from the system and then automatically populate a similar report in Excel using VBA. What used to take 8 hours all of a sudden took about 8 minutes (including the time it took me to get a cup of coffee). I was further able to automate tasks to the point where I could essentially take a day or two off each week because of how much time I saved at repetitive tasks (somehow I never did though).
I didn’t start out as a programmer, I’m not one now, and I don’t expect you to end up as one (unless you find it to be greatly beneficial to you, in which case you just might), but I will introduce you to how to start automatiing to your everyday work by recording macros.
NOTES: this article is written for Excel 2007 and its Ribbon Interface. The examples you see were created in Excel 2007. To follow the steps you would take in Excel to recreate the steps in the article I use the “Goto this, then” symbol: –>. I.E. goto the Home Button–>Open, as in goto the Home button, then select the Open option. As I’m a keyboard kind of guy I’ll also let you know the keyboard shortcuts, which will be notated in this style /avv, which means hit the forward slash key (it’s interchangeable with ALT, so you have your choice), then the keys listed, in this case “a”, then “v”, then “v”.
The Macro Recorder (is your friend)
Excel (and most other Office Programs) have a tool called the Macro Recorder, and you’ll also want to know that it’s probably your biggest friend as you start the foray into trying to automate your daily life. If you’re not familiar with it, here’s a homework assignment you can do right now and then come back (note that VBA procedures are generally undoable without preparation, so make sure you try any code, sample or recorded, on a TEST workbook): I want you to think of a fairly repetitve task you undertake and goto the Developer tab, then Record Macro (/lr).
When you first start recording a macro you’ll get the following dialog box:
Record Macro Dialog
I’ll get to the options you have here in a minute, so go ahead and hit OK for now.
Now go ahead and start doing what it is that you normally do for this particular task. When you’re done, hit the Stop Recording button (/lr). Now you can go to another worksheet and if you’re still on the Developer tab hit the Macros button (/lrpm), which will bring up a list of all the public macros stored in your workbook (public means that you can see and call the macros from the Excel side of the world). Select the one you just recorded (it will probably be called “Macro1″), then hit the Run button and see what happens.
Macro Dialog List
VBA will now repeat what you recorded on the other sheet. Pretty cool huh?
I have to note that the Macro Recorder is just that, a recorder, so it literally records everything that you do. If you make a mistake and undo it, that will be recorded and repeated when you run the macro again. It will also record all of your screen navigation, like moving from sheet to sheet or even scrolling up, down, across, etc., so while it’s a great tool, it can also be pretty inefficient as you (and your end users will see that activity - there are ways to get around it, but that’s another topic). On the upside, it will also record the correct file path/name of a workbook, query, external database that you might be trying to access, which you might (would) probably otherwise screw up on your own. I use the recorder all the time (as do many MVP’s) for just this reason. Remember though, that you’re just telling a computer to do what you tell it to do. One of my favorite quotes from fellow MVP, Ken Puls, is: “I hate it when my computer does what I tell it to do vs. what I want it to do!” Recorded macros lack lack the intuition that you can add yourself within VBA.
So now you know a bit about the macro recorder, and frankly some people never move past it, as it’s enough for them and that’s fine. In fact, I know many people who are Finance professionals who never get past this point and it’s not a problem (I also know quite a few “Excel Professionals” who have never even made it to this step!)
Now I’ll move onto the options that you have when you begin recording your Macro(s).
Record Macro Options
Going back to the Record Macro dialog there are four options you have:
- Record Macro Dialog
1) Macro Name - You have the option to name the macro whatever you want, although you need to follow some naming conventions (no spaces or special characters). You’ll know if you entered an invalid name as Excel will let you know and you’ll have to try again. I generally try to give my macros a fairly intuitive name, like “FormatSheet”, because if you start compiling a bunch of macros how do you know which one to choose? Believe me, I’ve been tricked by ambiguous macro names and had to rebuild a workbook because of it. By default VBA will name your macro “Macro1″, and each macro you record after that will be named in succcession (Macro2, Macro3, etc .)
2) Shortcut Key - Next you have the option of assigning what’s called a Shortcut key, which will allow you to call a macro with a keyboard shortcut instead of initiating the macro dialog list (I’ll explain how to call your macro(s) without shortcut keys in a minute).
Note that shortcut keys are activated with the CTRL key, so you don’t want to use reserved keys, like P, otherwise you’ll overrride your abilility to print with that shortcut (the same goes for C-Copy, V-Paste, X-Cut, and most importantly Z-Undo - You don’t want to disable that one! And if you do overwrite native commands you have to give them back, neither Excel nor VBA will do it for you, so be careful!)
With that in mind, you can also take advantage of the Shift key as an additonal step. To do this when you’re ready to assign the shortcut, hold down the Shift key and that will create a Ctrl+Shift+Key sequence (this is generally the route that I choose so I avoid any mistakes).
3) Store macro in - What’s that? You have a few options when you record a macro of where you want to put it (it’s one of those behind the scenes kind of things). If you click the arrow button to the right you’ll see that you can put your recorded macro in “Personal Macro Workbook”, “New Workbook”, or “This Workbook”. You’ll generally want to choose to the “This Workbook” option, which will, as it suggests, store your macro in the workbook you’re recording the macro. The “Personal Macro Workbook” option allows you to make a macro available to any open workbook (but that’s a topic for another discussion), and the “New Workbook” option is something I’ve never really had to use, so I’d ignore it and just stick with the default (note that if you do select something other than “This Workbook” the recorder will stick to that selecection for any other macros that you record, so you’ll need to be mindful of that, and switch it back if you have changed it…I’ve had a lot of macros “disappear”, because I put them in the wrong place).
4) Description - This is where you can add comments to your macro, so that when you open the Macros button from the Developer tab it will tell you what the code does (or at least is supposed to do…)
Here is an example of recording a macro and naming it yourself, assigning a Ctrl+Shift+Key combination, then adding comments:
Record Macro Dialog
5) TheFinal Step - Calling the Macro and putting it to use…Now, from the developer tab you can click the Macros button (/lpm), and you’ll get a dialog box that lists all of the macros that you have available:
Macro Dialog List - with Comments
Just so you have an idea of what multiple selections look like, here’s what my options look like when I do it (I keep several macros in my Personal.xls):
Smitty's Macro List
Here you have several options:
1) Run - The most common command, which will Run your code.
2) Step Into - This is for more advanced use and allows you to actually step into the code with the VBA environment for testing and debugging. Go ahead and try it if you’re interested (ALT+Q will exit the VB Editor and take you back to Excel). If you choose this option, the F5 key will execute your code immediately and F8 will let you run it one line at a time.
3) Edit - Ditto. This will take you directly to your macro’s code. The same holds true with F5/F8 here.
4) Create - I’m not sure why Microsoft left this option here, as I’ve never used it (or seen it enabled for that matter).
5) Delete - Does what it says, so be careful. There’s no Recycle Bin from which to retrieve a deleted macro.
6) Options - This gives you the chance to add a shortcut key and comments if you didn’t choose to do so when you first recorded your macro.
Now you know all you need to know to start recording your own macros and begin automating some of what you do everyday. Bear in mind that as I mentioned earlier that the recorder is just that and nothing more. Once you get the hang of recording some macros, you’ll start to wonder if you can do other things that the recorder just can’t capture. The good news is that you can with some practice. The best thing you can do if you want to continue past what the recorder is to start understanding the VBA code itself. Anytime you want you can select Edit from the Macros List and take a look a the code. Let’s say you record a macro that applies some formatting; when you look at the code you should be able to see what the code is doing, based on the steps you took when recording it. It’ll look a lot like Greek at first, but you’ll get the hang of it pretty quickly.
In my next post, I’ll discuss how to step beyond the recorder and begin editing your own code, then how to even start writing your own, which is when things really start to get fun and a whole new world of automation opportunity begins.
Good luck and most of all have fun!
[...] Excel (and most other Office Programs) have a tool called the Macro Recorder, and you’ll also want to know that it’s probably your biggest friend as you start the foray into trying to automate your daily life. Read more [...]
[...] SmittyPro » Automating Tasks with the Macro Recorder - First StepsI.E. goto the Home Button–>Open, as in goto the Home button, then select the Open option. As I’m a keyboard kind of guy I’ll also let you know the keyboard shortcuts, which will be notated in this style /avv, which means hit the forward … [...]
Hi, nice post. I have been wondering about this issue,so thanks for sharing. I’ll definitely be coming back to your posts.
Everything dynamic and very positively!
I’m glad it helped out! That’s the whole point. If there’s something else you’d like to see, please let me know.
Smitty
Visual Basics is an area I am interested in and would like more tutorials on. You are doing a good job
Thanks, I’m working on more VBA tutorials and should have them upp by next week. Sorry for the delay, but I’ve been buried at work…
My question is, can I, after a Data validation list has been created in an Excel document program it to automatically populate the box with one of my pre-defined items when I type the first letter??
Not with Data Validation, but if you use a Combo Box from the Control Toolbox you’ll get that functionality. In the Combo Box’s properties you can add your Data Validation range either the actual range or a named range in the ListFillRange box(i.e. ListFillRange–>MyList).
I am trying to copy info from one cell, paste into the next and delete the first 6 characters and/or “word” but when I record/play - on all consecutive lines it pasts the info from the first cell I copied; i.e.;
12345 user name
I want to copy that to the next cell & past I have tried copying the entire cell and just the user name with the same results - suggestions?
This is a very good article and the information of this article is useful…………….
cross-linkz
Hey Smitty.. I found this article really useful. Thanks a lot! Macros aren’t so scary after all..
Would love some more guidance on VB as it all looks very greek to me at the moment.. :S
Thanks! I’ll hopefully be putting up another VB article this weekend about the different types of code modules and what you can do with them. You might also want to check out Martin Green’s VBA tutorials at http://www.fontstuff.com