Here are common errors that show up in VBA code.
Lesson contents
Division by zero
Suppose this code...
- sAverageHeight = sTotalHeight / sNumGoats
... gives you:
If there's no data, sNumGoats is zero.
Use the guard pattern to prevent this.
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:
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
Usually means you've mistyped a variable name.