What is Null in Excel VBA

To understand keyword or value Null we must understand what is variables and how to declare variables. Below examples how we declare variables with Dim statement.

Dim StrName  - Declare as open data type default as Variant

Dim StrName as String -  Declare as String data type (0 to approximately 2 billion)

Dim IntID as integer -  Declare as Integer data type (-32,768 to 32,767)

When dealing with no valid data,unknown or empty data we use Null as value and this Null value only can be used when variables declare as variant (Same range as for variable-length String).

For example:

Dim myVar  OR Dim myVar as Variant, then only we can assigned myVar = Null.
If we declare other than these 2 it will trigger error Run-time error '94': Invalid use of Null.

VBA Vode:

Option Explicit
Sub StatementNull()

    Dim myVar1
    Dim myVar2 As Variant
    
    myVar1 = Null
    myVar2 = Null
    
    Debug.Print myVar1
    Debug.Print myVar2

End Sub

Microsoft Reference-Null
Other Reference - Null

Practice makes perfect. Thank You.

macro enabled excel
excel macro
vba coding
vba code

Summary Operator Is, Like, Mod and Not in Excel VBA

Even though Operator Is, Like, Mod and Not totally different in comparison but sometime difficult to remember which one need to use. Here comparison table for reference.

No.	Operator	Logical
1	Is	Compare two object reference variables.
2	Like	Compare two strings.
3	Mod	Divide two numbers and return only the remainder.
4	Not	Perform logical negation on an expression.

