Excel Macros for Beginners: Record One, Read It, Run It Safely

Recording is the beginner route, VBA is what it writes

Microsoft describes two layers. Recording captures your mouse clicks and keystrokes, and you can edit the result afterwards for minor changes. Underneath, the recording is Visual Basic for Applications (VBA) code, so a recorded macro is also a free lesson in VBA.

Microsoft's own example is formatting customer names with overdue accounts in red and bold each month. If you repeat a change more than ten or twenty times, the VBA overview says it may be worth automating, and for hundreds of times it is certainly worth considering. For a simple repeat, it also suggests trying Ctrl+Y (Redo) first.

Show the Developer tab first

The macro buttons live on the Developer tab, and Excel hides that tab by default. On Windows, go to File > Options > Customize Ribbon and tick Developer. On a Mac, go to Excel > Preferences > Ribbon & Toolbar, tick Developer under Main Tabs, and select Save.

Once it is visible, the tab has the Visual Basic and Macros buttons, plus Record Macro in the Code group. The steps below apply to Excel for Microsoft 365 on Windows and Mac, Excel 2024, and Excel 2021 for Mac.

A worked example: record a macro that writes Hello World

This is the exercise in Microsoft's VBA guide, and it takes under 2 minutes. Open a new workbook and choose Record Macro on the Developer tab. Accept the defaults, which are Macro1 as the name and This Workbook as the place to store it. Choose OK.

Click cell B1 and type Hello World. The Stop Recording button is grayed out while you are still typing, because Excel waits for you to finish the entry. Click B2 to complete the entry, then choose Stop Recording.

Choose Macros, select Macro1 and press Edit. The Visual Basic Editor shows about 3 lines of real code: Range("B1").Select, then ActiveCell.FormulaR1C1 = "Hello World", then Range("B2").Select. Notice that your click on B2 became a line of code, because the recorder records every action.

Reading and changing the recorded code

Lines that start with an apostrophe are comments, and VBA ignores them. The Sub line starts the macro and End Sub closes it. Microsoft warns that recorded code can look confusing, because the recorder makes assumptions about what you meant.

Try one edit. Replace the body with Worksheets(1).Range("A1").Value = "Wow!", then add Worksheets(1).Range("A1").Borders.LineStyle = xlDouble. Back in Excel, choose Macros, pick Macro1 and press Run. A1 now says Wow! with a double border.

Strings need double quotes in VBA. Single quotes give the error "Compile error: Expected: expression". Misspelling a property, for example ValueX instead of Value, gives the run-time message "Object doesn't support this property of method" and a yellow highlight on the failed line.

Saving: .xlsx throws the macro away, .xlsm keeps it

According to Microsoft's list of supported formats, .xlsx cannot store VBA macro code or Excel 4.0 macro sheets. The .xlsm format stores both. Macro-enabled templates use .xltm, and macro-enabled add-ins use .xlam. The .xlsb format is binary (BIFF12).

Choose .xlsm in Save As before you close a file that holds a macro you want to keep.

Running a macro, and a shortcut warning

Microsoft lists several ways to run a macro: Developer > Macros > Run, a shortcut key, a button on the Quick Access Toolbar or ribbon, or right-clicking a shape or picture and choosing Assign Macro. In the Visual Basic Editor you can click inside the macro and press F5.

Shortcut keys follow a pattern. On Windows, a lowercase letter becomes Ctrl+letter and an uppercase letter becomes Ctrl+Shift+Letter. On a Mac, lowercase is Option+Command+letter, though Ctrl+letter also works. A macro shortcut can override a default Excel shortcut while that workbook is open, so Microsoft suggests Ctrl+Shift combinations.

Macro Settings: four choices in the Trust Center

In File > Options > Trust Center > Trust Center Settings > Macro Settings, Excel offers 4 choices. In the Excel wording they read Disable VBA macros without notification, Disable VBA macros with notification, Disable VBA macros except digitally signed macros, and Enable VBA macros (not recommended, potentially dangerous code can run).

Microsoft's policy page marks Disable with notification as the default. A separate checkbox decides whether the same rule applies to old Excel 4.0 (XLM) macros: when it is cleared, XLM macros are disabled without notification. Access to the VBA project object model is blocked by default.

You do not need to enable macros to view or edit a file. Microsoft's advice is to enable them only when you are sure what they do.

Why a downloaded file shows a Security Risk banner

Since 2022, Office on Windows blocks macros in files from the internet. Current Channel version 2206 began rolling out on July 27, 2022, and the Semi-Annual Enterprise Channel got version 2208 on January 10, 2023. The change does not apply to Office on Mac, Android, iOS or the web.

Windows adds a Mark of the Web to downloads and email attachments, stored as a ZoneId. A ZoneId of 3 means Internet and blocks macros, while 2 means Trusted sites and does not. The banner says Security Risk and has no Enable content button.

If you trust the file, right-click it, choose Properties, and tick Unblock on the General tab. The PowerShell command Unblock-File does the same. Then reopen the file. Saving the file in a Trusted Location also skips the block. The Mark of the Web only exists on NTFS drives, not FAT32.

Who this suits and who should wait

Recording suits tasks you do the same way each time on the same layout, such as formatting a monthly report. It suits people who can open a file and check what changed. It does not suit tasks that need a decision halfway through, and it does not suit files from unknown senders. Only run macros from files that you trust.

If a security banner or a VBA error message on your screen is unclear, Crade can read it and explain it in plain words.