Checking a Range of Values
You may find at times that testing a value for a particular data type (such as number or string) is not enough to prevent input errors. Sometimes it is necessary to check for a range of values. For example, you may wish to prompt a user to enter a number from 1 to 100. Maybe you want a person to pick a letter from a to z.
Testing for a range of values involves a little more thought from the programmer. Specifically, your first thought should be to know the range(s) needing to be tested and whether the ranges are numeric or character based. Testing ranges of values with numbers or strings uses the same programming constructs consisting of compound conditions.
Let's take the 1 to 100 example mentioned earlier. As seen here, use the IsNumeric function as part of the overall testing for a range of numbers (1 to 100):
Private Sub cmdCheckRange_Click()
If IsNumeric(txtInput.Value) = True Then
If Val(txtInput.Value) >= 1 And _
Val(txtInput.Value) <= 100 Then
MsgBox "You entered a number between 1 and 100."
Else
MsgBox "Your number is out of range."
End If
Else
MsgBox "Please enter a number from 1 to 100."
End If
End Sub
Testing for a range of letters (characters) is not much different, if you remember that all characters (letters or numbers) can be represented with character codes, also known as ANSI (American National Standards Institute) values. For example, a user to enter a letter in the range of a through m (including both uppercase and lowercase letters within the range). Can still use the IsNumeric function to help me out, but need to perform some additional tests.
Private Sub cmdCheckRange_Click()
If IsNumeric(txtInput.Value) = False Then
If Asc(UCase(txtInput.Value)) >= 65 And _
Asc(UCase(txtInput.Value)) <= 77 Then
MsgBox "You entered a letter between a and m."
Else
MsgBox "Your letter is out of range."
End If
Else
MsgBox "Please enter a letter between a and m."
End If
End Sub
In the preceding code, looking for the IsNumeric function to return a False value, which means the input was not a number. Next use the Asc function, which converts a character to its corresponding ANSI value.
Using compound conditions, specifically look for an ANSI range between 65 and 77, the numbers that represent the capital letters A through M. You may also notice that used the function UCase in association with the Asc function. The UCase function converts lowercase letters to uppercase letters. If didn't convert the characters to uppercase, It would have needed to check for the lowercase letters as well (ANSI numbers 97 to 109).
