Ten Tips for Coding Excel VBA Macros

Ten commonsense suggestions to make coding Excel VBA faster and easier. These tips are based on Excel 2010 (but they work in nearly all versions) and many were inspired by the OReilly book Excel 2010 - The Missing Manual by Matthew MacDonald. 1 - Always test your macros in a throwaway test spreadsheet, usually a copy of one that its designed to work with. Undo doesnt work with macros, so if you code a macro that folds, spindles, and mutilates your spreadsheet, youre outta luck unless you have followed this tip. 2 - Using shortcut keys can be dangerous because Excel doesn’t warn you if you choose a shortcut key that Excel is already using. If this happens, Excel uses the shortcut key for the macro, not the built-in shortcut key. Think about how surprised your boss will be when he loads your macro and then Ctrl-C adds a random number to half the cells in his spreadsheet. Matthew MacDonald makes this suggestion in Excel 2010 - The Missing Manual. Here are some common key combinations that you should never assign to macro shortcuts because people use them too frequently: CtrlS (Save)CtrlP (Print)CtrlO (Open)CtrlN (New)CtrlX (Exit)CtrlZ (Undo)CtrlY (Redo/Repeat)CtrlC (Copy)CtrlX (Cut)CtrlV (Paste) To avoid problems, always use CtrlShiftletter macro key combinations, because these combinations are much less common than the Ctrlletter shortcut keys. And if you’re in doubt, don’t assign a shortcut key when you create a new, untested macro. 3 - Cant remember Alt-F8 (the default macro shortcut)? Do the names mean nothing to you? Since Excel will make macros in any opened workbook available to every other workbook that’s currently open, the easy way is to build your own macro library with all of your macros in a separate workbook. Open that workbook along with your other spreadsheets. As Matthew puts it, Imagine you’re editing a workbook named SalesReport.xlsx, and you open another workbook named MyMacroCollection.xlsm, which contains a few useful macros. You can use the macros contained in MyMacroCollection.xlsm with SalesReport.xlsx without a hitch. Matthew says this design makes it easy to share and reuse macros across workbooks (and between different people). 4 - And consider adding buttons to link to the macros in the worksheet that contains your macro library. You can arrange the buttons in any functional groupings that make sense to you and add text to the worksheet to explain what they do. Youll never wonder what a cryptically named macro actually does again. 5 - Microsofts new macro security architecture has been improved a lot, but its even more convenient to tell Excel to trust the files in certain folders on your computer (or on other computers). Pick a specific folder on your hard drive as a trusted location. If you open a workbook stored in this location, it’s automatically trusted. 6 - When youre coding a macro, dont try to build cell selection into the macro. Instead, assume that the cells that the macro will use have been pre-selected. Its easy for you to drag the mouse over the cells to select them. Coding a macro that is flexible enough to do the same thing is likely to be full of bugs and hard to program. If you want to program anything, try to figure out how to write validation code to check whether an appropriate selection has been made in the macro instead. 7 - You might think that Excel runs a macro against the workbook that contains the macro code, but this isn’t always true. Excel runs the macro in the active workbook. Thats the workbook that you looked at most recently. As Matthew explains it, If you have two workbooks open and you use the Windows taskbar to switch to the second workbook, and then back to the Visual Basic editor, Excel runs the macro on the second workbook. 8 - Matthew suggests that, For easier macro coding, try to arrange your windows so you can see the Excel window and the Visual Basic editor window at the same time, side-by-side. But Excel wont do it, (Arrange All on the View menu only arranges the Workbooks. Visual Basic is considered a different application window by Excel.) But Windows will. In Vista, close all but the two you want to arrange and right-click the Taskbar; select Show Windows Side by Side. In Windows 7, use the Snap feature. (Search online for Windows 7 features Snap for instructions.) 9 - Matthews top tip: Many programmers find long walks on the beach or guzzling a jug of Mountain Dew a helpful way to clear their heads. And of course, the mother of all VBA tips: 10 - The first thing to try when you cant think of the statements or keywords you need in your program code is to turn on the macro recorder and do a bunch of operations that seem to be similar. Then examine the generated code. It wont always point you to the right thing, but it often does. At a minimum, it will give you a place to start looking. Source MacDonald, Matthew. Excel 2010: The Missing Manual. 1 edition, OReilly Media, July 4, 2010.