Trending September 2023 # How To Use Vba Isnull Function In Excel? # Suggested October 2023 # Top 9 Popular

You are reading the article How To Use Vba Isnull Function In Excel? updated in September 2023 on the website Speedmintonvn.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 How To Use Vba Isnull Function In Excel?

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:

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?

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.

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

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

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

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

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

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.

Code:

Sub

VBA_IsNull()

Dim

Test

As Variant

Dim

Answer

As Boolean

Test = "VBA Macro" Answer = IsNull(Test)

End Sub

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

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

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.

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

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.

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

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

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

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

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.