Debugging, events and other ways to automate
Open the practice in the trainer →
Three kinds of errors
| Syntax errors | a typo, a missing End If or bracket — the editor highlights the line even before running |
| Run-time errors | division by zero, a missing sheet, type mismatch — the macro stops with an error number; Debug shows the line |
| Logic errors | the macro runs but the result is wrong — found by stepping through the code |
Debugging tools
| F8 | run one line at a time; hover over a variable to see its value |
| F9 | a breakpoint on the line: the macro stops when it reaches it |
| Ctrl+G | the Immediate window: Debug.Print writes there, and you can evaluate ? Range("A1").Value |
| View → Locals / Watch | all variables / watched expressions |
On Error Resume Next skips errors (use with care), On Error GoTo Label jumps to a handler.
Events and buttons
- Code in a sheet module,
Private Sub Worksheet_Change(ByVal Target As Range), runs on every cell change; Workbook_Open in ThisWorkbook — when the workbook opens.
- A button for a macro: Developer → Insert → Button, or right-click any shape → Assign Macro.
- Keep macros you need in every workbook in the Personal Macro Workbook PERSONAL.XLSB (choose it when recording).
Other ways to automate
| Office Scripts | the Automate tab: TypeScript scripts that work in Excel for the web and with Power Automate |
| Power Query | Data → Get Data: cleaning and combining data without code; the steps are remembered and replayed on Refresh |
| LAMBDA | your own functions as formulas, without VBA |
| Python in Excel | the =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
- The Report macro has two errors. Run it, read the messages in the Output box, fix the code — C2:C6 must show "pass" / "fail".
- The same macro must put the total score into B7 and print it with
Debug.Print.
Solve the tasks in the trainer →
Check yourself
- Which key runs a macro one line at a time?
- Where do you put code that must run when cells on a sheet change?
- Which way of automating works in Excel for the web?