Excel macros save you time and headaches by automating common, repetitive tasks. And you don't have to be a programmer or know Visual Basic Applications (VBA) to write one. With Excel 2013, it's as simple as recording your keystrokes. Use these tips to make macro recording a cinch.
1. Macro names
Keep macro names short (but descriptive), especially if you record a lot of macros, so you can easily identify them in the Macro Dialog Box. The system also provides a field for Description, though not everyone uses it.
Macro names must begin with a letter and cannot contain spaces, symbols, or punctuation marks. After the first letter, you can use more letters, numbers, or the underscore character, but the maximum length is 80 characters.
2. Use relative (not absolute) cell addresses
Absolute means that the exact cell locations are recorded into the macro — hardcoded cell addresses such as A6 or B12, which limits the macro's ability to function if anything changes, new data is added/removed, or the list gets longer. Relative means the macro's recorded keystrokes are relative to the starting cell's location.
The default in Excel is Absolute, but you can change this to Relative on the Stop Recording toolbar:
Click Developer>Record Macro.
3. Always begin at Home
For Absolute reference macros, always begin in the Home position (cell A1)— with your cursor and your data. If you saved your macro in the Personal Macro Workbook (recommended), you can reuse this macro on other worksheets with similar data. Regardless of where your cursor is positioned when you begin recording the macro, even if it's already located in cell A1, your first macro keystroke must be Ctrl+Home.
Example: Imagine that every month you receive dozens of spreadsheets from all your branch offices that you must first combine, then organize, and calculate to produce one monthly report. You can write a macro to perform all of these functions, including opening and combining all the worksheets into one combined spreadsheet. For this exercise, I'll just address the final spreadsheet with the combined data.
4. Always navigate with directional keys
Use the directional keys (End-Down, Ctrl+Up, etc.) to position your cursor so you can add, change, or delete the data inside the spreadsheet as needed.
Using the mouse to navigate is more complicated and not as reliable. When it comes to macros, use the mouse only for selecting or clicking menu options.
5. Keep macros small and specific
Keep your macros small and specific to the tasks at hand. The bigger the macro, the slower it runs, especially if it's required to perform many functions or calculate a lot of formulas in a large spreadsheet. Also, if you combine all the tasks into one long macro and it fails, it takes forever to locate the point of failure. If you run each macro separately, you can quickly review the results and verify accuracy.
Sign up for CIO Asia eNewsletters.