Three Kinds of Error

Not all bugs are the same

Before you can fix a problem you need to know what kind of problem it is. VBA gives you exactly three, and each one is found in a different way. Recognising which one you are looking at saves an enormous amount of wasted time.

The three types

• Compile errors — the code is not valid VBA. A missing End If, a misspelled keyword. Excel refuses to run anything until you fix it.
• Run-time errors — the code is valid but reality disagrees. Dividing by zero, opening a file that is not there, asking for Worksheets("Data") when there is no Data sheet.
• Logic errors — the code runs perfectly and produces the wrong answer. No message, no yellow line, just a total that is 200 too high.
• Only the third one is genuinely hard — and that is what breakpoints and the Immediate window are for.

Option Explicit removes a whole category

Put Option Explicit on the first line of every module. It forces you to declare every variable, which means a typo like totl instead of total becomes a compile error you see instantly, rather than a silent empty variable that quietly makes your report wrong. Switch it on permanently in the VBA editor under Tools → Options → Require Variable Declaration.

Option Explicit

Sub AverageAmount()
    Dim ws As Worksheet
    Dim total As Double
    Dim count As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    total = Application.Sum(ws.Range("C2:C1000"))
    count = Application.CountA(ws.Range("C2:C1000"))

    If count = 0 Then
        MsgBox "No data to average.", vbInformation
    Else
        MsgBox "Average: " & Format(total / count, "#,##0.00")
    End If
End Sub

Compile before you run

In the VBA editor choose Debug → Compile VBAProject. This checks the whole project in one go instead of stopping at the first line that happens to execute. It takes a second and it catches the entire compile-error category before a user ever sees it. Do it every time before you hand a workbook over.