Three Kinds of Error

Not all bugs are the same

Before you can fix a problem you have to know what kind it is. VBA gives you exactly three, and each is found in a different way. Knowing which one you are looking at is the difference between a two-minute fix and a lost afternoon.

The three types

• Compile errors — the code is not valid VBA: a missing End If, a misspelled keyword, a Next without a For. Excel refuses to run anything until you fix it, so these cost minutes, not hours.
• Run-time errors — the code is valid but reality disagrees: dividing by zero, opening a file that has been moved, asking for Worksheets("Data") after somebody renamed the sheet. You get a yellow line and a number.
• Logic errors — the code runs perfectly and gives the wrong answer. No message, no yellow line, just an average quietly 15 per cent too low on a report you already sent.
• Only the third kind is dangerous, because nothing tells you it happened.

A macro that hunts logic errors in your data

The macro below is the one to keep. Paste it into a standard module and run it. If there is no sheet called Error Demo it builds one, filled with the sort of messy column every real export contains, and audits it: how many values are real numbers, how many are numbers Excel is storing as text, how much is blank, and what the honest sum and average are. Later, change the sheet name in DemoSheet and point it at your own data.

Option Explicit

Sub ColumnHealthCheck()
    Dim ws As Worksheet
    Dim cell As Range
    Dim lastRow As Long
    Dim numbers As Long
    Dim textNumbers As Long
    Dim otherText As Long
    Dim blanks As Long
    Dim total As Double
    Dim msg As String

    Set ws = DemoSheet()
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
    If lastRow < 2 Then
        MsgBox "Nothing to check in column C.", vbInformation
        Exit Sub
    End If

    For Each cell In ws.Range("C2:C" & lastRow)
        If Len(cell.Value) = 0 Then
            blanks = blanks + 1
        ElseIf VarType(cell.Value) = vbString Then
            If IsNumeric(cell.Value) Then
                textNumbers = textNumbers + 1
            Else
                otherText = otherText + 1
            End If
        Else
            numbers = numbers + 1
            total = total + cell.Value
        End If
    Next cell

    msg = "Column C on sheet " & ws.Name & ", rows 2 to " & lastRow & vbCrLf & vbCrLf & _
          "Real numbers:    " & numbers & vbCrLf & _
          "Numbers as text: " & textNumbers & vbCrLf & _
          "Other text:      " & otherText & vbCrLf & _
          "Blank cells:     " & blanks & vbCrLf & vbCrLf

    If numbers = 0 Then
        msg = msg & "No numeric values, so there is no average to report."
    Else
        msg = msg & "Sum:     " & Format(total, "#,##0.00") & vbCrLf & _
              "Average: " & Format(total / numbers, "#,##0.00")
    End If

    If textNumbers > 0 Then
        msg = msg & vbCrLf & vbCrLf & "Warning: " & textNumbers & " value(s) look " & _
              "like numbers but are stored as text. SUM and this total both skip them."
    End If

    MsgBox msg, vbInformation, "Column health check"
End Sub

Private Function DemoSheet() As Worksheet
    Dim ws As Worksheet
    Dim found As Worksheet
    Dim values As Variant
    Dim i As Long

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = "Error Demo" Then Set found = ws
    Next ws

    If Not found Is Nothing Then
        Set DemoSheet = found
        Exit Function
    End If

    Set found = ThisWorkbook.Worksheets.Add
    found.Name = "Error Demo"
    found.Range("A1:C1").Value = Array("Date", "Customer", "Amount")
    found.Range("A1:C1").Font.Bold = True

    ' The two entries starting with an apostrophe stay TEXT in the cell -
    ' exactly what a CSV import or a copy from a web page gives you.
    values = Array(1200.5, 940, "'1050.75", 380.25, "", 610, "pending", "'1499.9")

    For i = LBound(values) To UBound(values)
        found.Cells(i + 2, 1).Value = DateSerial(2025, 1, i + 1)
        found.Cells(i + 2, 2).Value = "Customer " & i + 1
        found.Cells(i + 2, 3).Value = values(i)
    Next i

    found.Columns("A:C").AutoFit
    Set DemoSheet = found
End Function

Line by line

• Set ws = DemoSheet() — the helper creates the sheet only if it is missing, so a second run duplicates nothing.
• ws.Cells(ws.Rows.Count, "C").End(xlUp).Row — walks up from the bottom to the last used cell in column C. Never hard-code a last row; a hard-coded 1000 is how reports silently lose row 1001 onward.
• VarType(cell.Value) = vbString — asks what Excel really stored. A cell showing 1050.75 may hold a number or text, and the two behave differently.
• IsNumeric(cell.Value) separates text that looks numeric from real text such as pending.
• If numbers = 0 — the division guard. Division by zero is the commonest run-time error in reporting macros, and one line removes it.
• The & _ ending a line is a continuation: space, underscore, new line. It is the only way to break a long VBA statement.

The trap: SUM and COUNT disagree

This is the hour you get back. When numbers arrive as text — from a CSV, a web page, a system export — Application.Sum ignores them while CountA counts them. Divide one by the other and the average is too low, with no error of any kind. The macro above warns you; the version below is what most people write.

' Looks perfectly reasonable. Quietly wrong.
Dim total As Double
Dim count As Long

total = Application.Sum(ws.Range("C2:C1000"))          ' skips text
count = Application.CountA(ws.Range("C2:C1000"))       ' counts text
MsgBox "Average: " & total / count                     ' too low, no warning

Compile the whole project before you run it

Put Option Explicit on the first line of every module. It forces you to declare every variable, so a typo like totl for total becomes a compile error instead of a permanently empty variable that makes your report wrong. Then choose Debug → Compile VBAProject: it checks the whole project rather than stopping at the first line that happens to run.

How it goes on

Logic errors are found by watching the code run, which is what the next three lessons are for: breakpoints and stepping, the Immediate window with Debug.Print, and the Locals and Watch windows. Lessons five to eight take on run-time errors — On Error GoTo handlers, the narrow cases where On Error Resume Next is legitimate, clean-up that restores your settings, and logging so a failure at 03:00 leaves a trail.