Creating Excel Macros, with Gail Perry, CPA
Friday, March 2, 2001
Visit the AccountingWEB Workshop Calendar for upcoming sessions.
Learn how to create basic macros in Excel during this live workshop presentation. Topics included:
- What's a macro?
- Recording simple macros
- Editing macros
- Learning Visual Basic code
- Creating basic macros in Visual Basic
Read the complete transcript!
March 2, 2001 Session Sponsored by Practitioners Publishing Company
Gail Perry: As many of you know, I'm the managing editor here at AccountingWEB. I'm also a CPA and the author of the book, Excel 2000 Answers!, published last year by Osborne/McGraw-Hill.
I've taught computer classes to accountants and their clients since 1985 and began teaching Excel in the early 90s when people started vacillating from Lotus and WordPerfect to the Microsoft products.
I'd like to take a quick survey before we begin - please note your level of experience from these six choices below (wish we could just do a show of hands!):
(1) You aren't sure what a macro is or what it can do for you,
(2) You know what a macro is but have never used one
(3) You have used macros created by others,
(4) You have created macros by recording your actions,
(5) You have created macros by copying and pasting visual basic code,
(6) You have created macros by creating original visual basic code.
Corey Warawa: 6
Lewis Baum: 3
Jade Leung: 3
David Crites: 3
Jessie DeWire: 4
Pat Mellican: 3
Barbara Rick: 4- but it was so long ago I can't remember how
Barbara Rick: Liger Needom: 3
Gail Perry: OK - it looks like everyone has at least a bit of experience with macros
Gail Perry: First, what is a macro? Does anyone have any good definitions to share?
Barbara Rick: It's a quick way of doing something.
Lewis Baum: A memorized sequence of events activated by one command.
Gail Perry: Very good!
I like to think of a macro as a little robot, created to do a particular task flawlessly, over and over again. (The flawless part of course only occurs when you have written the macro correctly!)
In its most basic form, macros are recorded versions of steps that you perform yourself, using the keyboard, the menu choices, and the toolbar buttons. All Excel macros are prepared using Visual Basic code - when you record a macro, the code is written in the background and you never have to see it.
For those of you who may have learned to create macros in Lotus, this represents a big change. The concept of writing macros in Excel requires an entirely different mind set from that which you used working with Lotus. Making the transition from Lotus to Excel macros is probably the most difficult part of working with Excel macros. Are any of you former Lotus users?
Barbara Rick: yup
Jade Leung: yes
Jessie DeWire: yes
Pat Mellican: yes
David Crites: yes
Gail Perry: One way to explain this difference is that instead of recording movement in your macro, as in Lotus, you are recording theory. And I'm going to footnote this information by saying that we are dealing with Excel version 97 and 2000. Earlier versions of Excel differed in the way in which macro code was written. For those of you who might be using an earlier version, some of the information presented today may have to be revised to fit the rules for your version of Excel.
If you want to practice along with the macros we cover in this workshop, you can open Excel in another window on your computer and experiment as I enter information. If you prefer, you can just read along for now and later this evening, when the transcript of the workshop has been posted to our site, you can read the material at your leisure, print it if you want, and work through the examples at a slower pace.
Be sure to ask as many questions as you like, and share your experiences with Excel as I go through these examples.
We will begin by recording a few very simple macros, then we will test the macros to see what they do, then we will examine the code that was created by that recording process.
When you record macros, you need to keep in mind the following steps that must always be followed: First I'll list these steps, then we'll try them out with a real macro.
1. Turn on the macro recorder. In Excel 2000 this is accomplished by choosing Tools, Macro, Record New Macro from the menu. TIP: If you plan to record several macros, you can display the Visual Basic toolbar, which has a Record New Macro button on it. To display the Macro toolbar, choose View, Toolbars, Visual Basic. If you like, you can slide this little toolbar up to the top of your screen where it will attach itself to an existing toolbar. The little button with the blue circle is the Record Macro button.
Corey Warawa: Is there a major difference between writing macros in Excel 97 and Excel 2000? Just wondering because I recently picked up a book that refers to Excel 2000 and I'm using Excel 97.
Gail Perry: Excel 97 and Excel 2000 follow the same principles for macros, so you shouldn't have any problem using your book with Excel 97.
And while speaking of books - a good book for working with macros in Excel is "Excel for Windows Power Programming with VBA" written by John Walkenbach
Corey Warawa: That's the one!
Gail Perry: I think that book is excellent! Also the Microsoft press has a good book - Excel Visual Basic Step by Step, which has some nice examples in it.
2. Enter a name for the macro. In the window that appears, enter a unique name for your macro (Macro1, Macro2, Macro3 are the default names if you don't enter anything different). Your macro name is limited to 255 characters with no spaces, and the first character must be a letter.
3. Indicate a desired shortcut key for this macro. This step is optional, but if you want to run this macro with a keyboard shortcut, such as Ctrl+k or Ctrl+Shift+K, enter that keystroke in the space provided. Be careful about using shortcut keys that already serve some other purpose in Excel. The name you choose will override default shortcut keys. For example, the shortcut combination of Ctrl+s is a standard shortcut for saving your workbook. If you indicate you want to use Ctrl+s for this macro, that will take the place of the Ctrl+s for saving a workbook, whenever a workbook that uses this macro is open.
4. Indicate if you want to store this macro in the current workbook in a new workbook, or in your Personal Macro workbook. Macros stored in individual workbooks only work in those workbooks. If you want to make your macro available to other workbooks, save the macro in the personal workbook.
5. Indicate an optional description for the macro. The description will be available in the macro window whenever you are selecting a macro.
6. Click OK. The macro recorder is now turned on. You will see the word "Recording" at the bottom of the screen, and a tiny two-button toolbar should appear on-screen. This is called the Stop Recording toolbar. If the toolbar doesn't appear and you want to see it, choose View, Toolbars, Stop Recording and you should see the little toolbar. If you are displaying the Visual Basic toolbar, note that the Record Macro button has changed to a Stop Recording button.
7. Record your macro.
8. When you are finished, click the Stop Recording button on the little toolbar, or choose Tools, Macro, Stop Recording. The macro recording session has ended. TIP: When using the little Stop Recording toolbar, be sure to click the Stop Recording button when you are ready to end your recording session. Don't just click the little "x" in the corner of the toolbar. This will make the toolbar go away, but the recorder will still be turned on! You can also click the Stop Recording button on the Visual Basic toolbar to turn off the recorder.
Ok, here are a couple of basic sample macros that you can record. These two macros place your company name and address in particular cells in the worksheet. Creating these simple macros, then studying what happens when they run and studying the resulting code will provide a pretty good introduction to how this whole process works.
For the first macro, begin with your cellpointer in Cell A1. Now, turn on the macro recorder (see step 1 above). (Tools, Macro, Record new macro). Name this macro CompanyInfo1. Let's not give this macro a keyboard shortcut because it's just a silly little macro. Store the macro in This Workbook. You can leave off the optional description. Click OK to begin recording.
Enter your company name in cell A1, then enter the company street or P.O. address in cell A2, then enter the city, state, and postal code (and country, if applicable) in cell A3. Be sure you remember to hit "Enter" after the last line of information, just as you would if you weren't recording. Click the Stop Recording button to turn off the recorder.
To test this macro, let's first clear the information in cells A1 through A3 (highlight the cells and press Delete). Then position your cellpointer back in Cell A1.
Gail Perry: Now we will run the macro: Choose Tools, Macro, Macros, select CompanyInfo1, and click the Run button. The information you recorded should appear in cells A1 through A3, just as you recorded it.
Now here's something that sets Excel macro programming apart from Lotus. Go back and delete the information that just appeared in cells A1 through A3, so your spreadsheet is once again blank. Click on a cell somewhere in the middle of the screen, cell E12 for example. And now run the macro again with your cellpointer in cell E12. For those of you who are playing along - what happened?
Pat Mellican: It recorded in Cells A1..A3
Jade Leung: the company title appeared in e12 and the rest was in a2 and 3
Gail Perry: Excel employs the concept of absolute vs. relative movement on a worksheet. When recording absolute movement, the cells you move to while you are recording are the actual cells that are recorded in the macro. In this case, we recorded the company name, then moved to cell A2, then moved to cell A3 for the rest of the recording session. Those movements to cell A2 and A3 were recorded with the actual, absolute cell locations.
In Pat's case - you probably turned on the recorder, then moved your cellpointer into cell A1, so that got recorded too. With this type of macro, no matter which cell you start in, when you run the macro, you will get the same results. Recording a macro with relative movement is closer to the concept of recording Lotus macros, but still not the same.
In Lotus, when you record cursor movement like Up, Down, Right, and Left, those directions are stored.
In Excel, you record the movement in relation to the last cell that was active.
With this next macro, we can illustrate how relative movement works. Clear the cells on the worksheet by highlighting them and pressing Delete (a quick way to clear all the cells is Ctrl+a to select the entire worksheet, then hit the Delete key). Now return to cell A1 to begin recording a new macro.
For our second macro, turn on the macro recorder (Tools, Macro, Record New Macro). Call this CompanyInfo2 and store it without a keyboard shortcut, in This Workbook. This time, make sure the little Stop Recording toolbar appears, and turn it on, from the View menu, if it does not. (View, toolbars, Stop recording) Click the button on the right of the Stop Recording toolbar. This is the Relative Reference button. You will want to make sure this button looks like it has been pushed in. That means the Relative Reference feature has been turned on.
Now begin typing, enter your company name in cell A1, enter the address in A2, and enter the city, state, zip in A3, finishing by pressing Enter after you complete the information in cell A3.
Click the Stop Recording button.
To test this macro, first clear the worksheet by deleting all the information you already typed. Begin with your cellpointer in Cell A1. Run the macro by choosing Tools, Macro, Macros, selecting CompanyInfo2, and clicking Run. Your company name and address should appear in cells A1 to A3. So far so good.
Now clear those cells, and move your cellpointer over somewhere into the middle of the worksheet, D5 for example. Run the CompanyInfo2 macro again. This time the company name and address should stay together. Were you all successful?
Jade Leung: yahoooooooooo
Liger Needom: Yes
Pat Mellican: yes
Gail Perry: Let's examine the code behind these two macros. If you had a problem with either of these macros, we can figure out the problem by checking the code. Choose Tools, Macro, Visual Basic Editor. A separate window will open. On the left side of the screen you should see a little window called the Project window.
If this window doesn't display, go to the View menu and choose Project Explorer and now the window should appear. At the top of the little navigation tree in this window will be the description, VBAProject followed by the name of your workbook in parentheses. If you haven't given your workbook a name, you will see Book1 or Book2 or whatever workbook number you are using in the parentheses.
Beneath the workbook name you will see Microsoft Excel Objects, and beneath that is a header for Modules. You will want to display the information for Module1. It is possible that you will have to click a little plus sign to the left of Modules to show Module1 underneath this heading. Double-click on "Module1" to display the information in this Module. This is where your visual basic code is stored for the macros you recorded. You can maximize the Module 1 (Code) window if you so desire.
Te codes for each macro follow a basic style. First, the macro begins with the code Sub followed by the macro name and finished with an open and closed parentheses. So the first macro begins "Sub CompanyInfo1 ( )" and the second macro reads "Sub CompanyInfo2 ( )" Beneath the macro name is some information about the name of the macro and the date on which it was recorded and by whom it was recorded (this name is determined by the person to which the Excel program is registered). he name and date lines appear in green text and begin with an apostrophe. This is called reminder text and does not affect the action of the macro. You can enter any reminder text you wish in the VBA edit version of the macro by starting your line with an apostrophe.
For example, in the CompanyInfo1 macro you can start a new line with an apostrophe, then type "This macro enters my company information but I have to be in cell A1 for it to work properly." When you press Enter, the line turns to green and becomes a reminder line like the others.
Let's examine the code that you see for the first macro. My first line of code looks like this: ActiveCell.FormulaR1C1 = "AccountingWEB"
ActiveCell.Formula means this is the formula or information that will be entered in the active cell, the cell where my cellpointer is resting. Because I began recording the macro with our cellpointer already in the starting cell, there is no cell movement recorded. Furthermore, when we tested this macro by running it from cell E12, the first line of your address remained in cell E12, because that was the active cell when you started to run the macro. Company name, that is, remained in cell E12, or wherever you were when you began running the macro.
The R1C1 refers to Row 1, Column 1, which is completely unnecessary information. This coding is a carryover from earlier versions of Excel macro programming, and you can delete this information from your macro without causing any change in the macro operation.
The equal sign signifies that anything following that sign is what should be placed in the Active Cell.
"AccountingWEB" appears in quotes - the quotes encompass the information that goes in the active cell.
Line 2 of code looks like this: Range("A2").Select
Excel has recorded my movement to cell A2 and has selected that cell. Now A2 is the active cell.
Line 3 of my code looks like this: ActiveCell.FormulaR1C1 = "P.O. Box 68748"
Again, the ActiveCell.Formula command tells Excel to place the information following the equal sign into the active cell. After the equal sign is my company address. Note that you can edit information in the Visual Basic editor just as you would a word-processing document. If you made a typographical error in your street address, or in any other line of code, you can make the changes on this screen.
Again, the R1C1 is unnecessary information. You can delete this if you like.
The next line records my movement down to cell A3 and looks like this: Range("A3").Select
Cell A3 is now the ActiveCell, and the next line appears: ActiveCell.FormulaR1C1 = "Indianapolis, IN 46268-0748"
Now the last line records my activity of pressing Enter after the last line of the address: Range("A4").Select
Your macro code should look very similar to this, except that your own company name and address will appear. If, upon examination of this macro, I decide I want this macro to return to cell A1 when it finishes executing, I can simply change the last line to read: Range("A1").Select
The last line of the macro is: End Sub. All macros end with this line. Sub is short for "Subroutine" - the End Sub line tells Excel when to stop reading code for this macro.
Take a look at the second set of macro codes, for the CompanyInfo2 macro. There is a major shift in logic in this second macro. It begins the same as the first macro: ActiveCell.FormulaR1C1 = "AccountingWEB"
But look at the second line of code: ActiveCell.Offset(1, 0).Range("A1").Select
Instead of moving specifically to cell A2, this macro, which was recorded with the relative reference feature turned on, uses the command ActiveCell.Offset(1,0).
ActiveCell.Offset means that the cellpointer is going to be moved in relation to the location of the active cell, and it will be "offset" by a certain number of rows and a certain number of columns, in this case 1 row and 0 columns.
Using this relative reference, you can see that it doesn't matter in which cell you place your cellpointer when you begin running this macro, the cellpointer will move one row beneath the original row and that is where the address line of the company info will appear. If you wanted your street address to appear two lines beneath your company name, you would use the code: ActiveCell.Offset(2, 0).Range("A1").Select
Or if you wanted your street address to appear in the cell to the right of your company name, you would use the code: ActiveCell.Offset(0, 1).Range("A1").Select
The rest of this code, Range("A1").Select, means that Excel has essentially renamed the new active cell as A1, and all future references to this cell in this set of code will be to cell A1.
That way, no matter where your cellpointer is when you execute this macro, it will be as if you are starting in cell A1, and each cell thereafter is as if it is cell A1. Understanding this, you can probably decipher the rest of the code, which is very similar to the code in the first macro, except for the offset commands.
When using the Visual Basic editor, you can make any changes, additions, or deletions you need to in the macro code. Experiment with the editing process by changing the spelling or capitalization in your company name. As soon as you enter information into the visual basic editor, it becomes an active part of the macro, without you having to execute a "save" command. So, if you change the spelling of your name, or change a part of the company address, you can go right over to the Excel screen and run this macro to see how it works - the changes you made will take effect immediately.
Liger Needom: Great!
You can also copy and paste lines of code from one macro to another. When I am building complicated macros, I will create a macro called "test" or "junk" where I just record myself performing various steps of movement and entering information in the worksheet, then I paste the codes I need from the Junk macro into the final product macro that I am creating.
Also, if there is a macro that someone else created that contains some code you want, you can open the other macro in the editor and copy the code for your own macro. The best way to learn how the visual basic codes work is to examine other macros, and to make your own simple macros, then study the code .
Note that when you are ready to close the Visual Basic editor window, just close it by clicking the "x" in the corner or choosing File, Close and Return to Microsoft Excel - either way any changes that you made will be automatically saved.
What are some examples of macros that you have created that make your lives easier?
Corey Warawa: Formatting spreadsheets for printing.
Gail Perry: That's a good idea - any sort of formatting command can be saved in a macro, then recalled when you need it.
Corey Warawa: Also creating userforms for spreadsheets requiring a lot of data entry.
There is a macro security feature you should be aware of when opening spreadsheets containing macros created by someone else. If you go to Tools, Macro, Security, a window will appear letting you set the level of security for your computer.
If security is a big problem, you can choose High security, but this prevents macros from being opened unless they are digitally signed by someone you have approved (there is information on digital signatures in the Help area). Medium security is a good choice - this gives you a warning whenever you are about to open a worksheet containing macros, so you can decide if the source is trustworthy before opening the file on your computer. There are a lot of macro-related viruses about, which is why this feature can be important
When you have time, here's a nice tip that I wrote for AccountingWEB last year - it shows how you can create a button on your toolbar that will run a macro for you: www.accountingweb.com/item/30102/101
Jade Leung: Tools, macro, security is it Excel 2000?
Gail Perry: Yes it is in Excel 2000 - I don't have Excel 97 on my machine anymore - is it not there?
Jade Leung: Nope.
Gail Perry: Well, something to look forward to when you get the next version!
Jade Leung: Good answer
Gail Perry: Actually I think there may be something hidden somewhere in Excel 97 - check the help and it may give you some clues - may be an add in, I just can't remember (old age, memory loss...).
Before we wind up - I really want to thank all of you for attending today. Are there any last minute questions?
Corey Warawa: Are there going to be any more advanced macro workshops?
Jade Leung: Are you having any other session in the future?
Gail Perry: I think it would be a good idea - it's hard to include a lot of material in a short session. I have taught an 8-hour class on macros and this material was just the intro material for that class, so there's plenty more material for future sessions - probably we'll schedule another one for the summer.
David Crites: Gail, thanks for your time, this was very informative.
Gail Perry: Thanks again everyone - I hope this gave you some good groundwork for working with macros!
Gail Perry is a CPA, tax specialist, journalist, author, speaker and instructor. Her weekly newspaper column, "Fun With Taxes," which appears in the Indianapolis Star, provides readers with practical tax insights and advice presented in plain English with a touch of humor. She joined the staff of AccountingWEB in the summer of 2000.
Ms. Perry is a former senior tax consultant with the international firm of Deloitte and Touche, where she provided tax planning services and advice to individuals and small businesses. An accomplished free-lance writer, Perry is the author of over a dozen books, including "The Complete Idiot's Guide to Doing Your Income Taxes," "Using QuickBooks," "Excel 2000 Answers!" and "TurboTax for Dummies." In addition, she has written several computer training books and is co-author of a book for trainers, "The Computer Trainer's Personal Training Guide."
Ms. Perry is a regular guest on America Online's Money Whiz Q&A program, and she has been featured on Fox television's "Good Morning New York," Bloomberg Business News's "Bloomberg Forum," WOR Radio's syndicated program, "Manage Your Money with the Dolans," as well as dozens of radio shows across the country.