VBA ISNULL

What is the VBA ISNULL Function?

The VBA IsNull function is a built-in function that returns a Boolean value indicating whether an expression contains no valid data (Null). It is often confused with the VBA IsNull Empty function, which checks whether a given variable or cell is empty.

Consider the following example:

A variable is initialized at first. Then, it is checked whether it is a null value or not. Here, we use the VBA Null function to declare a null value. Hence, the VBA IsNull function returns true.

It can be viewed in the Immediate tab in the VBA Code Editor.

What is the VBA ISNULL Function?

The IsNull function is a built-in function in VBA that returns a Boolean value indicating whether an expression contains no valid data (Null).
An isNull function can be used with any sub-procedure or function procedure. It is available only in VBA and cannot be used with Excel worksheet functions.
This function can be used to check whether a field in a data table contains a Null value.
The IsNull function can replace Null values with a default value using the IIf function.
The function can be combined with other logical operators, such as Not, And, and Or, to create more complex logical expressions.

Syntax

The syntax for VBA IsNull is shown below

var = IsNull(variable)

Where,

The variable is checked for a valid data value. If not, it returns True.

How To Use VBA ISNULL Function?

To implement the VBA IsNull function in your VBA code, follow the steps below to learn how to do so.

Step 1: Open the Excel Workbook. In the title bar, go to “Developer” tab in the toolbar of the Workbook.

After clicking the “Developer” icon, go to the far left of the tools and select “Visual Basic.”

It opens the Excel VBA (Visual Basic for Applications) editor. To that, we need to add a new module. It can be done by clicking the “Insert” button on the toolbar of VBA and selecting “Module” in the drop-down section.

Now, you can create your very own subroutines for your VBA project.

Step 2: Create a sub-procedure to check whether a given variable has the “NULL” value.

Step 3: Initialize a variable and assign it some value. Here, the value is assigned as 0.

Step 4: Create an If-Else statement block where the VBA IsNull function checks whether the variable is null.

Step 5: Declare the Else part of the If-Else statement block. It uses the “Debug.Print” function.

Code:

Sub CheckIfValueIsNull()

Dim myCell As Integer

myCell = 0

If IsNull(myCell) Then

Debug.Print “It is a Null value.”

Else

Debug.Print “It is not a Null value.”

End If

End Sub

Step 6: To run the subroutine, hover over your cursor on the sub-procedure you want to run and press “F5” or the green arrow button on the VBA toolbar to run the program.

Due to using the “Debug.Print” function, the output is printed in the immediate tab. It returns VBA IsNull false.

Even though we’ve placed 0 as the value for the variable, it is not a null value since 0 is a valid integer.

Now that we know how to utilize the VBA IsNull function properly let us view some examples to expand our horizons on how to utilize the function.

Examples

You can go through the following examples to better understand how VBA IsNull works.

Example #1

Given an empty string, you must check if the value is Null or Not Null and print it accordingly.

Step 1: Define the subroutine you’re will check the empty string on.

Step 2: Define a VBA String variable and declare it an empty string with ” “.

Note: This is applicable only for the String and Variant datatypes. Any other datatype defined with an empty string will result in a VBA Type Mismatch error.

Step 3: Initialize an If-Else statement to check whether the given string is null.

If it is a Null value, it prints “NULL” in the immediate tab.

Step 4: Define the “Else” part of the If-Else statement and print the result.

Code:

Sub CheckIfStringIsNull()

Dim a As String

a = “”

If IsNull(a) Then

Debug.Print “NULL”

Else

Debug.Print “NOT NULL”

End If

End Sub

Step 5: Specify the above subroutine and run the code in the VBA Editor. The output is shown below:

Although an empty string is declared, it is not a Null Value since Excel VBA considers the String variable undefined or undeclared, but the data is still valid.

Example #2

Let us go through an example where we check if a given input from a user is Null.

Step 1: Define a subroutine to check if the input value from the InputBox() is Null or Not.

Step 2: Initialize a variable as the Variant datatype and assign it to the input value input by the user.

Step 3: Initialize a Boolean variable to store the result returned by the VBA IsNull function.

Step 4: With an If-Else statement, check whether the result returned VBA IsNull false, or true.

Step 5: Initialize another print function to deal with the Else block if the ‘If’ statement is false.

Code:

Sub CheckIfInputIsNull()

Dim ip As Variant

ip = InputBox(“Enter value”)

Dim res As Boolean

res = IsNull(ip)

If result = True Then

Debug.Print “Null? Yes.”

Else

Debug.Print “Null? No.”

End If

End Sub

Step 6: Run the above subroutine and view the output.

It returns as Not null. It is because we enter ‘Even’ if we leave an empty box, it will display the same.

Example #3

Consider a VBA Array with Null values. You need just integer values in your array. Hence, you must correctly identify the Null values and delete them. This process is used to clean datasets that have null values in Excel.

