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.


Download Practice Workbook

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


An Introduction to the VBA IsNull Function

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)

An Introduction to the VBA IsNull Function

⟴ Return Value

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

An Introduction to the VBA IsNull Function


5 Examples of Using VBA IsNull Function

1. Test 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.

Test a Text Expression “ExcelDemy” in VBA IsNull

Step 1:

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

Test a Text Expression “ExcelDemy” in VBA IsNull

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 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.

Test a Text Expression “ExcelDemy” in VBA IsNull

Step 3:

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

Test a Text Expression “ExcelDemy” in VBA IsNull

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

Test a Text Expression “ExcelDemy” in VBA IsNull

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

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


2. Examine a Numerical Expression “2020” in VBA IsNull

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

Examine a Numerical Expression “2020” in VBA IsNull

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.

Test a Text Expression “ExcelDemy” in VBA IsNull

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


3. Analyze an Expression for an “Empty String” in VBA IsNull

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

Analyse an Expression for an “Empty String” in VBA IsNull

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

Analyse an Expression for an “Empty String” in VBA IsNull

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).

Analyse an Expression for an “Empty String”

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


Similar Readings:


4. Check 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 these 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.

Check an Expression for an “Unassigned Variable”

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.

Check an Expression for an “Unassigned Variable”

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


5. Test an Expression for Null with VBA IsNull

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 test value as null.

Test an Expression for Null

Step 2:

  • To run the program, save and click run.

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

Test an Expression for Null

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


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

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

2 Comments
  1. Merci c’est bien intéressant, pédagogique et graduel

Leave a reply

ExcelDemy
Logo