5 tips for creating macros perfectly in Excel

Macros are the timesaving magic trick for Office applications because they automate repetitive tasks that gobble time. This week’s feature, which spotlights Excel, is one of several articles featuring macros in Microsoft. Stay tuned, in weeks to come, for more on macros in other Office programs.

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:

JD Sartain

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.

a. Right-click the worksheet tab of the current spreadsheet.

b. From the drop-down list, select Move or Copy Sheet.

c. From the dialog box Move Selected Sheets To Book, select (move to end) from the spreadsheet list, check the Create a Copy box, then click OK.

d. Repeat process until you have 41 copies of the first, original spreadsheet.

4. Enter Fixed Values/Data in Advance

The above example calculates the total rent dollars paid per property, then calculates the total rent dollars collected per property. Next, it calculates the difference, then provides a Profit/Loss total. This example shows how to maneuver the cursor to select ranges, cells, enter formulas, etc.

a. Only two columns/fields have unique/changing data. Column A = property name, column B = rent amount. Both are fixed values. The remaining columns are either formulas or new data. Enter the info for columns A and B in advance.

b. The first-game winner is KC. Enter the number of payments she made for each property in column C, then the number of collections she received in column E. Now record the macro to calculate the numbers.

5. Navigate with directional keys

a. Record macro: Ctrl+Home, Right 3 (press Right Arrow three times). Formula: =SUM(B1*C1). However, we need to modify it first because the formula columns—D: Total Rent Paid and F: Total Rent Collected—both use the Rent Amounts in column B. Because it’s easier to copy formulas than re-enter formulas, use the Absolute/Relative Reference function key F4 to hardcode the B (absolute) in this formula so it never changes when it’s copied.

Formula: Enter in cell D1: =SUM(B1 [then press F4 three times] *C1) Enter (key). Formula now looks like this: =SUM($B1*C1). Press Enter, Up.

Why Up arrow? Because the cursor moves down once (from D1 to D2) after you press Enter, and you need to copy the formula from D1.

JD Sartain

b. Copy formula: Cursor location is D1. Type Ctrl+C (copy), Left, End-Down; Right, Shift+End-Up-Down, Enter. Wow! That’s confusing. It means press the Shift key and hold throughout the entire string; that is, hold down the Shift key, then press and release the End key, press and release the Up Arrow, press and release the Down Arrow, then release the Shift key. The Shift key holds and extends the highlight, which selects the range, then the Enter key concludes and formulas are copied. The range is still highlighted, so press End-Up (to reposition the cursor in cell D1).

c. Copy again: (cursor in D1), press Shift+End-Down, Ctrl+C, Right 2, Enter, End-Up. Formulas copied and the cursor is repositioned in cell F1.

d. Calculate difference: Right, enter formula: =SUM(F1-D1) [F1 minus D1]. Press Enter. Again, type Ctrl+C, Left, End-Down; Right, Shift+End-Up-Down, Enter, Down.

Note: Formulas are hardcoded in this sample for ease and brevity. If the spreadsheet were completely dynamic, you would not hardcode the formulas either but, instead, “point” to the range as shown below.

e. Cursor position is now in the first blank cell beneath column G’s calculated formulas. Add this column for KC’s grand total using the “point” technique, which is best for macros. Enter =SUM( [equal sum left parenthesis], Up, End, Up, Shift+End-Down, Enter. Grand total calculated, and the macro is finished.