How to Use IsNull Function in Excel VBA (5 Examples)

Identifying the errors isn’t the simplest thing in the world, and spotting them in the middle of a large spreadsheet is nearly burdensome. Discovering the Null value, which indicates that the checklist contains no valid data, is one of the most difficult tasks. In VBA, there is a built-in function named “IsNull” that may be applied to fix this problem. In this article, we’ll illustrate to you how to use the “IsNull” function in VBA in a number of ways.

VBA IsNull Function Overview

The IsNull function in VBA is characterized as an Information Test function. In MS Office Excel VBA, it’s a built-in function. The VBA IsNull function determines whether or not the supplied expression is Null. There is just one input argument for this function. VBA IsNull test delivers you a Boolean value with True or False. ‘True’ is returned if the specified expression is null; otherwise, ‘False’ is delivered.

⟴ Syntax

IsNull(Expression)

⟴ Return Value

Returns the value ‘True’ for null expression and ‘False’ for no null expression.

1. Testing a Text Expression “ExcelDemy” in VBA IsNull

In the beginning, we will test IsNull for a Text expression. As the specified text expression contains a value, the result will be returned as ‘False’. To have done the first test follow the steps below.

Step 1:

• First of all, press  Alt + F11 to open the VBA Macro.
• Secondly, Click on the Insert.
• Then, select Module.

Step 2:

• After Opening a Module window, simply paste the following VBA codes.

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

Here,

Dim Test As Variant is the Input Test declared as Variant.

Dim Answer As Boolean is the return value declared as Boolean.

Test = Range(“B3”).Value is the specification of cell reference for the test.

Answer = IsNull(Test) is applying the IsNull function for the test value.

MsgBox “Is the Test is null? : is the typed words in the message box result.

” & Answer, vbInformation, “VBA ISNULL Function Example” specifies the result shown in the message box.

Step 3:

• Click to Save.
• Then, click on to play icon to run the program.

Therefore, A message window will pop up and return the result as ‘False’.

 Note.  Shortcut to run a saved program: Press  F5.

Read More: How to Use VBA Val Function in Excel (7 Examples)

2. Examining a Numerical Expression “2020” Using VBA IsNull Function

In this section. We will conduct the null test for a Numerical value shown in the below screenshot. To conduct the test, just follow the same steps as described in Example#1.

Step 1:

• After opening VBA Macro by pressing Alt + F11, select a new Module.
• Then, Paste the following VBA codes.

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

Step 2:

• After pasting the VBA codes, save and run the program.

As cell B3 contains a numerical value, you will obtain the same result ‘False’ as have got in Example#1.

Read More: How to Use VBA IsNumeric Function (9 Examples)

3. Analyzing an Expression for an “Empty String” with the Help of the VBA IsNull Function

As seen in the figure below, we will conduct a null test on an empty cell. Follow the instructions outlined below to complete the test.

Step 1:

• Select a new Module after opening the VBA Macro as described in the previous sections.
• Then, paste the following VBA codes.

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

Step 2:

• Finally, save and run the program to test the result.
• As a result, it will pop up with the result ‘False’ as it is an empty cell but not null (invalid).

Read More: How to Use VBA Str Function in Excel (4 Examples)

Similar Readings:

• How to Use VBA Case Statement (13 Examples)
• VBA If – Then – Else Statement in Excel (4 Examples)
• How to Use VBA Right Function in Excel (6 Examples)
• VBA EXP Function in Excel (5 Examples)
• How to Use Log Function in Excel VBA (5 Suitable Examples)

4. Checking an Expression for an “Unassigned Variable”

In addition to the previous sections, we will also conduct the IsNull test for an unassigned value. ‘Unassigned’ refers to there being no reference declared for the input value. Follow the steps below to perform the test.

Step 1:

• First of all, to write a program, select a new Module.
• Then, paste the following VBA codes.

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

Here,

sOutput = IsNull(unassigned) is applying IsNull test for the unassigned value.

But look carefully that, there is no reference declared for the unassigned value. Contrary to the previous section, we have a reference cell B3 for the test value.

Step 2:

• Finally, save and run the program to see the result.

Consequently, the dialog box below will pop up with the message ‘False’ as the value was undefined but not null or invalid.

Read More: How to Use VBA IsEmpty Function (5 Relevant Examples)

5. Testing an Expression for Null with VBA IsNull Function

Finally, here may be the required section where we will test a null expression. The steps are as same as the previous ones just modification in expression.

Step 1:

• Like previously, Insert a new Module
• Then, copy the following VBA codes to paste.

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

Here,

Test = Null is the declaration of the test value as null.

Step 2:

• To run the program, save and click run.

Therefore, you will get the result ‘True’ as it is a null expression.

Related Content: How to Use IsDate Function in VBA (3 Examples)

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

To sum it all up, I hope this post has demonstrated how to use the VBA IsNull function in Excel to test an expression for multiple conditions. All of these techniques should be learned and used in your data. Examine the practice book and put what you’ve learned into practice. Because of your support, we’re able to continue to provide programs like these.

Please do not hesitate to contact us if you have any inquiries. Please inform me what you feel in the comments area below.

The Exceldemy experts will respond to your inquiries as quickly as possible.

Related Articles

• Excel VBA ASC() Function – Get ASCII Value of Character
• How to Create a Body Mass Index (BMI) Calculator in Excel Using VBA
• Use TRIM Function in VBA in Excel (Definition + VBA Code)
• How to Use VBA SPLIT Function in Excel (5 Examples)
• How to Use LCase Function in VBA in Excel (With 4 Examples)