Macros and VBA: automate repetitive work

In this lesson you will learn

Enable the Developer tab, record a macro and run it
Understand the VBA object model (Workbook › Worksheet › Range)
Write a Sub with a For…Next loop and an If…Then condition, and create your own function
Handle macro security and file formats correctly

Every morning Nigar downloads a report and spends 10 minutes on the same routine: bold header, fill colour, column widths, unpaid invoices marked red. Over a year that is more than 40 hours. A macro records these actions and replays them with one keystroke; VBA (Visual Basic for Applications) is the programming language macros are written in.

Recording a macro

1
Show the Developer tab
File › Options › Customize Ribbon › tick Developer in the right-hand list › OK.
2
Start recording
Developer › Code › Record Macro. Name: FormatReport (no spaces), shortcut: Ctrl+Shift+R, Store macro in: This Workbook (or Personal Macro Workbook for all files) › OK.
3
Do the actions and stop
Format the header, fit the columns, then Developer › Code › Stop Recording. Every click becomes code, so avoid unnecessary clicks.
4
Run it
Alt+F8 › FormatReport › Run, or just Ctrl+Shift+R. You can also draw a button via Insert › Illustrations › Shapes, then right-click › Assign Macro….

Text

Sub FormatReport()
    Range("A1:F1").Select
    Selection.Font.Bold = True
    Selection.Interior.Color = RGB(221, 235, 247)
    Columns("A:F").Select
    Selection.Columns.AutoFit
End Sub

Code produced by the recorder (simplified): every click becomes a separate Select line.

Text

Sub FormatReport()
    With Range("A1:F1")
        .Font.Bold = True
        .Interior.Color = RGB(221, 235, 247)
    End With
    Columns("A:F").AutoFit
End Sub

The cleaned-up version: working with the object directly (With … End With) instead of Select/Selection makes the code shorter and faster.

The VBA editor and the object model

Alt+F11 opens the VBA editor (VBE). On the left, the Project Explorer (Ctrl+R) lists workbooks and modules; for new code use Insert › Module. In the Immediate window at the bottom (Ctrl+G) you can test one-line commands: type ?Range("A1").Value and press Enter. Everything in Excel is an object, and objects form a hierarchy:

Workbooks("Sales.xlsm").Worksheets("Data").Range("B2").Value = 1500

where:

Workbooks(…)a workbook (file)
Worksheets(…)a worksheet
Range(…) / Cells(row, col)a cell or range; Cells(2, 2) = B2
.Value, .Font, .ClearContentsproperties (what it has) and methods (what it does)

The path goes from big to small, separated by dots; if the active workbook and sheet are meant, Range("B2") alone is enough.

A macro that flags overdue invoices

Sheet “Invoices”, columns A:D — number, client, due date, status. Rows: INV-101 (15 Sep 2026, Paid), INV-102 (20 Sep 2026, Unpaid), INV-103 (30 Sep 2026, Unpaid), INV-104 (10 Sep 2026, Unpaid), INV-105 (25 Sep 2026, Paid), INV-106 (1 Oct 2026, Unpaid). Today is 27 Sep 2026. Colour the unpaid, overdue rows pink and show how many there are.

Show solution

The code below finds the last filled row, walks from row 2 to the end with For…Next and checks two conditions with If … And … Then.
Both conditions hold only for INV-102 (20 Sep < 27 Sep) and INV-104 (10 Sep < 27 Sep); INV-103 and INV-106 are not due yet.
Result: two rows are coloured and a message box says “2 overdue invoice(s)”.
The Else part clears old colours, so the macro can be rerun every day.

Text

Sub HighlightOverdue()
    Dim ws As Worksheet
    Dim lastRow As Long, r As Long, overdue As Long
    Set ws = ThisWorkbook.Worksheets("Invoices")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    For r = 2 To lastRow
        If ws.Cells(r, "D").Value = "Unpaid" And ws.Cells(r, "C").Value < Date Then
            ws.Cells(r, "A").Resize(1, 4).Interior.Color = RGB(255, 199, 206)
            overdue = overdue + 1
        Else
            ws.Cells(r, "A").Resize(1, 4).Interior.Pattern = xlNone
        End If
    Next r
    MsgBox overdue & " overdue invoice(s)", vbInformation
End Sub

Expected output

2 overdue invoice(s)

Dim declares variables, Date is today's date, and End(xlUp) jumps to the last filled cell like Ctrl+↑. To follow it step by step, put the cursor inside the Sub and press F8.

Type	What it holds	Example
`Long`	a whole number (row numbers, counts)	`Dim r As Long`
`Double`	a decimal number (amounts, rates)	`Dim rate As Double`
`String`	text	`Dim client As String`
`Date`	a date and time	`Dim due As Date`
`Worksheet`, `Range`	an object — assigned with `Set`	`Set ws = ActiveSheet`

Put Option Explicit at the very top of the module: an undeclared variable (for example overdeu instead of overdue) is flagged as an error at once.

To visit every cell of a range, For Each is shorter: For Each c In Range("C2:C7") … Next c. To speed up a macro on large tables, write Application.ScreenUpdating = False at the start and set it back to True at the end — the screen will not redraw after every change.

Your own function: price with VAT

Create a function used on the sheet as =WithVAT(100) and =WithVAT(250,0.1), with a default rate of 18%. What are the results?

Show solution

Write a Function in a module (unlike a Sub, it returns a value):
Function WithVAT(price As Double, Optional rate As Double = 0.18) As Double
WithVAT = price * (1 + rate)
End Function
=WithVAT(100) → 100 · 1.18 = 118; =WithVAT(250,0.1) → 250 · 1.1 = 275.
The function appears in the Insert Function list under the User Defined category.

Security and .xlsm

Format	Stores macros?	Note
.xlsx	no	if you save as this, Excel warns you and the code is removed
.xlsm	yes	macro-enabled workbook — the standard choice
.xlsb	yes	binary format: smaller and faster for large files
PERSONAL.XLSB	yes	personal macros — loaded hidden whenever Excel starts

Open/close the VBA editorAlt+F11

The Macros dialog: pick and runAlt+F8

In the VBE: run the Sub the cursor is inF5

In the VBE: step through line by lineF8

In the VBE: toggle a breakpointF9

In the VBE: the Immediate windowCtrl+G

Key points

A macro stores actions as VBA code; start with Developer › Record Macro and run with Alt+F8.
Object hierarchy: Workbook › Worksheet › Range; work with objects directly instead of Select.
For…Next walks through rows, If…Then…Else decides; Function creates your own worksheet function.
Ctrl+Z cannot undo a macro — test on a copy.
Macros are saved only in .xlsm/.xlsb; never enable macros in a file you don't trust.

Check yourself

10 questions. Every correct answer earns XP.

1 / 10

What happens if you save a workbook with macros as .xlsx?