Personal training advice

Live online training for international teams.For businesses
EnquireFind my course
Excel & VBA

Create an Excel macro: step by step with VBA

Create your first Excel macro: record it, read and edit the VBA code, run it from a button and save the workbook as .xlsm.

Programming code on a screen

The short answer: how an Excel macro is made

To create an Excel macro, show the Developer tab, select “Record Macro”, carry out the task once and stop the recording. While you work, Excel writes VBA code that you can read, shorten and extend in the Visual Basic Editor (Alt+F11). To keep the macro, save the file as an Excel Macro-Enabled Workbook (*.xlsm).

The labels apply to Excel for Windows with Microsoft 365; some menus differ in older versions and on a Mac. Work in a copy of your file, because changes made by a macro cannot be reversed with Undo.

Step 1: Show the Developer tab

The Developer tab is hidden by default. Go to File > Options > Customize Ribbon and tick “Developer” under “Main Tabs”. On a Mac, the setting is under Excel > Preferences > Ribbon & Toolbar. The Code group now holds the commands you need: Visual Basic, Macros, Record Macro and Macro Security.

Step 2: Record your first macro

Take a table with headings in row 1, for example customer, date and amount in columns A to C. Select Developer > Record Macro and fill in the four fields listed below.

Recording starts when you select OK. Select A1 to C1, make the cells bold and select “Stop Recording”. The recorder captures almost every click, including slips. One point matters later: a recorded macro works on exactly the range you selected while recording. Rows added afterwards are not included.

  • Macro name: starts with a letter; letters, numbers and underscores may follow, spaces may not. Example: FormatHeader.
  • Shortcut key: optional. Ctrl plus a letter overrides the matching Excel shortcut while the file is open.
  • Store macro in: “This Workbook” for macros that belong to the file; “Personal Macro Workbook” for macros you want in every Excel session.
  • Description: one sentence on what the macro does.

Step 3: Read and edit the recorded VBA code

Go to Developer > Macros, choose your macro and select “Edit” to open the code in the Visual Basic Editor. It starts with Sub FormatHeader() and ends with End Sub; what Excel should do sits in between. Green lines that begin with an apostrophe are comments and are not executed. The rest looks roughly like this: Range("A1:C1").Select and, below it, Selection.Font.Bold = True.

VBA addresses objects and changes their properties: Range("A1:C1") is the cell range, Font is its font and Bold is the property for bold type. The recorder selects first and then formats the selection. Replace both lines with one: Range("A1:C1").Font.Bold = True. As a test, change True to False, switch back to Excel and run the macro with Macros > Run. If a term is unfamiliar, put the cursor on it and press F1 for the help page.

Step 4: Write a small macro by hand

Now write a macro the recorder cannot produce, because it adapts to the length of the table. It makes the headings bold, colours negative amounts in column C red, adjusts the column widths and reports how many data rows it checked. Press Alt+F11, choose Insert > Module and type the following lines into the code window. If Option Explicit is already there, leave that line in place and type below it.

Sub MarkNegativeValues()
Dim lastRow As Long
Dim i As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:C1").Font.Bold = True
For i = 2 To lastRow
If IsNumeric(Cells(i, "C").Value) Then
If Cells(i, "C").Value < 0 Then Cells(i, "C").Font.Color = vbRed
End If
Next i
Columns("A:C").AutoFit
MsgBox "Done: " & (lastRow - 1) & " data rows"
End Sub

What each line does

The two Dim lines create variables for whole numbers. The next line finds the last filled cell in column A: it jumps up from the bottom row of the sheet, as End followed by the Up Arrow key would, and stores the row number. The For … Next loop runs from row 2 to that row. On each pass, IsNumeric first checks whether column C holds a number at all; only then does the macro compare the value and turn the font red if it is below zero. MsgBox shows a message window at the end.

Put the cursor between Sub and End Sub and press F5 to run the macro. It works on whichever sheet is active, because Range and Cells with no object in front of them mean the active sheet.

Step 5: Run the macro from a button

Whoever uses the file later should not have to hunt through menus. Select Developer > Insert and choose “Button” under “Form Controls”. Click the spot on the sheet where the top-left corner should be. Excel opens the “Assign Macro” window: choose MarkNegativeValues and confirm with OK.

You can attach a macro to a shape or picture in the same way: right-click > “Assign Macro”. ActiveX command buttons look similar, but they need their own code and are not supported on a Mac.

Step 6: Save as .xlsm

The default .xlsx format cannot store VBA code. So choose File > Save As and pick “Excel Macro-Enabled Workbook (*.xlsm)” under “Save as type”. If you save as .xlsx instead, Excel warns that the VBA code cannot be saved in a macro-free workbook. “Yes” saves without macros anyway, and your code is gone once you close the file. “No” takes you back so that you can change the file type. In newer versions of Excel the two buttons may be labelled differently; the message itself then says which one saves without macros.

Macros you need in every file belong in the personal macro workbook, Personal.xlsb. Excel creates it when you choose “Personal Macro Workbook” while recording and opens it hidden each time it starts. When you close, Excel asks whether to save the personal macro workbook as well. Confirm, or the new macro will be missing the next time you start Excel.

Macro security: what the settings mean

Macros can run any code, so Excel does not start them unasked. The settings are under File > Options > Trust Center > Trust Center Settings > Macro Settings, or via Developer > Macro Security. The default is “Disable VBA macros with notification”: when you open a file containing macros, a security warning appears and the macros only run after you select “Enable Content”. Leave this setting as it is. Microsoft labels “Enable VBA macros” as not recommended, because harmful code would then run without asking.

Office for Windows is stricter with files from the internet, such as downloads and email attachments. Instead of the warning, a “Security Risk” notice appears with no button to enable the macros. If you really trust the source, save the file locally, right-click it in File Explorer, open “Properties” and tick “Unblock” at the bottom of the General tab. For your own macro files, you can also set a folder as a trusted location in the Trust Center. If you do not know what a macro in someone else’s file is for, do not enable it.

Common errors and how to find them

VBA has two kinds of error. The editor spots syntax errors straight away: the line turns red and the macro will not start. Run-time errors only appear while the macro is running. Select “Debug” in the error message and the editor highlights the affected line in yellow. Correct it and select the green arrow in the toolbar to carry on.

  • Nothing happens and a security warning sits at the top: the macros are still disabled. Check where the file came from before you enable content.
  • The code has vanished after reopening the file: it was saved as .xlsx. Save as .xlsm.
  • Compile error “Variable not defined”: the module contains Option Explicit and a variable is undeclared or misspelt. That is intended, because typing mistakes show up at once. Put Option Explicit at the top of every module.
  • Run-time error 9 (“Subscript out of range”): you are referring to something that does not exist, for example a worksheet whose name is misspelt.
  • Run-time error 13 (“Type mismatch”): a value has the wrong type, for instance text where the code compares numbers. In the example, the IsNumeric check prevents this.

Where to go from here

Stick to small, real tasks, such as a report you format the same way every week. Record the process, read the code, delete unnecessary Select lines and replace fixed ranges with variables. After that, conditions with If, loops over sheets and files, your own functions and error handling are worth learning. Also ask first whether a built-in Excel feature already solves the task. Not everything needs code.

If you would rather learn this with guidance: NextNowa offers training on this topic on enquiry.

Sources and review date

Official information checked on 17 November 2026. General information, not legal advice.

NextNowa Guide

Your training guide

What would you like to learn next?

Ask about courses, learning paths or advice. I will help you find your next step.

Automated guidance using reviewed information. No booking or funding confirmation. Please avoid sensitive data.