VBA ISNULL

VBA ISNULL Function

ISNULL in VBA is a logical function which is used to determine whether a given reference is empty or NULL or not that is why the name ISNULL, this is an inbuilt function which gives us true or false as a result, based on the result we can arrive to conclusions, if the reference is empty it returns true value else false value.

Finding the error isn’t 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.

In this article, we 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 which returns the result in Boolean type i.e. either TRUE or FALSE.

If the testing value is “NULL” then it returns TRUE or else it will return FALSE. This function is available only with VBA and we cannot use this with Excel worksheet function. This function can be used in any subprocedure and function procedure.

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 and the value could be a cell reference, direct value, or variable assigned value as well.
• The Null indicates that expression or variable doesn’t contain valid data. Null is not the empty value because VBA thinks variable value has not yet been started and doesn’t treat as Null.

Examples of ISNULL Function in VBA

Below are the examples of the VBA ISNULL Function.

Example #1

Start off with a simple VBA ISNULL example. Check whether the value “Excel VBA” is NULL or not. 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 then, we will get the result as “FALSE” because the supplied value “Excel VBA” is not a NULL value.

Example #2

Now check 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. Below code is to test 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 is not yet initialized and cannot be considered as a NULL value.

Example #4

Now I will assign the word “Null” to the variable “ExpressionValue” and see what the result is.

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

Run this code manually or using the F5 key then, this code will return TRUE as a result because the supplied value is NULL.

You can download this VBA ISNULL Function template here – VBA ISNULL Excel Template

Recommended Articles

This has been a guide to VBA ISNULL. Here we learn how to use VBA ISNULL Function to find the null values in Excel Worksheet along with practical examples and downloadable codes. Below are some useful excel articles related to VBA –

• CSTR in Excel VBA
• Count Function in VBA
• AutoFill in VBA
• Random Numbers in VBA
• VBA Save As