Conditional Operators and Wildcards
Relationship Operators|Logical Operators|Wild Cards
VBA conditional operators are used to test whether certain conditions are met and, based on the result, actions will either be taken or not. These operators are an integral component of Control Flow Statements, which are discussed in the next two sections. Relationship operators are used to compare two variables, while logical operators compare two objects.
Wildcards are used along with Conditional Statements as part of pattern recognition tests to specify that any character or range of characters should be matched on.
Relationship Operators
| Operator | Definition |
|---|---|
| = | Equals |
| < | Less than |
| ≤ | Less than or equal to |
| > | Greater than |
| ≥ | Greater than or equal to |
| <> | Not equal to |
Relationship Operator
Sub relationshipOperatorExample()
Dim X, Y as integer
X = 2
Y = 2
If X = Y Then
MsgBox "Match found"
Else
MsgBox "Not equal"
End If
End Sub
In this example, "Match found" is returned. If the value of X is changed and Y is left unchanged, then "Not equal" will be returned.
Logical Operators
| Operator | Definition |
|---|---|
| Not | Negates the logical sense of the condition being tested |
| And | Test whether two conditions are both true |
| Or | Test whether one (or both) of two conditions are true |
| Xor | Test whether exactly one of two conditions are true |
| Is | Test if two objects are the same |
| Like | Compares a string for a match with a subset of the pattern in another string |
Logical operator
Sub logicalOperatorExample()
Dim ws As Worksheet
' Set ws = ActiveSheet
If ws Is Nothing Then
MsgBox "Not Assigned"
Else
MsgBox "Assigned"
MsgBox ws.Name
End If
End Sub
In this example, the worksheet object ws is not set, as the Set statement has been commented out using a single quote at the beginning of the statement. When the subroutine is run, "Not assigned" is returned. If we remove the comment indicator (single quote) and re‑run, "Assigned" is returned along with the name of the active worksheet.
The VBA keyword Nothing is used with object variables to represent the NULL value.
Wild Cards
VBA wild cards are used for pattern matching when comparing strings. A wildcard is a symbol used to replace one or more characters in a string. For example, He* will find and match on Hear, Her, Hello, Hey, Heaven. He? Will only match on Her and Hey, since ? replaces only one character.
| Wild Card | Matches on |
|---|---|
| ? | Any single character |
| * | Zero or more characters |
| # | Any single digit (0-9) |
| [ charlist ] | Any single character in charlist |
| [! charlist ] | Any single character not in charlist |
Consider the data in the above worksheet where is listed the first and last names of seven individuals, a unique ID for each, and their monthly income. This spreadsheet will be used below to show examples of using conditional operators and wildcards.
Use Like and * to find names beginning with a specified letter
In the first example below, VBA will find everyone whose last name begins with an "R" using the Like operator and a Do Loop.
Sub wildcardExample_1()
Dim k As Integer
k = 1
Do
k = k + 1
If Cells(k, 1) = "" Then Exit Do
If Range("B" & k).Value Like "R*" Then
Range("B" & k).Font.Color = vbRed
End If
Loop
End Sub
A counter k is initialized to 1 before the loop begins. At the beginning of each iteration, k is incremented by 1 and is used to indicate the row in the spreadsheet, so the first iteration inside the loop will inspect row 2. With each loop, a check is made to determine when the end of the data has been reached, and when it has an Exit Loop command ends the iterations, thus preventing an infinite loop.
Using the Like operator, we compare the first letter of the last name designated by "R*" and with each match, we change the font color of that name to red.
Use Like and ? to find names beginning with specified letters and of fixed length
Sub wildcardExample_2()
Dim k As Integer
k = 1
Do
k = k + 1
If Cells(k, 1) = "" Then Exit Do
If Range("C" & k).Value Like "Mar?" Then
Range("C" & k).Interior.Color = vbCyan
End If
Loop
End Sub
Here VBA finds all first names that start with Mar and are followed by only one character, then colors their cells in cyan.
Use Like and * to find names with a beginning and ending pattern
Sub wildcardExample_3()
Dim k As Integer
For j = 2 To 8
If Range("C" & k).Value Like "L*nda" Then
Range("C" & k).Interior.Color = vbYellow
End If
Next j
End Sub
In this example, VBA finds all first names that start with L and end with nda, and colors their cells in yellow. Any number of characters at the beginning and end can be used.
Use Like with ? And [*] to find names with a range of letters
Sub wildcardExample_4()
Dim j As Integer
For j = 2 To 8
If Range("B" & k).Value Like "?[i-k]*" Then
Range("A" & k).Interior.Color = vbGreen
End If
Next j
End Sub
Here VBA finds all last names whose second character is i, j or k and colors their ID cell in green. Using a For Loop requires specifying the values of the counter j to step through, but requires no futher statements inside the loop to check on the end of records.
Use Like and [*] to find patterns at the beginning of an amount or a string, allowing for upper and lower case
Sub wildcardExample_5()
Dim j As Integer
For j = 2 To 8
If Range("D" & j).Value Like "[0‑3]*" Then
Range("A" & j & ":D" & j).Interior.Color = vbMagenta
End If
If Range("B" & j).Value Like "[J‑K,j‑k]*" Then
Range("A" & j).Interior.Color = vbGreen
End If
Next j
End Sub
In this example, VBA first finds all income less tahn $4,000 and colors the row of data in magenta. Next, it finds all lastnames that start with J or K and allows for lower case matching as well. The IDs of the matches are colored in green. Note that VBA first colored Cells(8,1) as magenta, but on the second check changed the color to green.
