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

Logical Expressions

   <source lang="vb">

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

</source>
   
  

Logical operators in VBA

   <source lang="vb">

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.

</source>
   
  

Truth table for the AND operator

   <source lang="vb">

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

</source>
   
  

Truth table for the NOT operator

   <source lang="vb">

Condition1 NOT Condition1 True False False True

</source>
   
  

Truth table for the OR operator

   <source lang="vb">

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

</source>
   
  

Use Boolean value in an If statement

   <source lang="vb">

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

</source>