Excel Macro Definition

What Is a Macro in Excel and When Is It Used?

Using the Macro Development Tools in Excel
The Macro Development Tools in Excel. © Ted French

An Excel macro is a set of programming instructions stored in what is known as VBA code that can be used to eliminate the need to repeat the steps of commonly performed tasks over and over again.

These repetitive tasks might involve complex calculations that require the use of formulas or they might be simple formatting tasks - such as adding number formatting to new data or applying cell and worksheet formats such as borders and shading.

Other repetitive tasks for which macros can be used to save include:

  • adding or removing rows and columns
  • protecting or unprotecting worksheets
  • selecting a range of cells
  • adding the current date to a worksheet

Triggering a Macro

Macros can be triggered by a keyboard shortcut, toolbar icon or a button or icon added to a worksheet.

Macros vs. Templates

While using macros can be a great time saver for repetitive tasks, if you routinely add certain formatting features or content - such as headings, or a company logo to new worksheets, it might be better to create and save a template file containing all such items rather than creating them anew each time you start a new worksheet.

Macros and VBA

As mentioned, in Excel, macros are written in Visual Basic for Applications (VBA). Macros writing using VBA is done in the VBA editor window, which can be opened by clicking on the Visual Basic icon on the Developers tab of the ribbon (see below for instructions on adding the Developers tab to the ribbon if needed).

Excel's Macro Recorder

For those who cannot write VBA code, has a built-in macro recorder that allows you to record a series of steps using keyboard and mouse that Excel then converts into VBA code for you.

Like the VBA editor mentioned above, the Macro Recorder is located on the Developers tab of the Ribbon.

Adding the Developer Tab

By default in Excel, the Developer tab is not present on the Ribbon. To add it:

  1. Click the File tab to open the drop down list of options
  2. On the drop-down list, click Options to open the Excel Options dialog box
  3. In the left-hand panel of the dialog box, click on Customize Ribbon to open the Customize Ribbon window
  4. Under the Main Tabs section in the right-hand window, click on the checkbox next to Developer to add this tab to the Ribbon
  5. Click OK to close the dialog box and return to the worksheet.

The Developer should now be present - usually on the right-hand side of the Ribbon

Using the Macro Recorder

As mentioned, the Macro Recorder simplifies the task of creating macros - even, at times, for those who can write VBA code, but there are a few points to be aware of before you begin to use this tool.

1. Plan the Macro

Recording Macros with the Macro Recorder involves a bit of a learning curve. To simplify the process, plan ahead of time - even to the point of writing out what the macro is intended to do and the steps that will be needed to accomplish the task.

2. Keep Macros Small and Specific

The bigger a macro is in terms of the number of tasks it performs the more complicated it will likely be to plan and record it successfully.

Bigger macros also run slower - especially those involving lots of calculations in large worksheets - and they are harder to debug and correct if they do not work right the first time.

By keeping macros small and specific in purpose it is easier to verify the accuracy of the results and to see where they went wrong if things don't go as planned.

3. Name Macros Appropriately

Macro names in Excel have several naming restrictions that must be observed. First and foremost is that a macro name must begin with a letter of the alphabet. Subsequent characters can be numbers but macro names cannot include spaces, symbols, or punctuation marks.

Nor can a macro name contain any of a number of reserved words that are part of the VBA uses as part of its programming language such as If, GoTo, New, or Select.

While macro names can be up to 255 characters in length it is seldom necessary or advisable to use that many in a name.

For one, if you have a lot of macros and you plan on running them from the macro dialog box, long names just cause congestion making it harder to pick out the macro you are after.

A better approach would be to keep the names short and make use of the description area to give details about what each macro does.

The Underscore and Internal Capitalization in Names

Since macro names cannot include spaces, one character that is allowed, and which makes reading macro names easier is the underscore character which can be used between words in place of a space - such as Change_cell_color or Addition_formula.

Another option is to employ internal capitalization (sometimes referred to as Camel Case) which starts each new word in a name with a capital letter - such as ChangeCellColor and AdditionFormula.

Short macro names are easier to pick out in the macro dialog box, especially if a worksheet contains a number of macros and you record a lot of macros, so you can easily identify them in the. The system also provides a field for Description, though not everyone uses it.

4. Use Relative vs. Absolute Cell References

Cell references, such as B17 or AA345, identify the location of each cell in a worksheet.

By default, in the Macro Recorder all cell references are absolute which means that the exact cell locations are recorded into the macro. Alternatively, macros can be set to use relative cell references which mean that movements (how many columns left or right you move the cell cursor) are recorded rather than exact locations.

Which one you use depends on what the macro is set to accomplish. If you want to repeat the same steps - such as formatting columns of data - over and over, but each time you are formatting different columns in a worksheet, then using relative references would be appropriate.

If, on the other hand, you want to format the same range of cells - such as A1 to M23 - but on different worksheets, then absolute cell references could be used so that each time the macro runs, its first step is to move the cell cursor to cell A1.

Changing cell references from relative to absolute is easily done by clicking on the Use Relative References icon on the Developers tab of the ribbon.

5. Using Keyboard Keys vs. the Mouse

Having a macro record keyboard keystrokes when moving the cell cursor or selecting a range of cells is usually preferable to having mouse movements recorded as part of the macro.

Using keyboard key combinations - such as Ctrl + End or  Ctrl + Shift + the Right Arrow key -  to move the cell cursor to the edges of the data area (those cells containing data on the current worksheet) rather than repeatedly pressing the arrow or tab keys to move multiple columns or rows simplifies the process of using the keyboard.

Even when it comes to applying commands or selecting ribbon options using keyboard shortcut keys is preferable to using the mouse.