Validation (numeric, basic)

Tags
Validation
Summary

Test that data is numeric. Add range checks and other tests, as needed.

Situation

You have some data that's supposed to be numeric. Could be from a cell, a file, a dialog box, wherevs. You want to check the data.

Needs

A data value.

Provides

Number that's valid, or an error message.

Action

An example:

  1. 'A temporary variable to hold unchecked user input.
  2. Dim tUncheckedUserInput as String
  3. Dim sMealCost As Single
  4. ...
  5. tUncheckedUserInput = Cell(x,y), InputBox(), Input#1..., wherevs.
  6. If Not IsNumeric(tUncheckedUserInput) Then
  7.     MsgBox "Sorry, meal cost must be a number."
  8.     End
  9. End If
  10. sMealCost = tUncheckedUserInput
  11. ' Range check.
  12. If sMealCost < 0 Then
  13.     MsgBox "Sorry, meal cost cannot be negative."
  14.     End
  15. End If

You need two variables. The first is a string, to hold the raw data you want to test. Strings can hold anything you can type, so there are no data mismatch errors, if the raw data is non-numeric.

You test whether the raw data is numeric (line 6). If it is not, show an error message (line 7), and stop the program (line 8).

If the raw data is numeric, you can safely move into a Single variable (line 10). That's the variable you'll use for calculations later.

If you need other tests, add them. For example, in this case there's a range check, to make sure the value is more than zero (lines 12-15).