Quadra

Connecting Technology and Business.

Excel Macros - how to build, run and follow best practices...

Excel macro best practices 

  • It's usually a good idea to turn on the Use Relative References option because the Macro Recorder will be more likely to produce the steps that you expect. Turn on this option by clicking the Use Relative References button in the Code group on the Developer tab. Learn how to show the Developer tab (by default, it's not shown) in the "Record a macro" section, below.
  • Inspect your macro in the Visual Basic Editor after you've recorded it to see if you can improve it or at least understand how it works by looking at its instructions. Do this by selecting your macro in the Macros dialog box (in the Code group on the Developer tab, click Macros) and then clicking the Edit button.
  • Make a habit of saving your macros to the personal workbook so that you can reuse them on your computer. Do this by selecting the Personal Macro Workbook option in the Store macro in list when you record a macro.

Record a macro

First, make sure the Developer tab is visible, because that's where all the macro commands are found:
  1. Click the File tab, click Options, and then click Customize Ribbon.
  2. Under Main Tabs, make sure the Developer check box is selected.
Then, start the Macro Recorder:
  1. On the Developer tab, in the Code group, click Record Macro.
  2. Optionally, you can assign your macro a shortcut key combination so that it's easy to run.
  3. Click OK to start the Macro Recorder.
  4. In your workbook, perform the actions that you want recorded, which can include typing words or numbers, clicking cells, clicking buttons, dragging cells, formatting, and more.
  5. When you're done with the actions that you want recorded, click Stop Recording.

Run your macro

  1. On the Developer tab, in the Code group, click Macros.
  2. In the Macros dialog box, find your macro and click Run.
    Note If you assigned your macro a keyboard combination (for example, CTRL+SHIFT+M) when you started the macro recorder, you can use that shortcut to run the macro.

Record a macro in your personal macro workbook

By saving a macro to the personal macro workbook, you make your macro available every time you open or create an Excel workbook on your computer. It's best to make this decision when you first record your macro, because it requires a bit of work (and knowledge of the Visual Basic Editor environment) to later move a macro to the personal macro workbook.
  1. Click Record Macro and then, in the Store In list, select Personal Macro Workbook.
  2. Click OK to record your macro, and click Stop Recording when you're done.
If you have already created a macro in your current workbook and would like to copy it to your personal workbook, you can record an empty macro whose instructions you can then replace. Do the following:
  1. On the Developer tab, in the Code group, click Record Macro.
  2. In the Store In list, select Personal Macro Workbook.
  3. Give the macro the same name as the macro that you've already saved to your current workbook (that is, the macro whose instructions you want to copy).
  4. Click OK to start the Macro Recorder, and then immediately stop the recording.
  5. On the Developer tab, in the Code group, click Macros, select the macro in the current workbook that contains the instructions you want to copy, and then click Edit.
  6. In the Visual Basic Editor, select the entire macro, starting with "Sub" and ending with "End Sub."
  7. Click CTRL+C to copy the macro, and then close the Visual Basic Editor.
  8. Click Macros again and this time select the macro you just recorded to your personal macro workbook (in the list, it's probably preceded by PERSONAL.XLSB!).
  9. Click Edit to start the Visual Basic Editor, and select all the lines of the macro, starting with "Sub" and ending with "End Sub."
  10. Paste the macro that you previously copied over these instructions to replace them.
  11. Close the Visual Basic Editor to save your changes.
When you close Excel after you've saved a macro to your personal macro workbook, you'll be prompted to save the changes to the personal workbook. Be sure to click Yes to save those changes.
Loading