Day 1 — What VBA is, and your first macro

VBA is how you stop doing the same Excel job by hand every week. Day one is the whole shape of it.

What VBA actually is

VBA is a small programming language that lives inside Excel. It can do anything you can do with the mouse and keyboard - and it can do it to 40,000 rows while you get a coffee.

People reach for it when a report is the same every month. Open four files, filter, copy, paste, format, save. That is two hours of clicking, and it is exactly the kind of job a computer should be doing.

Where the code lives

Step	What you do
1	Press Alt + F11 in Excel. The Visual Basic Editor opens.
2	Insert → Module. A blank white page appears - that is where code goes.
3	Type your macro, click inside it, press F5 to run.
4	Save the file as .xlsm. A normal .xlsx silently throws your code away.

The .xlsm trap This catches everybody once. If you save a workbook containing macros as .xlsx, Excel drops the code without much of a fight. Macro-enabled workbooks are .xlsm. If your macros vanished overnight, this is why.

Your first macro

Every piece of VBA you run sits between Sub and End Sub. Sub is short for subroutine, which just means "a job with a name".

Sub SayHello()
    MsgBox "Hello from VBA"
End Sub

Press Run. The grey box below the code is the message box Excel would pop up. Change the text and run it again - this is your code, edit it freely.

Debug.Print, the tool you will use most

MsgBox stops everything and waits for a click. That is fine once, and unbearable inside a loop that runs 500 times. Debug.Print writes quietly to the Immediate window instead.

Sub ShowNumbers()
    Debug.Print "Two plus three is", 2 + 3
    Debug.Print "Half of seven is", 7 / 2
    Debug.Print "Seven divided by two, whole part only", 7 \ 2
End Sub

In real Excel you see that output by pressing Ctrl + G in the editor. Here it is the dark panel. Get into the habit now: when a macro misbehaves, Debug.Print the values and the reason is usually obvious in ten seconds.

Two divisions, on purpose / is ordinary division and gives 3.5. \ is integer division and gives 3 - it throws the remainder away. VBA has both, and mixing them up is a classic first-week bug.

Comments

Anything after an apostrophe is ignored by Excel and read by humans. Write them for the version of you that opens this file in eight months.

Sub Commented()
    ' This line does nothing at all
    Debug.Print "Only this line runs"   ' ...and this bit is ignored too
End Sub

The sheet everything runs against

Every macro on this page runs against the Sales sheet below - 16 orders in A1:G17. Column H is empty on purpose; filling it is the first real job you will do.

Sixteen orders, headers in row 1, data in rows 2 to 17. Every example in this tutorial reads or writes this sheet, and the grid redraws after each run so you can see exactly what your macro did.

What this playground can and cannot do

The VBA language is a language, so that part runs here for real: variables, loops, decisions, procedures, and genuine Range and Cells work against a genuine worksheet. A loop that is wrong here is wrong in Excel too.

Not covered here Recording macros, the editor windows, buttons and form controls, UserForms, opening other workbooks, PivotTable and Chart objects, and error handling with On Error. Those need Excel itself in front of you - that is the classroom course. What you get here is the part people actually get stuck on: writing the loop and getting the right answer.

Try these yourself

Change SayHello so the message box shows your own name.
Use Debug.Print to show the result of 17 divided by 5, both ways (/ and \).
Add a comment above each line of ShowNumbers explaining what it does.
Explain in one sentence why a workbook with macros must be saved as .xlsm.
What is the difference between MsgBox and Debug.Print, and when would you choose each?

← BackTutorial home Next →Day 2 — Variables, types and Option Explicit