Getting the Most Out of VBA Macros for Microsoft Office 2010
Macros are one the most powerful capabilities integrated into Microsoft Office 2010. These macros are instruction sets that let you automate practically any aspect of the Office suite, and all users can take advantage of them to work faster and more productively in Access, PowerPoint, Word and so forth.
VBA macro support must be installed. If that support was not installed, then you’ll need to use the Office installer to update your installation. Even when installed, macro support may be turned off by default because it is a security risk. Be sure to enable macros in the Trust Center Settings, which you can find in File > Help > Options > Trust Center.
Enabling all macros is the most convenient solution but a potentially risky one if you open documents from untrusted sources. If you’re concerned about security, then choose to run only digitally signed macros instead. This means you’ll have to digitally sign your own macros, which is an additional step but a rather simple one once you’ve become accustomed to doing it.
Note Your Usage Patterns
The power of macros lies in creating reusable functions that automate tasks that you do repeatedly. The challenge here is assessing your personal usage and identifying patterns. You don’t have to think in terms of big or complex operations. Much of the time you can save yourself can be found in small, relatively minor operations that you might perform hundreds of times a day without really noticing.
Create Focused, Reusable Macros
The key to working smarter not harder with Microsoft Office 2010 is to create reusable macros. Try to reduce macros to functions that perform a small, specific task. That doesn’t mean that you can’t have complex macros as well, but those complex macros can largely be calls to a series of smaller, focused functions. The goal here is to create a function once that you can use again and again.
Assign Keyboard Shortcuts to Your Macros
Your master macros are those macros that you’ll use directly when working in Word, Excel or another aspect of Microsoft Office 2010. Accessing macros through the menu system is relatively slow and cumbersome and will limit how useful macros are. By assigning macros a shortcut, you’ll be able to execute them with just a small key combination.
Learn How in VBA with Macro Recording
The macro recorder in Microsoft Office 2010 doesn’t create optimized code, but what it does do is teach you how to achieve a desired action in code. In fact, the recorder can monitor practically any Office action and show you how to represent it in VBA code.
Optimize Recorded Macros
Recorded code is perfectly fine for many macros, but that generated code will almost always contain some nonessential commands. Take some time to optimize these recorded code blocks. It’s good practice and will generally make code run faster, which may matter when you reference these recorded macros from other macros.