Course contents · Macros and VBA

Debugging, events and other ways to automate

Open the practice in the trainer →

Three kinds of errors

Syntax errorsa typo, a missing End If or bracket — the editor highlights the line even before running
Run-time errorsdivision by zero, a missing sheet, type mismatch — the macro stops with an error number; Debug shows the line
Logic errorsthe macro runs but the result is wrong — found by stepping through the code

Debugging tools

F8run one line at a time; hover over a variable to see its value
F9a breakpoint on the line: the macro stops when it reaches it
Ctrl+Gthe Immediate window: Debug.Print writes there, and you can evaluate ? Range("A1").Value
View → Locals / Watchall variables / watched expressions

On Error Resume Next skips errors (use with care), On Error GoTo Label jumps to a handler.

Events and buttons

Other ways to automate

Office Scriptsthe Automate tab: TypeScript scripts that work in Excel for the web and with Power Automate
Power QueryData → Get Data: cleaning and combining data without code; the steps are remembered and replayed on Refresh
LAMBDAyour own functions as formulas, without VBA
Python in Excelthe =PY() formula in Microsoft 365: pandas and charts right in a cell

Excel for Mac. VBA works on a Mac too: Tools → Macro → Visual Basic Editor (⌥+F11). Step into is ⇧+⌘+I, and there are no ActiveX controls.

Tasks in the VBA editor

  1. The Report macro has two errors. Run it, read the messages in the Output box, fix the code — C2:C6 must show "pass" / "fail".
  2. The same macro must put the total score into B7 and print it with Debug.Print.

Solve the tasks in the trainer →

Check yourself

  1. Which key runs a macro one line at a time?
  2. Where do you put code that must run when cells on a sheet change?
  3. Which way of automating works in Excel for the web?