What VBA Is and When to Use It

A programming language that already lives in Excel

VBA stands for Visual Basic for Applications, and it is already inside your copy of Excel — nothing to download, nothing to install. You write instructions as plain text and Excel carries them out: the same clicks you would make by hand, in a fraction of a second and identically every time. By the end of this lesson you will have run a macro that builds a sales sheet by itself.

When a macro earns its keep

• Repetition — the same clean-up on a new export every Monday morning.
• Volume — 40,000 rows, where doing it by hand costs an afternoon.
• Consistency — a report that must look identical every month.
• Things formulas cannot do — create sheets, save files, print, show a dialog box.
• When not to bother — if a formula, a PivotTable or Power Query already solves it, use those instead. Nobody has to maintain them.

Give the code somewhere to live

• Open a blank workbook and press Alt+F11 (on a Mac, Fn+Alt+F11). The Visual Basic Editor opens in its own window.
• Choose Insert → Module. An empty page called Module1 appears: that is where macros go.
• Type the three lines below into it, click inside them, press F5.

Sub SayHello()
    MsgBox "This is my first macro."
End Sub

Now the real one

A message box proves the machinery works. The next macro does what would otherwise take a hundred clicks, and it builds its own sample data, so it needs no file from you. Paste it underneath, click inside it and press F5.

Option Explicit

Sub BuildDemoSalesSheet()
    Dim ws As Worksheet
    Dim products As Variant
    Dim rowCount As Long
    Dim lastRow As Long
    Dim totalRow As Long
    Dim i As Long

    products = Array("Coffee", "Tea", "Cocoa", "Juice")
    rowCount = 12

    Set ws = ThisWorkbook.Worksheets.Add
    ws.Name = "Demo " & Format(Now, "hh-mm-ss")

    ws.Range("A1:E1").Value = Array("Date", "Product", "Units", "Price", "Revenue")

    For i = 1 To rowCount
        ws.Cells(i + 1, 1).Value = DateSerial(2026, 1, i)
        ws.Cells(i + 1, 2).Value = products(i Mod 4)
        ws.Cells(i + 1, 3).Value = 10 + i * 3
        ws.Cells(i + 1, 4).Value = 2.5 + (i Mod 4)
        ws.Cells(i + 1, 5).Formula = "=C" & (i + 1) & "*D" & (i + 1)
    Next i

    lastRow = rowCount + 1
    totalRow = lastRow + 1

    ws.Range("A1:E1").Font.Bold = True
    ws.Range("A1:E1").Interior.Color = RGB(226, 232, 240)
    ws.Range("A2:A" & lastRow).NumberFormat = "dd.mm.yyyy"
    ws.Range("D2:E" & lastRow).NumberFormat = "#,##0.00"

    ws.Cells(totalRow, 4).Value = "Total"
    ws.Cells(totalRow, 5).Formula = "=SUM(E2:E" & lastRow & ")"
    ws.Range(ws.Cells(totalRow, 4), ws.Cells(totalRow, 5)).Font.Bold = True

    ws.Columns("A:E").AutoFit

    MsgBox "Sheet " & ws.Name & " created with " & rowCount & " rows.", _
        vbInformation, "Demo sheet ready"
End Sub

What the lines actually do

• Sub … End Sub — the container. Everything between is the macro, and BuildDemoSalesSheet is the name Excel lists under Alt+F8.
• Option Explicit at the top of the module makes VBA insist that every variable is declared. Turn it on and leave it on.
• Dim ws As Worksheet — a labelled box that will hold a sheet. Each Dim announces one variable and what goes in it.
• Set ws = ThisWorkbook.Worksheets.Add — adds a sheet and keeps hold of it. Objects need Set; numbers and text do not.
• For i = 1 To rowCount … Next i — runs the indented block twelve times, with i counting 1, 2, 3 …
• ws.Cells(i + 1, 3) — row and column as numbers, so row 1 stays free for the headers.
• .Value writes a value, .Formula writes a formula, and & glues text together — so row 5 receives =C5*D5.
• The last statement ends in a space and an underscore: VBA's line continuation.

Change one number and run it again

Reading code teaches far less than breaking it, and each run creates a fresh tab, so nothing here can spoil earlier output. Try these edits one at a time:

    rowCount = 40                                          ' 40 rows instead of 12
    ws.Range("A1:E1").Interior.Color = RGB(255, 235, 156)  ' an amber header
    ws.Cells(totalRow, 4).Value = "Grand total"            ' your own wording

The trap: .xlsx silently throws your code away

Your macro lives inside the workbook, not inside Excel. Save the file the ordinary way and Excel offers .xlsx, a format that cannot store code at all. It warns you once, in a dialog most people confirm by reflex, and the module is gone when you reopen the file. Choose Excel Macro-Enabled Workbook (.xlsm) instead — and on reopening, click Enable Content on the yellow bar, or the macros stay dead without explanation.

How it goes on

The next lesson takes the editor apart: the panes that matter, the Immediate window for testing a single line, and where code must sit for Excel to find it. Then you write a macro from scratch, and the following lessons cover what this one used without explaining — procedure names, Dim and Option Explicit, MsgBox and InputBox. The course closes with the four ways to start a macro and how to read the yellow line when one stops.