Common errors

Here are common errors that show up in VBA code.

Lesson contents

Division by zero

Suppose this code...

  • sAverageHeight = sTotalHeight / sNumGoats

... gives you:

Division by zero

If there's no data, sNumGoats is zero.

Use the guard pattern to prevent this.

Type mismatch

Type mismatch

Usually happens when you try to put a string into a number, like this:

  • Dim sThing as Single
  • sThing = "a thing"

Singles can only contain numbers.

More realistic:

  • Dim sSales as Single
  • sSales = Cells(7, 7)

If the cell has non-numeric data, like "Terrible!", you'll see the message.

The error can come from an If statement.

  • Dim sThing As Single
  • ...
  • If sThing = "Goatland" Then

Crashy crashy.

The solution is to validate the data. For example, suppose you had this:

String in sales cell

Code that would not crash:

  • Dim tInput As String
  • Dim sSales As Single
  • ' Input sales into a temporary string variable.
  • ' String variables can take anything.
  • tInput = Cells(2, 2)
  • ' Check whether the input is a number.
  • If Not IsNumeric(tInput) Then
  •     MsgBox "Sorry, sales must be a number."
  •     End
  • End If
  • ' If the program gets to here, the data is a number.
  • ' Put the data into a single.
  • sSales = tInput

You can try it.

Variable not defined

Variable not defined error message

Usually means you've mistyped a variable name.