Ten tips 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 O’Reilly book: Excel 2010 – The Missing Manual by Matthew MacDonald.
New to macros? Read Excel Macros – What is the deal with it?
1 – Always test your macros in a throwaway test spreadsheet, usually a copy of one that it’s designed to work with. Undo doesn’t work with macros, so if you code a macro that folds, spindles, and mutilates your spreadsheet, you’re 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:
- Ctrl+S (Save)
- Ctrl+P (Print)
- Ctrl+O (Open)
- Ctrl+N (New)
- Ctrl+X (Exit)
- Ctrl+Z (Undo)
- Ctrl+Y (Redo/Repeat)
- Ctrl+C (Copy)
- Ctrl+X (Cut)
- Ctrl+V (Paste)
To avoid problems, always use Ctrl+Shift+letter macro key combinations, because these combinations are much less common than the Ctrl+letter shortcut keys. And if you’re in doubt, don’t assign a shortcut key when you create a new, untested macro.
Also read VBA Functions You Can’t Live Without
3 – Can’t 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. You’ll never wonder what a cryptically named macro actually does again.
5 – Microsoft’s new macro security architecture has been improved a lot, but it’s 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.
Also read 5 must-know Excel macros for common tasks
6 – When you’re coding a macro, don’t try to build cell selection into the macro. Instead, assume that the cells that the macro will use have been pre-selected. It’s 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 on the active workbook. That’s the workbook that you looked at most recently. As Matthew explains it, “If you have two workbooks open and you use the Windows task bar to switch to the second workbook, and then back to the Visual Basic editor, Excel runs the macro on the second workbook”. However you can avoid this by restricting macro to specific workbook and work sheet and range, by defining an variable/object and confine your code to it.
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 won’t 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”.
9 – Matthew’s 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 can’t 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 won’t always point you to the right thing, but it often does. At a minimum, it will give you a place to start looking.
Also read
Learn to optimize calculation time of worksheet data in VBA
Click here for hand full of Real Time VBA Examples with Code and sample files
Courtesy: visualbasic.about.com