VBA Course: Conditions (continued)
A conditional based on type
IsNumeric (this is the function we used on the last page) returns TRUE if the value is numerical and FALSE if this is not the case:
If IsNumeric(Range("A1")) = True Then 'IF THE VALUE IS NUMERICAL ...The following code has the same effect as the version above (we don't have to include the = True because it is the default to ask whether a condition is true):
If IsNumeric(Range("A1")) Then 'IF THE VALUE IS NUMERICAL ...If what we wanted to do was to test whether the value was NOT numerical, we could do this in either of the following two ways:
If IsNumeric(Range("A1")) = False Then 'IF THE VALUE IS NOT NUMERICAL...If Not IsNumeric(Range("A1")) Then 'IF THE VALUE IS NOT NUMERICAL ...Here are some examples of other functions similar to IsNumeric:
If IsDate(Range("A1")) Then 'IF THE VALUE IS A DATE ...If IsEmpty(Range("A1")) Then 'IF EMPTY...If var_object Is Nothing Then 'IF OBJECT IS NOT SET ...A conditional based on a variable's type
To execute commands based on the type of a variable (Variant), we will need to use the VarType function.
The list of types will appear once we have entered the = operator:
If VarType(my_variable) = vbInteger Then 'IF my_variable if is of type Integer ...Values of constants:
| Constant | Value |
|---|---|
| vbEmpty | 0 |
| vbNull | 1 |
| vbInteger | 2 |
| vbLong | 3 |
| vbSingle | 4 |
| vbDouble | 5 |
| vbCurrency | 6 |
| vbDate | 7 |
| vbString | 8 |
| vbObject | 9 |
| vbError | 10 |
If VarType(my_variable) = vbInteger Then 'IF my_variable is of type Integer ...
'Identical to:
If VarType(my_variable) = 2 Then 'IF my_variable is of type Integer ...A conditional based on comparing two character strings
Up to this point, we have seen this:
my_variable = "Example 12345"
If my_variable = "Example 12345" Then ' => TRUEIn this case, the two strings are identical, it's nothing out of the ordinary ...
But if we want to test whether the variable contains the value "12345" without taking any of the other characters into account, we would have to use the Like command and a * operator before and after the search value.
The * operator stands in for: any character or multiple characters:
my_variable = "Example 12345"
If my_variable Like "*12345*" Then ' => TRUEThe # operator can stand in for any numerical character from 0 to 9:
my_variable = "Example 12345"
If my_variable Like "Example 12###" Then ' => TRUEThe ? operator can stand in for any single character:
my_variable = "Example 12345"
If my_variable Like "?xample?1234?" Then ' => TRUEWe can also use a particular character or range of characters in the same way:
- [abc] stands in for any one of the following characters: a b c
- [a-g] stands in for any one of the following characters: a b c d e f g
- [369] stands in for any one of the following characters : 3 6 9
- [2-5] stands in for any one of the following characters: 2 3 4 5
- [?*#] stands in for any one of the following characters: ? * #
my_variable = "Example 12345"
If my_variable Like "[DEF]xample 1234[4-7]" Then ' => TRUEAn ! added after the [ will stand in for any character not included within the bracketed expression:
my_variable = "Example 12345"
If my_variable Like "[!GHIJ]xample 1234[!6-9]" Then ' => TRUE