As developers, a smooth-running program is our ultimate goal. If we can write a macro such that the program runs with minimal input and handles its own errors, we have succeeded - of course, assuming the output is what we wanted.

A frequent mistake is not catching easy-to-anticipate errors, like a null variable being passed into a function and throwing an error or outputting unexpected results. Let’s explore the Null value, its uses, and a couple applications for your own programs.

What is Null?

Null is used to indicate “invalid data” or unknown values. This is generally applied in the context of databases, where a field may be left blank when it’s optional. This differentiates that value from 0, an empty string "" or “nothing” values. As we know, 0 represents some information. It’s numeric, it non-negative (and non-positive), and it has a particular value (precisely between -1 and 1 on the integer spectrum). However, an optional field may change as more information becomes available. To indicate this potential for change, Null acts as a placeholder until more information surfaces.

In VBA, some functions or properties may output Null as well. This is the “invalid data” aspect of Null. For example, the following line of code normally outputs the color value of the highlight range:

rangeFontColor = Range("A1:D5").Font.Color

If all the cells have the same font color, rangeFontColor will give you that value. Conversely, if some of the cells have font of a different color than others, this property does not know what to return. If some cells have black font while others are red, should this property return 0 for black or 255 for red? Surely we do not want the property to produce an array of values, as this would be very difficult to anticipate. It could be a 1-element array, a 3-element array, or any other number of elements.

Thus, in this example, the data is “invalid”, and the property will read Null.

WhatData Types can be Null?

In VBA, there’s a single variable type that accepts Null as a value: Variant. If you try to assign Null to any other variable type (Integer, String, etc), you’ll receive an error.

I don’t recommend typing all your variables as Variant, though. Since Variant can take on any data type, it must have all the structures available to it and thus has a larger memory footprint than a type like Boolean. More importantly, though, narrowly typing your variables can help you enforce code integrity and find mismatched data types at compile time to avoid runtime errors.

With that said, Variants have their place in code and can be useful for finding unfilled fields in databases (including those implemented in Excel sheets).

Catching Unfilled Values with IsNull

For this first example of Null, let’s use the customer spreadsheet from our Find and Replace Cells with VBA post. You can download the CSV here.

In this sheet, though, let’s assume a customer made an order by phone and it was undecided which shipping method would be used. Because of that, the final Amount column has been left blank until the customer confirms the preferred method tomorrow. For this example, edit the CSV file to take out a customer’s payment amount in Column E, like this:

VBA IsNull on Empty Cell

Now, let’s say you want to write a macro that does a basic data integrity check on your data to see if any bits of information are missing. We can assume all orders must have an Order Number in column A, but other fields are optional.

One way to do this is to simply count the number of entries for each column and assign a separate variable to be Null if it has fewer entries than the Order Number column, which by definition must be filled.

Then, because IsNull will return TRUE if any part of an expression is Null, you can test them all at once. The full code block looks like this:

Sub basicDataIntegrityCheck()
Dim orderCount As Integer, nameCount As Integer
Dim methodCount As Integer, amountCount As Integer
Dim oC As Variant, nC As Variant
Dim mC As Variant, aC As Variant

orderCount = WorksheetFunction.CountA(Columns("B"))
nameCount = WorksheetFunction.CountA(Columns("A"))
methodCount = WorksheetFunction.CountA(Columns("D"))
amountCount = WorksheetFunction.CountA(Columns("E"))

If nameCount < orderCount Then nC = Null
If amountCount < orderCount Then aC = Null
If methodCount < orderCount Then mC = Null

If IsNull(aC + mC + nC) Then MsgBox ("Some fields are empty")


End Sub

Make powerful macros with our free VBA Developer Kit

Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.

I'll take a free VBA Developer Kit

Now, why would you use this method instead of simply writing an if-statement for each column, like this?

If nameCount < orderCount Then MsgBox ("Some names are missing")

With the variables aC, mC, nC, you can now use these variables in other parts of your macro. Perhaps you want to send these numbers off to another module to do some calculations. It’s advantageous to write the assignment once then use stored values rather than having to re-evaluate the CountA functions in another module.

Catching Data Variation with IsNull

The other major source of Null values may fall out of the evaluation of built-in VBA functions or properties that are not producing expected results - hence the “invalid data” piece of the VBA IsNull function.

Using the same data set from above, let’s say you instruct employees to highlight any tentative details, such as the shipping method, with a different background color. To check whether any such highlights exist, you can write a simple one-liner to test for variation in cell background colors:

If IsNull(Range("A:G").Interior.ColorIndex) Then MsgBox ("Some data is tentative. Check manually")

Conditional statements like If IsNull are probably the most common way people use the VBA IsNull function.

Notice in this case you don’t have to assign the output to any variable at all and can simply feed the output of the .ColorIndex property to the IsNull function.

Catching Errors with IsNull

Sometimes our programs aren’t built to anticipate what a user will do, so they error out when encountering something odd. Catching errors is very important for user experience, though, even if it is a thankless job.

For example, if we wanted to write the color of the A:G range from our spreadsheet, the .ColorIndex property seems like the perfect way to identify it. Consider the following code and think about why it’s not advised:

Dim bColor As Integer
bColor = Range("A:G").Interior.ColorIndex

If any cell in columns A through G have a different background color than all the other cells, your program will throw a “Run-time error ‘94’: Invalid use of Null” error. This happens because the computer is trying to assign Null to an integer-typed variable. Remember, only Variants can be set to Null.

It’s certainly useful to type bColor as an Integer, but withour a robust code, your user may call you up asking why there’s an error.

To avoid that call and the associated debugging, use error trapping with IsNull and handle the issue in the background without ever notifying the user.

Sub IsNullErrorTrapping()
Dim bColor As Integer
If IsNull(Range("A:G").Interior.ColorIndex) Then
    'code to handle issue
Else
    bColor = Range("A:G").Interior.ColorIndex
End If
End Sub

To recap, the Null value in VBA is used for missing or invalid data, and it has close connections to database entries. You can only assign Null values to Variant data types. Otherwise, you’ll get a “Run-time error ‘94’: Invalid use of Null” error.

Properties and many functions can also result in Null values, and that makes the VBA IsNull function useful for trapping errors and handling them in the background to avoid interrupting the user.

As with most programming tasks, the use of Null can require a little creativity, and thinking in this way will improve your program user experience (UX).

To get more free VBA training tips like this, join us using the form below.