Subscribe / Unsubscribe Enewsletters | Login | Register

Pencil Banner

5 tips for creating macros perfectly in Office applications

JD Sartain | March 23, 2015
Shrink hours of labor to minutes with these easy time-saving techniques.

Macros are the timesaving magic trick for Office applications because they automate repetitive tasks that gobble time. To get the most out of your macros, make sure you follow these fundamental tips. We stick with Excel for our examples, but remember: Macros work in other Office applications too!

1. Use Relative cell references

Always use Relative cell references/addresses (the default is Absolute references) unless your macro is for one specific application in one spreadsheet. If you plan to re-use the macro for any other spreadsheet or need to make adjustments, such as inserting extra rows to accommodate additional records, you must click the Relative Reference button immediately after you click Record Macro (and before you record any keystrokes). See my Excel macro tips article for detailed instructions.

2. Use specific VBA code with directional keys

Always use directional keys to navigate through spreadsheets. In Excel, these keys include Home, End, Page Up, Page Down, and the four arrow keys: Up, Down, Right, and Left. When combined with Shift, Alt, and Ctrl, you have a lot of options for quickly moving the cursor all over the spreadsheet in macros and while working.

Directional keys (like shortcuts) must be entered exactly as defined here, or the macros will fail. For example, the dash key and the plus key are not interchangeable. When the instructions specify a dash (e.g., End-Down), it means press the End key first, release, then press the Down Arrow key. When the instructions specify a plus sign (Ctrl+Home), it means press the Ctrl key and hold down, then press the Home key, and then release both keys.

The macro code is displayed in the Visual Basic for Applications (VBA) Editor and accessed through the Code group under the Developer tab. Select Macros > Edit or Macros > Step Into. VBA code, which Excel writes for you, looks like this:

If you plan to write a lot of macros, it's a good idea to (at least) view your macros in the VBA Editor to see how Visual Basic writes the commands you record. You don't have to be a programmer to make simple adjustments. 

3. Create flexible macros

Use the directional keys to record flexible macros. Data always begins in cell A1 (Home position), and macros should begin with Ctrl+Home to ensure that the macro performs accurately.

In this example, TJ is required to track the dollars won and lost by the winners of each game played in the annual Monopoly tournament--six games per day for seven days, or 42 games/winners. She only needs to create one spreadsheet with macros (which are saved in the PERSONAL.XLSB! file) for one winner, then copy that sheet 41 times (in the same workbook). Change the number of payments and collections for each winner, and the task is complete.


1  2  3  Next Page 

Sign up for CIO Asia eNewsletters.