How to Use the Macro Recorder in Excel 2016 - dummies

How to Use the Macro Recorder in Excel 2016

By John Walkenbach

One way you can get code into a VBA module is by recording your actions, using the Excel macro recorder. One thing you need to remember is that you can record only things that you can do directly in Excel. Displaying a message box is not in Excel’s normal repertoire. (It’s a VBA thing.) The macro recorder is useful, but in many cases, you’ll probably need to enter at least some code manually.

Here’s a step-by-step example that shows you how to record a macro that inserts a new worksheet and hides all but the first ten rows and all but the first ten columns. If you want to try this example, start with a new, blank workbook and follow these steps:

  1. Activate a worksheet in the workbook.

    Any worksheet will do.

  2. Click the Developer tab, and make sure that Use Relative References is not highlighted.

    This macro will be recorded using Absolute References.

  3. Choose Developer→Code → Record Macro, or click the icon next to the Ready indicator on the left end of the status bar.

    Excel displays its Record Macro dialog box.

  4. In the Record Macro dialog box, name the macro TenByTen, and press Shift+T for the shortcut key.

    The macro can be executed when you press Ctrl+Shift+T.

  5. Click OK to start recording.

    Excel automatically inserts a new VBA module into the project that corresponds to the active workbook. From this point on, Excel converts your actions to VBA code. While you’re recording, the icon in the status bar turns into a small square. This is a reminder that the macro recorder is running. You can also click that icon to stop the macro recorder.

  6. Click the New Sheet icon to the right of the last sheet tab.

    Excel inserts a new worksheet.

  7. Select the entire Column K (the 11th column) and press Ctrl+Shift+right arrow; then right-click any selected column and choose Hide from the shortcut menu.

    Excel hides all of the selected columns.

  8. Select the entire Row 11 and press Ctrl+Shift+down arrow; then right-click any selected row and choose Hide from the shortcut menu.

    Excel hides all of the selected columns.

  9. Select cell A1.

  10. Choose Developer → Code → Stop Recording, or click the Stop Recording button on the status bar (the small square).

    Excel stops recording your actions.

To view this newly recorded macro, press Alt+F11 to activate the VBE. Locate the workbook’s name in the Project window. You see that the project has a new module listed. The name of the module depends on whether you had any other modules in the workbook when you started recording the macro. If you didn’t, the module will be named Module1. You can double-click the module to view the Code window for the module.

Here’s the code generated by your actions:

Sub TenByTen()
‘ TenByTen Macro
‘ Keyboard Shortcut: Ctrl+Shift+T
  Sheets.Add After:=ActiveSheet
  Range(Selection, Selection.End(xlToRight)).Select
  Selection.EntireColumn.Hidden = True
  Range(Selection, Selection.End(xlDown)).Select
  Selection.EntireRow.Hidden = True
End Sub

To try out this macro, activate any worksheet and press the shortcut key that you assigned in Step 4: Ctrl+Shift+T.

If you didn’t assign a shortcut key to the macro, don’t worry. Here’s how to display a list of all macros available and run the one you want:

  1. Choose Developer → Code → Macros.

    Keyboard fans can press Alt+F8. Either of these methods displays a dialog box that lists all the available macros.

  2. Select the macro in the list (in this case, TenByTen).

  3. Click the Run button.

    Excel executes the macro, and you get a new worksheet with ten visible rows and ten visible columns.

You can execute any number of commands and perform any number of actions while the macro recorder is running. Excel dutifully translates your mouse actions and keystrokes to VBA code.

And, of course, you can also edit the macro after you record it. To test your new skills, try editing the macro so that it inserts a worksheet with nine visible rows and columns — perfect for a Sudoku puzzle.