What VBA Is

VBA, Visual Basic for Applications, is the programming language built into Excel. It automates the repetitive work you would otherwise do by hand: formatting a hundred rows, deleting unwanted data, creating worksheets, applying passwords, building reports.

A macro is simply a block of VBA code. You can generate one by recording your actions, or write it directly in the Visual Basic Editor. Either way, the code lives inside the workbook and runs when you trigger it.

This guide covers the essentials and then walks through four practical macros that solve everyday problems. It is the tutorial companion to our VBA and macros service, and if you are deciding between Excel macros and no-code tools, see VBA vs n8n: which automation approach for the bigger picture.

Before You Start: Macro Security

Excel blocks macros by default. To run your own macros:

1. Save the workbook as macro-enabled: use File > Save As and choose "Excel Macro-Enabled Workbook" (.xlsm). A regular .xlsx cannot store macros.
2. Enable content: when you reopen the file, Excel shows a security warning bar. Click Enable Content for files you created yourself and trust.
3. Keep the default security setting: File > Options > Trust Center > Macro Settings. Leave it on "Disable VBA macros with notification". Never set it to "Enable all macros" as a habit; that is how malicious files run.

Workbooks from unknown sources that contain macros are a genuine risk. Only enable content you trust, and keep a backup of anything important before running an unfamiliar macro.

The Visual Basic Editor

Open the editor with Alt + F11. The Project Explorer on the left lists your workbook and its sheets. To add code:

1. Right-click the workbook name in the Project Explorer
2. Choose Insert > Module
3. Paste or type your code in the white editor pane

A module is just a container for procedures. You can have many modules in one workbook.

Run any macro with Alt + F8, select it, and click Run. To step through a macro line by line, press F8 in the editor; this is the best way to see exactly what your code does and find errors.

Your First Macro: Recorded

The macro recorder is the fastest way to get working code.

1. Click the Developer tab (if it is missing, right-click the ribbon, choose Customize the Ribbon, and tick Developer)
2. Click Record Macro, name it, and click OK
3. Do your actions: format a range, apply borders, set column widths
4. Click Stop Recording
5. Open Alt + F11 and look at the generated module

The recorder writes real VBA you can read and edit. Run the macro on a new sheet and it replays your exact actions. Recorded code is literal (it refers to the exact ranges you touched), which is why the next step is learning to write code that adapts.

Four Practical Macros

1. Delete rows based on a cell value

The classic cleanup task: remove every row where a column equals a certain value. This macro deletes all rows where column C contains "1", starting at row 3:

Sub DeleteCriteria()
    Dim i As Long, LastRow As Long
    LastRow = Range("C" & Rows.Count).End(xlUp).Row
    For i = LastRow To 3 Step -1
        If Cells(i, "C").Value = "1" Then
            Cells(i, "C").EntireRow.Delete
        End If
    Next i
End Sub

The loop runs backwards (Step -1) because deleting a row shifts everything below it up. Looping backwards means the row numbers stay valid as you delete.

Change the criteria column ("C") and the value ("1") to match your data. For text criteria, use If Cells(i, "C").Value = "Closed" Then.

2. Add a new worksheet to the end of the workbook

A common reporting task: create a new sheet named from a cell, check the name does not already exist, and place the sheet at the end.

Sub CreateSheet()
    Dim WSName As String, Found As Boolean
    Dim i As Long
    Found = False
    WSName = Sheets("Main").Range("C5").Value

    For i = 1 To ActiveWorkbook.Sheets.Count
        If UCase(WSName) = UCase(ActiveWorkbook.Sheets(i).Name) Then
            Found = True
            Exit For
        End If
    Next i

    If Found = False Then
        Sheets.Add.Name = WSName
        ActiveSheet.Move After:=Worksheets(Worksheets.Count)
        Sheets("Main").Select
    Else
        MsgBox "Sheet " & WSName & " already exists."
    End If
End Sub

The UCase check catches duplicate names regardless of capitalisation, and the MsgBox stops you from hitting Excel's duplicate-name error.

3. Protect or unprotect all worksheets

Protecting a dozen sheets one by one is exactly the kind of work VBA should do. These two macros protect and unprotect every sheet in the workbook with one password:

Sub ProtectSheets()
    Dim P As Integer
    For P = 1 To Worksheets.Count
        Worksheets(P).Protect Password:="YourPassword"
    Next P
End Sub

Sub UnprotectSheets()
    Dim U As Integer
    For U = 1 To Worksheets.Count
        Worksheets(U).Unprotect Password:="YourPassword"
    Next U
End Sub

Replace YourPassword with a real password and update both macros together, otherwise you will lock yourself out. You can assign shortcut keys (Alt + F8 > Options) so one keystroke protects the whole workbook.

4. Require a password to open or modify a workbook

To stop a file being opened or edited without a password, do not use VBA at all. Use the built-in save options:

1. File > Save As
2. Click Tools > General Options
3. Enter a password to open, a password to modify, or both
4. Tick "Read-only recommended" if you want that prompt as well

For a code-based approach, the same settings are available through VBA when you need to apply them programmatically across many files:

Sub PasswordProtect()
    ActiveWorkbook.Password = "YourPassword"
    ActiveWorkbook.Save
End Sub

Run it once per file, or loop over files in a folder to protect a whole directory in one pass.

Writing Clean VBA: Five Habits

1. Declare variables: start modules with Option Explicit so every variable must be declared with Dim. It catches typos before they corrupt data.
2. Name things clearly: LastRow not lr, WSName not x. You will read this code again in six months.
3. Comment the intent: a line explaining why a loop runs backwards is worth more than the code itself.
4. Test on a copy: run new macros against a duplicate of the file. Undo does not work after a macro runs.
5. Use the Immediate window: press Ctrl+G in the editor and type ?Range("A1").Value to inspect values while debugging.

Related reading

• VBA vs n8n: Which Automation Approach Suits Your Team?
• Excel VBA vs Custom Software: When a Spreadsheet Is Enough
• Business Automation with Excel: A Practical Guide