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?
- What Data Types can be Null?
- Catching Unfilled Values with IsNull
- Catching Data Variation with IsNull
- Catching Errors with IsNull
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,
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
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
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
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
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 spreadsheets with our free VBA Developer's Guide Tutorials like this can be complicated. That’s why we created our free VBA Developer Guide to supplement this tutorial. Grab it now and we’ll also send you a bunch of pre-built macros.
Tutorials like this can be complicated. That’s why we created our free VBA Developer Guide to supplement this tutorial. Grab it now and we’ll also send you a bunch of pre-built macros.
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
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
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
.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
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.