【VBA】And/Or/NotのIF文とIF以外での使い方を少し詳しく解説

' a が b より大きい、かつ、c が 5 、かつ、b が 2 かどうか判定 
if a > b And c = 5 And b = 2 Then
  ' 条件を満たすときの処理
Else
  ' 条件を満たさないときの処理
End if

Dim a As Integer, b As Integer, c As Integer
Dim result As Variant

a = 5: b = 2: c = 5
result = a > b And c = 5 And b = 2
Debug.Print result
>> True

Debug.Print 7 And 9
>> 1
Debug.Print 7 And 9 And 4
>> 0
Debug.Print 7 And "9"
>> 1

a = 234: b = 123: c = 321

result = a And b And a > b And c = 321 And b = 123 And c + 100
Debug.Print result
>> 32 

If a And b And a > b And c = 321 And b = 123 And c + 100 Then ' 計算結果32 ⇒ True
 Debug.Print "true"
End If
>> "true"

Sub test2()
    Dim a As Integer, b As Integer, c As Integer
    Dim result As Variant
    a = 234: b = 123: c = 321

    If Not (a And b And a > b And c = 321) Or (b = 123 And c) Then
     ' 条件を満たすときの処理
    End If

    If Not func(200) Then
     ' 条件を満たすときの処理
    End If
End Sub

Function func(val As Integer) As Boolean
    If val > 100 Then
        func = True
    Else
        func = False
    End If
End Function

If Not a > b Then ' 単一の比較演算にNotを使用
End If

If a <= b Then ' 演算子を変更
End If

a = 234: b = 123: c = 321

result = a And b Or a > b And c = 321 Or Not (b = 123 Or c)
Debug.Print result
>> -1

Sub test()

    Dim cell1 As Range
    Set cell1 = Cells(1, 1)
    
    If cell1.value >= 100 _
        And cell1.value <= 200 Then
        MsgBox "範囲内です"
    Else
        MsgBox "範囲内ではありません"
    End If
    
End Sub

Sub test()

    Dim cell1 As Range
    Set cell1 = Cells(1, 1)
    
    If Not (cell1.value >= 100 _
        And cell1.value <= 200) Then
        MsgBox "範囲内ではありません"
    Else
        MsgBox "範囲内です"
    End If
    
End Sub

    If cell1.value < 100 _
        Or cell1.value > 200 Then

Sub test()
    Dim cell1 As Range
    Set cell1 = Cells(1, 1)
    
    If cell1.Value = Cells(2, 1).Value _
        And cell1.Value = Cells(3, 1).Value Then
        MsgBox "同じです"
    Else
        MsgBox "同じではありません"
    End If
    
End Sub

Sub test()

    Dim value As Integer
    value = 100
    
    If Cells(1, 1).value = value _
        Or Cells(2, 1).value = value _
        Or Cells(3, 1).value = value Then
        
        MsgBox "同じ値があります"
    Else
        MsgBox "同じ値がありません"
    End If
    
End Sub

Sub test()
    Dim cell1 As Range, cell2 As Range, cell3 As Range
    Set cell1 = Cells(1, 1)
    Set cell2 = Cells(2, 1)
    Set cell3 = Cells(3, 1)
    
    Dim value As Integer
    value = 100

    If cell1.value <> cell2.value _
        And cell1.value <> cell3.value _
        And cell2.value <> cell3.value _
        And Not ( _
                cell1.value = value _
                Or cell2.value = value _
                Or cell3.value = value _
            ) Then

        MsgBox "条件に一致します"
    Else
        MsgBox "条件に一致しません"
    End If
    
End Sub