Simplify Conditions in VBA
This page brings together several examples of simplifications that you can apply when writing your VBA code involving conditions.
If you think the following line is not valid in VBA, these examples should interest you!
result = text1 = text2
Simple Condition
This first condition here returns Yes if the value is True:
Sub example()
value = True
If value = True Then
MsgBox "Yes"
End If
End Sub
In a condition, it is unnecessary to specify = True because the condition already seeks to determine if the condition is true:
Sub example()
value = True
If value Then
MsgBox "Yes"
End If
End Sub
If the If condition is on a single line, you don't need to add End If at the end:
Sub example()
value = True
If value Then MsgBox "Yes"
End Sub
Assigning True or False to a Variable
This new condition is used here to assign the value True to the variable result if the two texts are identical, or False if they are not:
Sub example()
text1 = "XLP"
text2 = "XLP"
If text1 = text2 Then
result = True
Else
result = False
End If
End Sub
This condition can be shortened using the IIf function, which returns one value or another based on a condition:
Sub example()
text1 = "XLP"
text2 = "XLP"
result = IIf(text1 = text2, True, False)
End Sub
But when the IIf function returns the values True and False, it becomes redundant because the test text1 = text2 already returns True or False:
Sub example()
text1 = "XLP"
text2 = "XLP"
result = (text1 = text2)
End Sub
This line can be further simplified by removing the unnecessary parentheses:
Sub example()
text1 = "XLP"
text2 = "XLP"
result = text1 = text2
End Sub
Another example with a test that needs to determine if the values are different:
Sub example()
text1 = "XLP"
text2 = "XLP"
differences = text1 <> text2
End Sub
True, False, 1, 0
In a condition, the test = 1 is equivalent to = True, but other values can also evaluate to True.
The following conditions are all true:
Sub example()
value = True
If value Then MsgBox "TRUE"
value = 1
If value Then MsgBox "TRUE"
value = 5
If value Then MsgBox "TRUE"
value = -5
If value Then MsgBox "TRUE"
value = "10"
If value Then MsgBox "TRUE"
End Sub
Conversely, the values 0, "0", and "" evaluate positively to the = False test.