Accounting

How to record macros in Excel

person working on a laptop while sitting on a couch

Macros allow you to easily automate repetitive Microsoft Excel tasks. For example, if you often apply a particular format to a range of cells, you can create a macro that performs the formatting task with the click of a button. Or perhaps there is a set of headings you use often in your worksheets. After creating the appropriate macro, a click of a button will insert these headings anywhere in your workbook.

In this article, we’ll share how to record and run macros.  

Please note, while you can accomplish a lot with recorded macros, many macros cannot be recorded and must be created using the Excel Visual Basic for applications (VBA) programming language.

Adding the Developer tab

To create or run a macro you need to access the Developer tab on the ribbon. If the Developer tab does not appear on the ribbon proceed as follows:

  1. From the File tab click on Options.
  2. In the left pane of the Excel Options dialog box, click Customize Ribbon.
  3. Select the Developer check box in the pane on the right (see Figure 1). Then click OK.

 

Figure 1: Adding the Developer tab to the ribbon

How to record a macro

To record a macro, proceed as follows:

  1. After clicking the Developer tab, you will see the options shown in Figure 2.

Figure 2: The Developer tab

  1. In the Code group on the Developer tab, click the Record Macro button. Alternatively, click the Ready icon in the lower-left corner of your screen. The Record Macro dialog box (see Figure 3) opens.

Figure 3: The Record Macro dialog box

  1. In the Macro Name box, replace the default name with one that’s more descriptive.
  2. If you want to use a shortcut key to run your macro, enter the key you want to use in the Shortcut Key box. A shortcut key is a sequence of keystrokes that can be used to run the macro. For example, if you enter Shift+F, then pressing Ctrl+Shift+F will cause your macro to run. When assigning a shortcut key, make sure you don’t choose one that’s already used to perform a task. For example, since Ctrl+B applies bold font, you cannot assign that shortcut key to your macro.
  3. By default, Excel stores the macro in the current workbook, making it available to run whenever the workbook is open. If you want your macro to always be available no matter what workbook you’re using, open the Store Macro In the drop-down list and select Personal Macro Workbook.
  4. Optionally, type a description of the macro in the Description box. Then click OK.
  5. Perform the steps you want to be repeated when you run the macro.
  6. In the Code group on the Developer tab, click the Stop Recording button. Alternatively, click the small gray rectangle that appears in the lower left-hand corner of your screen under the worksheet tabs.

Recording and running your first macro

Here’s an example that shows how you can record and run a useful macro. Suppose your work often requires you to highlight numerical data in a bold red font. Let’s record a macro to automate this task. Note that workbooks containing macros need to be saved in the .xlsm format.

To create and run this macro proceed as follows:

  1. In the Code group on the Developer tab click Record Macro.
  2. Fill in the Record Macro dialog box as shown in Figure 4. Then click OK.

Figure 4: Settings for the redbold macro

  1. Press Ctrl+1 to open the Format Cells dialog box.
  2. Click the Font tab, choose Bold in the Font Style list, and select a shade of red under Color. Then click OK.
  3. Click Stop Recording.
  4. Because the workbook contains a macro, save the workbook file as an Excel macro-enabled workbook. The workbook’s name is now Redbold.xlsm. The suffix .xlsm indicates the file is a macro-enabled workbook.
  5. Test the macro by entering data in cells J5:J7. After selecting J5:J7 and pressing Ctrl+Shift+R, cells J5:J7 will be formatted in a red font and bolded.

Trouble running a macro?

Recently, Office 365 made it more difficult to run a macro. If you open a .xlsm file and see the message shown in Figure 5, then close the file and find it in File Explorer. Then, right-click the file and choose Properties. Then, from bottom of the General Tab check Unblock. You should now be able to run the Macro.

Figure 5: New message indicating your macro is untrusted

Next month, we’ll share other ways to run a macro, record more complex macros and examine the VBA “code” created by a recorded macro.

Now Leaving Becker.com

You are leaving the Becker.com website. Once you click “continue,” you will be brought to a third-party website. Please be aware, the privacy policy may differ on the third-party website. Adtalem Global Education is not responsible for the security, contents and accuracy of any information provided on the third-party website. Note that the website may still be a third-party website even the format is similar to the Becker.com website.

Continue