Vba Isnull Function How To Use Vba Isnull To Find Null Values

VBA ISNULL Function

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.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA ISNULL (wallstreetmojo.com)

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 referenceCell ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more, 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.

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.

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.

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 use the F5 key. Then, this code will return TRUE because the supplied value is NULL.

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