EDUCBA

Home VBA VBA Resources VBA Information Functions VBA ISNULL

VBA ISNULL

Ashwani Jaiswal
Article byAshwani Jaiswal
Madhuri Thakur
Reviewed byMadhuri Thakur

Excel VBA ISNULL

VBA ISNULL Function

ISNULL function in VBA is used for finding the null value in excel. This seems easy but when we have a huge database which is connected to multiple files and sources and if we asked to find the Null in that, then any manual method will not work. For that, we have a function called IsNull in VBA which finds the Null value in any type of database or table. This can only be done in VBA, we do not have any such function in Excel.

 

 

Syntax of ISNULL in Excel VBA

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

The syntax for the VBA ISNULL function in excel is as follows:

ISNULL Function

As we can see in the above screenshot, IsNull uses only one expression and to as Boolean. Which means it will give the answer as TRUE and FALSE values. If the data is Null then we will get TRUE or else we will get FALSE as output.

How to Use VBA ISNULL Function in Excel?

We will learn how to use a VBA ISNULL function with example in excel.

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

Example #1 – VBA ISNULL

Follow the below steps to use IsNull in Excel VBA.

Step 1: To apply VBA IsNull, we need a module. For this go to the VBA window and under the Insert menu select Module as shown below.

VBA IsNull Module

Step 2: Once we do that we will get a blank window of fresh Module. In that, write the subcategory of VBA IsNull or in any other name as per your need.

Code:

Sub VBA_IsNull()

End Sub

VBA ISNULL Example 1.1

Step 3: For IsNull function, we will need one as a Variant. Where we can store any kind of value. Let’s have a first variable Test as Variant as shown below.

Code:

Sub VBA_IsNull()

Dim Test As Variant

End Sub

VBA ISNULL Example 1.2

Step 4: As we know that IsNull works on Boolean. So we will need another variable. Let’s have our second variable Answer as Boolean as shown below. This will help us in knowing whether IsNull is TRUE or FALSE.

Code:

Sub VBA_IsNull()

Dim Test As Variant
Dim Answer As Boolean

End Sub

VBA ISNULL Example 1.3

Step 5: Now give any value to the first variable Test. Let’s give it a text value “VBA Macro” as shown below.

Code:

Sub VBA_IsNull()

Dim Test As Variant
Dim Answer As Boolean
Test = "VBA Macro"

End Sub

VBA ISNULL Example 1.4

Step 6: Now we will use our second variable Answer with IsNull function as shown below.

Code:

Sub VBA_IsNull()

Dim Test As Variant
Dim Answer As Boolean
Test = "VBA Macro"

Answer = IsNull(

End Sub

Function Example 1.5

As we have seen in the explanation of VBA IsNull, that syntax of IsNull is Expression only. And this Expression can be a text, cell reference, direct value by entering manually or any other variable assigned to it.

Step 7: We can enter anything in Expression. We have already assigned a text value to variable Test. Now select the variable into the expression.

Code:

Sub VBA_IsNull()

Dim Test As Variant
Dim Answer As Boolean
Test = "VBA Macro"

Answer = IsNull(Test)

End Sub

Test Example 1.6

Step 8: Once done, then we will need a message box to print the value of IsNull if it is TRUE or FALSE. Insert Msgbox and give any statement which we want to see. Here we have considered “Is the Test is null?” as shown below.

Code:

Sub VBA_IsNull()

Dim Test As Variant
Dim Answer As Boolean
Test = "VBA Macro"

Answer = IsNull(Test)
MsgBox "Is the Test is null? : "

End Sub

Excel VBA ISNULL Example 1.7

Step 9: And then add rest of the variable which we defined above separated by the ampersand (&) as shown below which includes our second variable Answer and name of the message box as “VBA ISNULL Function Example”.

Code:

Sub VBA_IsNull()

Dim Test As Variant
Dim Answer As Boolean
Test = "VBA Macro"

Answer = IsNull(Test)
MsgBox "Is the Test is null? : " & Answer, vbInformation, "VBA ISNULL Function Example"

End Sub

VBA ISNULL Example 1.8

Step 10: Now compile the code by pressing F8 and run it by pressing the F5 key if there is no error found. We will see the Isnull function has returned the Answer as FALSE. Which means text “VBA Macro” is not null.

VBA ISNULL

Step 11: Now we will see if numbers can be null or not. For this, we use a new module or we can use the same code that we have written above. In that, we just need to make changes. Assign any number to Test variable in place of text “VBA Macro”. Let’s consider that number as 123123 as shown below.

Code:

Sub VBA_IsNull()
Dim Test As Variant
Dim Answer As Boolean
Test = 123123
Answer = IsNull(Test)
MsgBox "Is the Test is null? : " & Answer, vbInformation, "VBA ISNULL Function Example"
End Sub

Example 1.9

Step 12: Now again compile the code or we can compile the current step only by putting the cursor there and pressing F8 key. And run it. We will get the message box with the statement that our Test variable which is number 123123 is also not a Null. It is FALSE to call it a null.

Example 1.9

Step 13: Now it is clear that neither Text nor Number can be Null. To test further, now we will consider a blank. A reference which has no value. For this, in the same previously written code put the double inverted (“”) commas with nothing in it in Test variable as shown below. Now we will is if a Blank can be a null or not.

Code:

Sub VBA_IsNull()
Dim Test As Variant
Dim Answer As Boolean
Test = ""
Answer = IsNull(Test)
MsgBox "Is the Test is null? : " & Answer, vbInformation, "VBA ISNULL Function Example"
End Sub

Example 1.10

Step 14: We will get a message which says the Blank reference is also not null. It is FALSE to call it so.

Excel Example 1.8

Step 15: We have tried Text, Number and Blank for testing if they are null or not. Now we will text Null itself under variable Test as see if this is a null or not.

Code:

Sub VBA_IsNull()
Dim Test As Variant
Dim Answer As Boolean
Test = Null
Answer = IsNull(Test)
MsgBox "Is the Test is null? : " & Answer, vbInformation, "VBA ISNULL Function Example"
End Sub

VBA ISNULL Example 1.7

Step 16: Now run the code. We will in the message box the statement for “Is the Test is null?” has come TRUE.

VBA IN

Which means, in data if there are any cells with Blank, Space, Text or Number. Those cells will not be considered as Null.

Pros of VBA IsNull

  • We can find if a cell is Null or not.
  • We can test any variable if it is null or not.
  • This quite helps in a big database which is fetched from some source.

Things to Remember

  • IsNull finds only Null as Null. Text, Numbers, and Blanks are not null.
  • IsNull is only applicable in VBA. Excel doesn’t have any function as IsNull or other matching function which can give the same result as IsNull.
  • To use the code multiple times, it is better to save the excel in Macro Enable Excel format. This process helps in retaining the code for future use.
  • IsNull only returns the value in the Boolean form, means in TRUE and FALSE
  • Considering the Variant as Test variable allow us to use numbers, words and blank values in it. It considers all the type of values majorly used for Boolean.

Recommended Articles

This is a guide to VBA ISNULL. Here we discuss how to use Excel VBA ISNULL function along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA InStr
  2. VBA Integer
  3. VBA Select Cell
  4. VBA Transpose