Creating a macro in Microsoft Excel 2019

A macro is a recording of a series of steps that are performedregularly. You can compare this to a recorded voice that is played backover and over when required. An example of a simple macro is to recordthe steps to create a header and footer in a certain format for all yourworksheets. A macro should be used to save the user time by automating aprocess.

Macros are usually assigned to buttons or icons and reside under theExcel ribbon. This way, they can be used with the click of a button tomake changes to an existing worksheet. When recording macros, mouseactions and keystrokes are not recorded. So, any errors made whenrecording, such as fixing a typing mistake, will not show up in themacro when playing it back—only the corrected entry will.

As with any other actions on the Excel interface, there are multipleways to perform tasks within the environment. Therefore, there is morethan one method to record macros:

  • The first method is to click on the macro indicator icon located onthe leftmost side of the status bar:
ms office 486
  • The second method is via the View| Macros icon:
ms office 621
  • The third method is via the Macrosicon on the leftmost side of the Developer tab (note that this is onlyvisible if you have added the Developer tab to the ribbon):
ms office 289

Macros are stored in the current workbook, a new workbook, or in theglobal personal macro workbook named personal.xlsb that isstored in a folder called XLSTART on your computer. Thepersonal macro workbook opens up in the background every time you openMicrosoft Excel. When macros are stored in the personal macro workbook,they can be run on any open workbook.

It is very important to plan the steps of the macro, as recordingunnecessary steps could lead to having to use the undo key ortypographic errors in the document.You only want to record what isrequired. Without taking this planning step, you will find thatrecording your macro takes longer than necessary.

We will record a macro to set the worksheet to Landscape, create a custom header with thedate on the left and the filename on the right, insert a custom footerwith the page number and the number of pages in the center, and centerthe data horizontally and vertically on the page:

  1. Open up the workbook from the previous example to record the macroin.
  1. Go to View | Macros | RecordMacro… to start recording:
ms office 342
  1. The Record Macro… dialog boxappears, where you can enter a name for the macro. For this example, wewill use PrintSettings as the macro name. You can also entera shortcut key to run the macro after creating it.
  2. Choose to store the macro in this workbook, to the personal macroworkbook, or to a new workbook. For this example, we will store themacro in the personal macro workbook.
  3. It is always important to enter a description of what the macro willdo so that other users are aware of how it changes the workbook databefore using it:
ms office 602
  1. Click on OK to startrecording.
  2. Perform the previous steps, just as if you are working on thecurrent workbook.
  3. Notice that the Ready indicator onthe status bar changes to recording mode. To stop the recording, eitherclick on the stop recording icon alongside the Ready indicator on the status bar, goto View | Macros | Stop Recording, oruse the Stop Recording icon under theDeveloper tab:
ms office 721
  1. Close the workbook without saving it as we only used the workbook tocreate the macro and have not added any extra importantinformation.
  2. Open the workbook you just closed, then go to File | Print to get to the print previewpane. Notice that the workbook is set to Portrait with no header or footer. Goto View | Macros to see a list of themacros available.
  1. Click to select the PrintSettings macro, then click Run:
ms office 88
  1. The macro is immediately applied to the workbook. It happens soquickly behind the scenes that you will not even notice all the changesmade in a split second. Return to the print preview pane to see thechanges made to the workbook by running the macro.
  2. Save the workbook to keep the print settings applied to theworkbook.

Leave a Comment