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, 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. Absolute references limit 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.
- In the Record Macro dialog box, enter a macro name and Shortcut Key (if applicable). Choose Personal Macro Workbook from the Store Macro In drop-down list of choices, enter a description (if desired), and click OK.
- The dialog box disappears, and the Record Macro button changes to a Stop Recording button. Click the Relative Reference button next—it turns dark green to indicate that it’s active.
- Enter your keystrokes, formulas, and so forth, then click the Stop Recording button and run your macro.
JD Sartain / IDG
Record macros using relative cell addresses.
3. Always begin at Home
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 to make sure you start at 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.
- Create a new workbook in Excel (e.g., April Report). Your cursor is automatically located in the Home position (A1). Do not move it to accommodate titles, field headers, or anything else. It must be positioned in A1.
- Open the first branch workbook and copy the entire spreadsheet’s data (minus the column headers) to cell A1 in your April Report spreadsheet. The data begins in A1 and ends in G55.
- Open the second branch workbook and copy the entire spreadsheet’s data (minus the column headers) to cell A56 in your April Report spreadsheet. Continue this process until the data in all workbooks are copied into your April Report spreadsheet.
- If the other spreadsheets’ data contains formulas, choose Paste > Special >Values for your Paste options. Do not copy the formulas or the formats.
- Don’t worry about a worksheet title, column headers, or formatting (such as column widths, fonts, etc.) at this point. Just copy the data and run the macro.
- After the data is calculated, you can use Insert Rows to add column headers, spreadsheet titles, and other information. Then you can format the column widths, change fonts, add attributes, and place borders or boxes if you’d like. (You can also create an additional macro to perform formatting tasks.)
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. If you hardcode cell addresses, any changes or edits to the spreadsheet can invalidate the macro functions. For example, if you code the macro to sum A1 through A20, then add more rows (e.g., A1 through A35), the formula will not include the additional cells and the sum total will be wrong.
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.
JD Sartain / IDG
Step through an excel macro with F8.
In the April Report example, you wouldn’t combine the calculation macro with the format macro. You’d use one macro to combine all the branch workbooks into one final spreadsheet, a second macro to calculate the data, a third macro to format the spreadsheet, a fourth macro to create charts, and perhaps a fifth macro to print.
If a macro fails, you can troubleshoot it by stepping through it one line at a time. It’s like slow motion, only you control each time the next event occurs using the F8 key. Click Developer > Macro, select your macro from the list, then click the Step Into button. Shrink the Code window and place it on the bottom right side of the screen so you can see your spreadsheet behind it. Press F8 each time you want the macro to proceed to the next step. The macro stops when it reaches the error. You can then repair the error or record a new macro.
6. Enter static data in advance
If your spreadsheet contains static data such as the company name and location, you should enter that information in advance, before you write the macros, because this static information rarely, if ever, changes. Additional fields such as product ID numbers, product prices, or product descriptions would, generally, also contain static data. Use your macros to perform repetitive tasks such as formatting cells and/or charts, creating tables, sorting, entering formulas, managing ranges, or defining Pivot table filters.
7. Saving the spreadsheet
Once you create a macro in Excel, the file becomes a macro-enabled workbook with the xlsm extension. If you try to save the file as a regular spreadsheet (xlsx), Excel displays a warning dialog that says “The following features cannot be saved in macro-free workbooks,” and then shows a list of those features. Click the No button, then select Save As again and in the Save As Type dropdown dialog, choose Excel Macro-Enabled Workbook (*.xlsm) from the list. Click the Save button.
JD Sartain / IDG
Saving the spreadsheet as a macro-enabled workbook
8. Add your macro to a custom button on the Quick Access toolbar
For convenience, you can add a button on the Quick Access toolbar for your macros. Because there are so few shortcut keys available, this is a useful solution.
1. Select File > Options > Quick Access Toolbar.
2. In the Choose Commands From field box, click the small arrow and select Macros from the list.
3. In the dialog box below, Excel displays a list of all the macros in this workbook.
4. Select the SumTotals macro, then click the Add button to move the macro to the Customize Quick Access Toolbar dialog box.
5. With the SumTotals macro highlighted, click the Modify button at the bottom of this dialog panel.
6. Browse through the Modify Button dialog window and choose an icon symbol that best identifies your new macro (we chose the calculator).
7. Next, enter a descriptive name for your macro in the Display Name box.
8. And last, click the OK button twice.
9. Then check out the new button on the Quick Access Toolbar. Click this button once to run your macro.
JD Sartain / IDG
Add the macro to a custom button on the quick access toolbar.
9. Add a macro button to a custom group on the Ribbon menu
1. Select File > Options > Customize Ribbon.
2. First, ensure that the Developer box is checked in the Main Tabs panel under Customize the Ribbon.
3. Choose a tab for your custom macros or select/highlight the Add-Ins group under the Developer tab and click the New Group button.
4. With the New Group already selected (highlighted), click the Rename button, which opens the Rename dialog.
5. The Display Name field box is selected: Enter a name for this group, such as Custom Macros.
6. Next, browse through the Symbols dialog window and choose an icon that best identifies this macro (we chose the hourglass), then click OK.
7. In the Choose Commands From field box, click the small arrow and select Macros from the list.
8. In the dialog box below, Excel displays a list of all the macros in this workbook.
9. Select the SumTotals macro, then click the Add button to move the macro to your new group called Custom Macros.
10. If desired, click the Rename button again and choose an icon from the Symbols dialog window (we chose the calculator again, same as the button on the Quick Access Toolbar.
11. Click OK twice to close the Customize screen.
JD Sartain / IDG
Add a macro button to a custom group on the ribbon menu.
10. Macros Security Warning
When you open a workbook that contains a macro, you get this message:
“Security Warning: Macros have been disabled,” followed by a button that says Enable Content.
If you want to use your macro, you have to click this button. But what a hassle!
You can easily disable this repetitive task from the Message Bar or in the Trust Center.
Trust Center Macro Settings
1. Select File > Options> Trust Center.
2. Click the Trust Center Settings button.
3. Select Macro Settings from the menu.
4. Check Enable All Macros, then click OK.
Yes, Excel says this is not recommended, because dangerous code can potentially run. And this is true, if you download macros from the Internet, from another person’s system, or pick up a macro virus somewhere out there in cyberspace. But if you run your own macros only, and protect your system from malware, running dangerous code is unlikely.
If this option makes you uncomfortable, choose one of the other three options, then continue to click the Enable Content button when the macro workbook opens.
Note: The Change Macro settings in the Trust Center affect only the current program.
JD Sartain / IDG
Change the security warning in the Trust Center.
Message Bar
5. Select File > Options > Trust Center.
6. Click Trust Center Settings button.
7. Select Message Bar from the menu.
8. Check Never Show Information About Blocked Content, then click OK.
Note: If you choose Disable All Macros Without Notification in the Macro Settings panel in the Trust Center, the Message Bar does not show.