Operator		Commonly Use for
Is		ObjectA Is ObjectB = TRUE/FALSE
Is		MyRange Is Nothing = TRUE/FALSE
Is		Intersect(MyRange,Selection) Is Nothing = TRUE/FALSE
Like		String Like String Pattern(*,#,?,[!]) = TRUE/FALSE
Mod		A=7,B=2, A Mod B = 1 (Balance for 2*3=6)
Not		A=7,B=2. Not (A > B) = FALSE, Not (A < B) = TRUE

VBA Vode:

Option Explicit
Sub OperatorIsLikeModNot()
    
    Dim MySheet As Worksheet
    Dim iNumA As Integer, iNumB As Integer
    Dim StrUrlA As String, StrUrlB As String
    
    Dim MyRange As Range, MySelect As Range
    
    Set MySheet = ActiveSheet
    Set MyRange = MySheet.Range("A1:D20")
    Set MySelect = Selection
    StrUrlA = "https://mrvba.blogspot.com/"
    StrUrlB = "C:\Users\UserName\Desktop\AlQuran MP3"
    iNumA = 10: iNumB = 3
    
    'Combination Is and Not Operator
    If Not MySheet Is Nothing Then
        Debug.Print MySheet.Name
        'Combination Is Intersect and Not Operator
        If Not Intersect(MySelect, MyRange) Is Nothing Then
            Debug.Print "Your Selected cell is in range A1 to D20."
        Else
            Debug.Print "Your Selected cell is Not in range."
        End If
        'Operator Like
        If StrUrlA Like "https://*" Then
            Debug.Print "Yes! This is URL:" & StrUrlA
        Else
            Debug.Print "This is not Url:" & StrUrlB
        End If
        If StrUrlB Like "https://*" Then
            Debug.Print "Yes! This is URL:" & StrUrlA
        Else
            Debug.Print "This is not Url:" & StrUrlB
        End If
        'Operator Mode and And operator
        If IsNumeric(iNumA) And IsNumeric(iNumB) Then
            Debug.Print iNumA Mod iNumB
        End If
        'Operator Not
        If Not (iNumA > iNumB) Then
            Debug.Print "No: " & iNumA & " is Less than " & iNumB
        ElseIf Not (iNumA < iNumB) Then
            Debug.Print "Yes: " & iNumA & " is more than " & iNumB
        End If
    End If

End Sub

Note:
Most commonly use Operator among these is Not and Is, sometime only use like and seldom use Mod.

Microsoft Reference-Is-operator
Microsoft Reference-Like-operator
Microsoft Reference-Mod-operator
Microsoft Reference-Mod-operator example
Microsoft Reference-Not-operator

Practice makes perfect. Thank You.

excel training beginners
coding in vba
excel training online
visual basic for applications

The different between Operator Or, Xor, And, Eqv and Imp in Excel VBA

Sometime very hard to choose which Operator need use when we have 2 expressions or statements to compare because the result almost the same. Here we have table for reference.

No.			Operator			Logical
1			Or			Disjunction
2			Xor			Exclusion
3			And			Conjunction
4			Eqv			Equivalence
5			Imp			Implication

			Operator
No.	If expression1 is	And expression2 is	Or	Xor	And	Eqv	Imp
1	True	True	True	False	True	True	True
2	True	False	True	True	False	False	False
3	True	Null	True	Null	Null	Null	Null
4	False	True	True	True	False	False	True
5	False	False	False	False	False	True	True
6	False	Null	Null	Null	False	Null	True
7	Null	True	True	Null	Null	Null	True
8	Null	False	Null	Null	False	Null	Null
9	Null	Null	Null	Null	Null	Null	Null

For more example refer below table and code.

VBA Vode:

Option Explicit
Sub ComparisonOperator_All()

    Dim i As Integer
    Dim myRng As Range, Cell As Range
    Dim iNum1, INum2, INum3, INum4
    With ActiveSheet
        i = 2
        Do
            If .Range("B" & i) = "" Then
                iNum1 = Null
            Else
                iNum1 = .Range("B" & i)
            End If
            If .Range("C" & i) = "" Then
                INum2 = Null
            Else
                INum2 = .Range("C" & i)
            End If
            If .Range("E" & i) = "" Then
                INum3 = Null
            Else
                INum3 = .Range("E" & i)
            End If
            If .Range("F" & i) = "" Then
                INum4 = Null
            Else
                INum4 = .Range("F" & i)
            End If
            
            .Range("D" & i) = iNum1 > INum2
            .Range("G" & i) = INum3 > INum4
            
            'Operator
            .Range("H" & i) = iNum1 > INum2 Or INum3 > INum4
            .Range("I" & i) = iNum1 > INum2 Xor INum3 > INum4
            .Range("J" & i) = iNum1 > INum2 And INum3 > INum4
            .Range("K" & i) = iNum1 > INum2 Eqv INum3 > INum4
            .Range("L" & i) = iNum1 > INum2 Imp INum3 > INum4
            
            i = i + 1
        Loop While .Range("A" & i) <> ""
    
        'To put background color base on result
        Set myRng = .Range("H2:L10")
        For Each Cell In myRng
            If Cell.Text = "TRUE" Then
                Cell.Interior.ColorIndex = 4 'Green
            ElseIf Cell.Text = "FALSE" Then
                Cell.Interior.ColorIndex = 6 'Yellow
            Else
                Cell.Interior.ColorIndex = 3 'Red
            End If
        Next
    End With
    
End Sub

Note:
The above code can be simplify with array in advanced topic. These operator commonly use with If...Then..Else function. Operator Or & And are the most commonly use.

Microsoft Reference-Or-operator
Microsoft Reference-Xor-operator
Microsoft Reference-And-operator
Microsoft Reference-Eqv-operator
Microsoft Reference-Imp-operator

Practice makes perfect. Thank You.

excelmacros
macro excel
excel programming
excel vba

Imp Operator in Excel VBA

Operator Imp is Used to perform a logical implication on two expressions or statements. For example we have 2 sets of number to compare 20<30 (True), another 30<40 (True). Therefore if we used 20<30 Imp 30<40 then the result is True and both False result is True. If both Null then result is Null. The details combination of Imp logical test as below.

No.

If expression1 is

And expression2 is

The result is

1

True

True

True

2

True

False

False

3

True

Null

Null

4

False

True

True

5

False

False

True

6

False

Null

True

7

Null

True

True

8

Null

False

Null

9

Null

Null

Null

For more example refer below table and code.

VBA Vode:

Option Explicit
Sub ComparisonOperator_Imp()

    Dim i As Integer
    Dim myRng As Range, Cell As Range
    Dim iNum1, INum2, INum3, INum4
    With ActiveSheet
        i = 2
        Do
            If .Range("B" & i) = "" Then
                iNum1 = Null
            Else
                iNum1 = .Range("B" & i)
            End If
            If .Range("C" & i) = "" Then
                INum2 = Null
            Else
                INum2 = .Range("C" & i)
            End If
            If .Range("E" & i) = "" Then
                INum3 = Null
            Else
                INum3 = .Range("E" & i)
            End If
            If .Range("F" & i) = "" Then
                INum4 = Null
            Else
                INum4 = .Range("F" & i)
            End If
            
            .Range("D" & i) = iNum1 > INum2
            .Range("G" & i) = INum3 > INum4
            
            'Or operator
            .Range("H" & i) = iNum1 > INum2 Imp INum3 > INum4
            
            i = i + 1
        Loop While .Range("A" & i) <> ""
    
        'To put background color base on result
        Set myRng = .Range("H2:H10")
        For Each Cell In myRng
            If Cell.Text = "TRUE" Then
                Cell.Interior.ColorIndex = 4 'Green
            ElseIf Cell.Text = "FALSE" Then
                Cell.Interior.ColorIndex = 6 'Yellow
            Else
                Cell.Interior.ColorIndex = 3 'Red
            End If
        Next
    End With
    
End Sub

Note:
The above code can be simplify with array in advanced topic. This Imp operator commonly use with If...Then..Else function.

Microsoft Reference-Imp-operator

Practice makes perfect. Thank You.

excelmacros
macro excel
excel programming
excel vba

Eqv Operator in Excel VBA

Operator Eqv is Used to perform a logical equivalence on two expressions or statements. For example we have 2 sets of number to compare 20<30 (True), another 30<40 (True). Therefore if we used 20<30 Eqv 30<40 then the result is True and both False result is True. If both or either one is Null then result is Null. The details combination of Eqv logical test as below.

No.

If expression1 is

And expression2 is

The result is

1

True

True

True

2

True

False

False

3

False

True

False

4

False

False

True

5

Null

True

Null

6

False

Null

Null

7

Null

Null

Null

For more example refer below table and code.

VBA Vode:

Option Explicit
Sub ComparisonOperator_Eqv()

    Dim i As Integer
    Dim myRng As Range, Cell As Range
    Dim iNum1, INum2, INum3, INum4
    With ActiveSheet
        i = 2
        Do
            If .Range("B" & i) = "" Then
                iNum1 = Null
            Else
                iNum1 = .Range("B" & i)
            End If
            If .Range("C" & i) = "" Then
                INum2 = Null
            Else
                INum2 = .Range("C" & i)
            End If
            If .Range("E" & i) = "" Then
                INum3 = Null
            Else
                INum3 = .Range("E" & i)
            End If
            If .Range("F" & i) = "" Then
                INum4 = Null
            Else
                INum4 = .Range("F" & i)
            End If
            
            .Range("D" & i) = iNum1 > INum2
            .Range("G" & i) = INum3 > INum4
            
            'Or operator
            .Range("H" & i) = iNum1 > INum2 Eqv INum3 > INum4
            
            i = i + 1
        Loop While .Range("A" & i) <> ""
    
        'To put background color base on result
        Set myRng = .Range("H2:H8")
        For Each Cell In myRng
            If Cell.Text = "TRUE" Then
                Cell.Interior.ColorIndex = 4 'Green
            ElseIf Cell.Text = "FALSE" Then
                Cell.Interior.ColorIndex = 6 'Yellow
            Else
                Cell.Interior.ColorIndex = 3 'Red
            End If
        Next
    End With
    
End Sub

Note:
The above code can be simplify with array in advanced topic. This Eqv operator commonly use with If...Then..Else function.

Microsoft Reference-Eqv-operator

Practice makes perfect. Thank You.

macro enabled excel
excel macro
vba coding
vba code