Start Excel and lets find out what we need to know to make Excel write VBA programs automatically.
Nearly everything you do as an Excel VBA developer will be done under the "Developer" tab in Excel. But in Excel 2010, that tab isn't there in the default installation. If it's missing in yours, go to:
File > Options > Customize Ribbon
In the Main Tabs list, make sure the Developer check box is checked and click OK. Click the new Developer tab to display the Developer ribbon. Now you're ready.
To make Excel write your new VBA program, click Record Macro at the left side of the Developer ribbon. This part of Excel VBA hasn't changed much at all. The Record Macro dialog should pop up and provide a place for:
- > The name you want to use for the macro
(You can leave it at the default, usually Macro1, but it's not a good idea. You should always give your macros meaningful names so you'll be able to recognize what they are later.)
- > A Shortcut key
(It's not necessary to use this unless you want to.)
- > Where to store the macro
(This is very important! You get a choice of "Personal Macro Workbook", "New Workbook" and "This Workbook". More about this later.)
- > A text description.
(This is optional, but it can save you time and frustration. The description you enter will be added as comments to the VBA program.)
Choosing Where To Store The Macro
This is the third option above and it's worth thinking about how you will use your macro. If you work in a company, they may have a standard about this. All three places that you can store a macro relate to two factors:
- > Security. Who can execute the macros and on which machines.
- > Organization. How to group the macros so they will be available to people who need them. (And not available to people who shouldn't have them.)
A Personal Macro Workbook is a special file on your PC named Personal.xlsb. You can use macros in your personal workbook in any workbook when you start Excel on the same computer. This works because Personal.xlsb is opened as a hidden workbook every time you start Excel. You normally don't need to know, but if you're curious, you can find it in (Vista and Win 7):
Use the Personal Macro Workbook for macros that you will use in different workbooks and which don't have to be shared with anyone else. Using the Personal Macro Workbook can be confusing and inconvenient because you have to Unhide it to maintain the macros and then Hide it again. (Use the buttons in the Window section of the View ribbon.)
If you select New Workbook for your macros, then Excel will save the workbook you're currently using without the macro and create a different workbook just for the macro. Excel 2010 treats workbooks that contain macros differently than normal macros for better security so you might want to separate workbooks for that reason. Or, you might have a library of macro containing workbooks that others can access. If you're going to distribute the macro separately from the workbook, choose this option.
This Workbook is the default and the option you will probably use nearly all the time. Most macros relate to a specific workbook and should be saved with them.
In the early days of VBA, macro security was a serious problem. I remember that Microsoft seemed to be completely surprised to discover how easy it was for even novice programmers to write very damaging Excel macros that could do just about anything on a host machine as soon as a spreadsheet was opened. As a result, VBA got a very bad reputation in those early days and some companies banned macros completely.
Fortunately, those days have passed. Microsoft now has a secure architecture for VBA, but you have to understand how it works.
The basic security strategy has been to create new file types to tell Excel whether a macro is present in an Excel workbook or not.
*.xlsm - macro enabled workbook
If you save macros in This Workbook, you have to use this type.
*.xlsb - binary workbook
The Personal Workbook has this type.
*.xltm - macro enabled template
Since macros are not independent programs and must be executed by the Excel host, Excel can enforce these file types and prevent macros from running if the file type isn't correct.
The Record Macro Tool
Microsoft thought "Record Macro" would be a fast way for non-programmers to create macros for their own use. For example, if you wanted to enter the value "123" into cell J3 in a whole group of sheets, you could quickly record a macro to do it. The problem is that recorded macros are completely inflexible. The recorded macro would use the value "123" and nothing else and it would only be entered into cell J3. If you want to select a cell or enter one of a range of values, you have to write VBA code.
But Record Macro has another use that makes it invaluable. It's a great way to learn how to write VBA. If you don't know how to do something, just record a macro that does what you want to do and then look at the VBA code to see how Excel did it. Here's how that works.
Make sure the Developer ribbon is displayed, then simply click Record Macro. Decide where to store the macro. ("This Workbook" is recommended.) Notice that Record Macro has changed to Stop Recording. Then just perform a bunch of Excel operations like entering values into cells or summing a column. Click Stop Recording. Your macro has been automatically created and is now part of your workbook. This is where the programming starts.
To see the program Excel wrote, you have to open it in the Visual Basic editor. This can be done in several ways, but we'll do using the Visual Basic icon at the far left of the Developer ribbon.
Click Here to display the illustration
Note that the VBA program is in a module that is part of the workbook. This program doesn't do much, so lets see how to write one that does on the next page.