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