VBA ISNULL

Publication Date :

15 Jun, 2019

Blog Author :

Edited by :

Reviewed by :

Table Of Contents

VBA ISNULL Function

ISNULL in VBA is a logical function used to determine whether a given reference is empty or NULL. That is why the name ISNULL is an inbuilt function that gives us True or False as a result. Based on the result, we can arrive at conclusions. For example, if the reference is empty, it returns a True or False value.

Finding the errors is not the easiest job in the world, especially in a huge spreadsheet finding them in between the data is almost impossible. Finding the NULL value in the worksheet is one of the frustrating jobs. To resolve this problem, we have a function called “ISNULL” in VBA.

This article will show you how to use the “ISNULL” function in VBA.

ISNULL is a built-in function in VBA and is categorized as an Information function in VBA that returns the result in Boolean type, i.e., either TRUE or FALSE.

If the testing value is “NULL, " it returns TRUE or will return FALSE. This function is available only with VBA. We cannot use this with the Excel worksheet function. However, we can use this function in any sub procedure and function procedure.

VBA ISNULL Function

Syntax

Take a look at the syntax of the ISNULL function.

This function has only one argument, i.e., “Expression.”
An expression is nothing but the value we are testing; the value could be a cell reference, direct value, or variable assigned value.
The Null indicates that the expression or variable does not contain valid data. Null is not the empty value because VBA thinks the variable value has not yet been started and does not treat it as Null.

Examples of ISNULL Function in VBA

Below are examples of the VBA ISNULL function.

Example #1

Start with a simple VBA ISNULL example. First, check whether the value Excel VBA is NULL. The below code is the demonstration code for you.

Code:

Sub IsNull_Example1()
'Check the value "Excel VBA" is null or not
'Declare two Variables
'One is to store the value
'Second one is to store the result

Dim ExpressionValue As String
Dim Result As Boolean

ExpressionValue = "Excel VBA"

Result = IsNull(ExpressionValue)

'Show the result in message box
MsgBox "Is the expression is null? : " & Result, vbInformation, "VBA ISNULL Function Example"

End Sub

When you run this code using the F5 key or manually, we will get the result as “FALSE” because the supplied value “Excel VBA” is not a NULL value.

Example #2

Now, check whether the value “47895” is NULL or not. Below is the code to demonstrate the formula.

Code:

Sub IsNull_Example2()
'Check the value 47895 is null or not

'Declare two Variables
'One is to store the value
'Second one is to store the result
Dim ExpressionValue As String
Dim Result As Boolean

ExpressionValue = 47895

Result = IsNull(ExpressionValue)

'Show the result in message box
MsgBox "Is the expression is null? : " & Result, vbInformation, "VBA ISNULL Function Example"

End Sub

Even this code will return the result as FALSE because the supplied expression value “47895” isn’t the NULL value.

Example #3

Now, check whether the empty value is NULL or not. For example, the below code tests whether the empty string is NULL or not.

Code:

Sub IsNull_Example3()
'Check the value "" is null or not

'Declare two Variables
'One is to store the value
'Second one is to store the result
Dim ExpressionValue As String
Dim Result As Boolean

ExpressionValue = ""

Result = IsNull(ExpressionValue)

'Show the result in message box
MsgBox "Is the expression is null? : " & Result, vbInformation, "VBA ISNULL Function Example"

End Sub

This formula also returns FALSE because VBA treats the empty value as a variable that is uninitialized yet and one cannot consider it as a NULL value.

Example #4

Now, we will assign the word “Null” to the variable “ExpressionValue” and see the result.

Code:

Sub IsNull_Example4()
'Check the value "" is null or not

'Declare two Variables
'One is to store the value
'Second one is to store the result
Dim ExpressionValue As Variant
Dim Result As Boolean

ExpressionValue = Null

Result = IsNull(ExpressionValue)

'Show the result in message box
MsgBox "Is the expression is null? : " & Result, vbInformation, "VBA ISNULL Function Example"

End Sub