VBA/Excel/Access/Word/Language Basics/Logical Operators

Logical Expressions

 
Sub Main()
    Dim num1 As Integer
    Dim num2 As Single
    Dim str1 As String
    Dim str2 As String
    Dim obj1 As Object
    Dim obj2 As Object
    Dim result As Boolean
    num1 = 10
    num2 = 10
    str1 = "10"
    str2 = "ABC"
    Set obj1 = Range("A1")
    Set obj2 = Range("A1")
    
    result = (num1 = num2)      "result holds True
    result = (num1 < num2)      "result holds False
    result = (num1 > num2)      "result holds False
    result = (num1 >= num2)     "result holds True
    result = (num1 <= num2)     "result holds True
    result = (num1 <> num2)     "result holds False
    
    result = (num1 = str1)      "result holds True
"    result = (num1 = str2)      "Type mismatch error
    
    result = (str1 = str2)       "result holds False
    result = (str1 > str2)       "result holds False
    result = (str1 < str2)       "result holds True
    result = ("a" < "A")         "result holds False
    result = (str1 >= str2)     "result holds False
    result = (str1 <= str2)     "result holds True
    result = (str1 <> str2)     "result holds True
    
    
    result = (obj1 Is obj2)      "result holds False
             Set obj1 = obj2
    result = (obj1 Is obj2)      "result holds True
End Sub

Logical operators in VBA

 
Operator       Description 
AND            All conditions must be true before an action can be taken.
OR             At least one of the conditions must be true before an action can be taken.
NOT            If a condition is true, NOT makes it false. If a condition is false, NOT makes it true.

Truth table for the AND operator

 
Condition1     Condition2     Condition1 AND Condition2
True           True           True
True           False          False
False          True           False
False          False          False

Truth table for the NOT operator

 
Condition1     NOT Condition1
True           False
False          True

Truth table for the OR operator

 
Condition1     Condition2     Condition1 OR Condition2
True           True           True
True           False          True
False          True           True
False          False          False

Use Boolean value in an If statement

 
Sub Use()
    Dim userName As String
    Dim userBirthday As Date
    Dim nameOk As Boolean
    nameOk = True
    Do
        userName = InputBox("What is your first and last name?", "Name")
        If (userName <> "") Then nameOk = ValidateName(userName)
    Loop While (nameOk = False) Or (userName <> "")
End Sub
Function ValidateName(userName As String) As Boolean
    Dim strLength As Integer
    Dim I As Integer
    Dim numSpaces As Integer
    Dim tempString As String
    Dim msb As Integer
    userName = Trim(userName)
    strLength = Len(userName)
    For I = 1 To strLength
        If Left(userName, 1) = " " Then
            numSpaces = numSpaces + 1
        End If
        userName = Right(userName, Len(userName) - 1)
    Next I
    If (numSpaces <> 1) Then
        ValidateName = False
        msb = MsgBox("Please enter just two names separated by one space", vbCritical, "Error")
    Else
        ValidateName = True
  End If
End Function