Step 1: Declare a subroutine to delete Null values.

Step 2: Declare a VBA Integer array and initialize your values.

Step 3: Define another array in VBA which is currently empty.

Step 4: Define two iterative variables and assign one as the LBound value of the array. It means that it iterates the number of columns available in the array.

Step 5: Declare a FOR loop running through the entire array. This checks if every variable is not Null with the VBA IsNull not function.

The non-null values are then added to the new array, and the iteration continues.

Step 6: Using the ReDim Preserve function, we remove all the empty variables and adjust the size of the VBA array from the variable.

Step 7: Print the array’s values in the immediate window using a FOR loop.

Code:

Sub DeleteNullElements()

Dim myArray As Variant

myArray = Array(1, 2, Null, 4, Null, 6, 7, Null, 9)

Dim newArray() As Variant

ReDim newArray(LBound(myArray) To UBound(myArray))

Dim i As Integer

Dim j As Integer

j = LBound(newArray)

For i = LBound(myArray) To UBound(myArray)

If Not IsNull(myArray(i)) Then

newArray(j) = myArray(i)

j = j + 1

End If

Next i

ReDim Preserve newArray(LBound(newArray) To j – 1)

For i = LBound(newArray) To UBound(newArray)

Debug.Print newArray(i)

Next i

End Sub

Step 8: Run the above subroutine, then check the immediate window.

We learned how to delete elements containing Null values in this example.

Example #4

Consider you have another array, but due to the application of statistical procedures, you can’t delete any values in the array. Hence, you need to utilize the concept of data preprocessing, where one way to deal with Null values is to substitute them with the Mean value of the entire array.

Step 1: Create a subroutine to replace null values with the mean value of the array.

Step 2: Declare an array as the Variant datatype and initialize the values inside it.

Step 3: Initialize two integer variables to find the sum and count of the array. It is used to find the size of the array and add all its elements (not null).

Step 4: Define a FOR loop running through the array and use the If-Else statement to add the not null values into the ‘sum’ variable.

Step 5: Define the variable storing the mean value of the array elements as a Double value.

Step 6: Initialize another FOR-loop and check for Null values in the array, then substitute them with the mean value.

Step 7: Print the values of the array onto the Immediate tab.

Code:

Sub ReplaceNullWithMean()

Dim myArray As Variant

myArray = Array(12, 24, Null, 34, Null, 67, 74, Null, 98)

Dim sum As Double

Dim count As Integer

sum = 0

count = 0

For i = LBound(myArray) To UBound(myArray)

If Not IsNull(myArray(i)) Then

sum = sum + myArray(i)

count = count + 1

End If

Next i

Dim mean As Double

mean = sum / count

For i = LBound(myArray) To UBound(myArray)

If IsNull(myArray(i)) Then

myArray(i) = mean

End If

Next i

For i = LBound(myArray) To UBound(myArray)

Debug.Print myArray(i)

Next i

End Sub

Step 8: Run the above example in the VBA Editor. The array is printed in the Immediate tab.

As we can see, all the Null values in the array were dealt with, without the need for deleting them.

Important Things To Note

The IsNull function returns True if an expression contains no valid data (Null); otherwise, it returns False.
Null is not the same as Empty, which indicates that a variable has not yet been initialized. It’s also not the same as a zero-length string (“”).
Use the IsNull function to determine whether an expression contains a Null value. Expressions that you might expect to evaluate to True under some circumstances, such as If Var = Null and If Var <> Null, always return VBA IsNull false.
If the expression consists of more than one variable, Null in any constituent variable causes True to be returned for the entire expression.
The Null value indicates that the Variant contains no valid data.
The IsNull function can be used with numeric expressions or string expressions.

Frequently Asked Questions (FAQs)

1. What is the difference between VBA IsNull vs VBA IsEmpty?
The difference between VBA IsNull Empty are shown below:

2. Why is VBA IsNull not working?

• Misunderstanding of the function: It’s essential to understand that Null is not the same as Empty or a zero-length string (“”).
• Syntax or usage error: The IsNull function may be used incorrectly in the code, leading to unexpected results.
• Undefined function: In some cases, the IsNull function may be reported as an undefined function by the VBA environment. It could be due to a missing reference or a problem with the installation.
• Alternative approaches: Depending on the specific use case, the IsNull function may not be the most appropriate solution. Consider using alternative methods, such as checking for Empty or zero-length strings.

3. How do you check if something is not null in VBA?

• Use the IsNull function to check if the expression contains a Null value.
• Use the Not operator to negate the result of the IsNull function.
• If the result is True, then the expression is not Null.

For example, below

    If Not IsNull({variable-name}) Then
        MsgBox “The variable is not Null.”
    Else
        MsgBox “The variable is Null.”
    End If
End Sub

Here, we implement VBA IsNull Not using the “Not” operator in VBA. It will return VBA IsNull False.

VBA ISNULL