VBA Course: Conditions (Part 2)
IsNumeric Function
The IsNumeric function (seen on the previous page) returns True if the value is numeric and False if it's not:
If IsNumeric(Range("A1")) = True Then
If IsNumeric(Range("A1")) Then
These two lines are identical (it's not necessary to enter = True since we are checking if the expression is true anyway).
In case we want to check if the value is not numeric, we also have two possibilities:
If IsNumeric(Range("A1")) = False Then 'If the value is not numeric
If Not IsNumeric(Range("A1")) Then 'If the value is not numeric
There are many other functions that you can use in your conditions (or more generally in your VBA code).
Date Functions
There are many date and time functions that can be used in conditions, here are some examples.
The IsDate function returns True if the value is a date or False if it's not:
If IsDate(Range("A1")) Then 'If the value is a date
The Day function allows to extract the day from a date:
If Day(Range("A1")) = 1 Then 'If it is the first day of the month
The Year function allows to extract the year from a date:
If Year(Range("A1")) = 2023 Then 'If it is a date from the year 2023
The Weekday function returns the number of the day of the week:
If Weekday(Range("A1"), 2) >= 6 Then 'If it is a Saturday or Sunday
The Date function returns the current date:
If Range("A1") < Date Then 'If the date has passed
IsEmpty Function
The IsEmpty function returns False if the variable has been initialized or True if it hasn't:
If IsEmpty(myVariable) Then 'If the variable hasn't been initialized
In this example, the condition is true because no type or value has been assigned to myVariable:
Sub example()
Dim myVariable
If IsEmpty(myVariable) Then
MsgBox "My variable hasn't been initialized!"
Else
MsgBox "My variable contains: " & myVariable
End If
End Sub
Condition based on the comparison of 2 strings
Until now, we've only seen this:
myVariable = "Example 12345"
If myVariable = "Example 12345" Then '=> True
In this case, the two strings are identical, so the expression is true.
Now, to check if the variable contains the value 12345 regardless of other characters, we'll use the Like operator with * before and after the value to search for.
The * character can replace: no character, one character, or multiple characters:
myVariable = "Example 12345"
If myVariable Like "*12345*" Then '=> True
The # character can replace a numeric character from 0 to 9:
myVariable = "Example 12345"
If myVariable Like "Example 12###" Then '=> True
The ? character can replace any character:
myVariable = "Example 12345"
If myVariable Like "?xample?1234?" Then '=> True
We can also replace a character based on a range of characters or specific characters:
- [abc]: replaces one of the following characters: a b c
- [a-g]: replaces one of the following characters: a b c d e f g
- [369]: replaces one of the following characters: 3 6 9
- [2-5]: replaces one of the following characters: 2 3 4 5
- [?*#]: replaces one of the following characters: ? * #
myVariable = "Example 12345"
If myVariable Like "[TEST]xample 1234[4-7]" Then '=> True
To replace a character that is not included in the values between brackets, an ! must be added after [:
myVariable = "Example 12345"
If myVariable Like "[!WRONG]xample 1234[!6-9]" Then '=> True