Input validation

Keywords: 
If
Condition
Situation: 

You get input from the user. S/he might have made a mistake.

Actions: 

Add an If statement to check the data before processing it.

Explanation: 

Example:

If Not IsNumeric(Cells(1, 2)) Then
    MsgBox "Sorry, amount must be a number."
    End
EndIf
amount = Cells(1, 2)

What to check?

Sometimes you're checking cells, sometimes variables. Depends on the program.

Be sure to check user data before any code that would crash the program. For example, instead of the code above, you could have tried:

amount = Cells(1, 2)
If Not IsNumeric(amount) Then
    MsgBox "Sorry, amount must be a number."
    End
EndIf

That wouldn't work. If the user typed "dog" or some such into the cell, the code would crash at the first line (amount = Cells(1, 2)), before the If statement is reached.

InputBoxes can be tricky. They always return strings. If the user leaves the input field empty, InputBox will return an empty string. So this won't work, for the same reason we just talked about:

amount = InputBox( "What is the amount?" )
If Not IsNumeric(amount) Then
    MsgBox "Sorry, amount must be a number."
    End
EndIf

A good solution is to use a string variable for all input. Put the user's input into the variable, and then check it. If the data is OK, put the string variable into the real variable. For example:

Dim userInput as String
...
userInput = InputBox( "What is the amount?" )
If Not IsNumeric(userInput) Then
    MsgBox "Sorry, amount must be a number."
    End
EndIf
amount = userInput

It's clear what's going on with this code.

What to check for?

What checks should you make? Depends on the situation. Maybe for not numeric, negative, an empty cell, something other than Yes or No… depends on the situation.

Often you'll need to make several checks on the same data. Maybe a cell must have a positive number. That means it can't be empty, must be numeric, and can't be zero or less. You can do these checks with one If statement (using And and Or to link checks together), or multiple If statements. For example:

userInput = InputBox( "What is the amount?" )
If Not IsNumeric(userInput) Then
    MsgBox "Sorry, amount must be a number."
    End
ElseIf userInput <= 0 Then
    MsgBox "Sorry, amount must be greater than zero."
    End
EndIf

In this code, the numeric test should come before the negative test. We don't want Excel to try to do a numeric comparison (<= 0) if the data is not numeric. So test whether the data is numberic, first.

Also, if the data isn't numeric, we don't want to test userInput <= 0. Excel will try to convert userInput to a number so it can run the test, and we already know that will fail. So use an ElseIf. That way, the test userInput <= 0 will only be done if we know that userInput is numeric.

What to do if a check fails?

Again, it depends on the situation. If the input is coming from a cell, it makes sense to tell the user about the problem, and end the program. You can show an error message with MsgBox, as above, or put an error message in the worksheet:

If Not IsNumeric(userInput) Then Cells(1, 3) = "Sorry, amount must be a number." Cells(1, 3).Font.Color = vbRed Cells(1, 3).Font.Bold = True End EndIf

You can simplify that a little using With:

If Not IsNumeric(userInput) Then
    With Cells(1, 3)
        .Value = "Sorry, amount must be a positive number."
        .Font.Color = vbRed
        .Font.Bold = True
    EndWith
    End
EndIf

If you add error messages to the worksheet, be sure to clear them at the start of the program:

...
Dim amount as Single
'Initialize
'Clear error messages
Cells(1, 3) = ""
...

Otherwise, when the user corrects the error and reruns the program, the error message will still be there.

If input is coming from a file with lots of data, maybe you want to create a separate file of error messages, one message for each error. Again, it all depends on the situation.

Referenced in: 
Numeric expressions