VBA IsNull in Excel — What Null Really Means, and Why = Null Never Works

TL;DR — Null is the tag for "no single answer". In Excel VBA you almost never get it from a cell: an empty cell is Empty, so IsNull on a blank cell is always False. You get Null when you read a property of a range whose cells disagree, such as Font.Bold over bold and plain cells, NumberFormat over mixed formats or RowHeight over rows of different heights, and when you read a database field. Two rules follow: test it only with IsNull, because x = Null is itself Null and never True; and read range-wide properties into a Variant, because assigning Null to a Boolean, String or Double stops with error 94, Invalid use of Null.

Dim bold As Variant
bold = Range("A1:A10").Font.Bold
If IsNull(bold) Then Debug.Print "Some cells are bold, some are not"

This is the third article in a cluster on asking a value what it is: IsEmpty for values never assigned, VarType and TypeName for reading any tag, and here IsNull for the tag that means mixed or unknown. The cluster idea is that a VBA value carries a type tag separately from its content, and these tests read the tag, not what the value looks like.

What you'll learn

• The mental model: Null means no single answer
• Where Null comes from in Excel, and where it does not
• Why = Null never works
• Error 94, Invalid use of Null
• How Null spreads through expressions
• Handling a mixed range as a third answer
• Null from databases, at the border

The mental model: Null means no single answer

Empty means never given a value. Null means there is no single value to give. A question like "is this range bold?" has three honest answers: yes, no, and "some of it". VBA has no third Boolean, so it answers with a Variant holding Null.

Once you see Null as "the cells disagree" or "the value is unknown", its behaviour makes sense. A comparison with an unknown value has an unknown result. A sum that includes an unknown is unknown. And a Boolean variable, which only holds True or False, has nowhere to put "some of it".

Where Null comes from in Excel, and where it does not

Not from cells. An empty cell's value is Empty, a cell with "" is a String, an error cell is an Error. IsNull(Range("A1").Value) is False for all of them, which is why IsNull used as a blank-cell test never fires. Use IsEmpty for that.

From range properties over cells that disagree. Many Range properties return Null when the cells in the range do not share one value:

Selection.Font.Bold over a selection that is partly bold is the everyday example: there is no True or False that describes it, so the answer is Null.

From outside Excel. A field read from a database through ADO is Null when the database column holds NULL.

Why = Null never works

The first instinct is to compare:

v = Range("A1:A10").Font.Bold
If v = Null Then Debug.Print "mixed"        ' never prints
If v <> Null Then Debug.Print "not mixed"   ' never prints either

Any comparison with Null is itself Null, because comparing with an unknown gives an unknown. If treats a Null condition as not True, so both branches above are skipped, whatever v holds. There is no compile error and no run-time error; the check is silently dead. IsNull(v) is the only test that works.

Error 94, Invalid use of Null

The second instinct is to declare the type you expect:

Dim isBold As Boolean
isBold = Range("A1:A10").Font.Bold      ' Run-time error 94: Invalid use of Null

This works on every test sheet where the range is all bold or all plain, and fails the first time a user has bolded a single cell. The same happens with Dim fmt As String: fmt = rng.NumberFormat on mixed formats, with Dim h As Double: h = Rows("1:5").RowHeight on rows of different heights, and with CStr(Null) or CLng(Null). Only a Variant can hold Null.

The fix is always the same: read range-wide properties into a Variant, test IsNull, then convert.

Dim fmt As Variant
fmt = rng.NumberFormat
If IsNull(fmt) Then
    Debug.Print "Mixed formats in " & rng.Address
Else
    Debug.Print "All cells use " & fmt
End If

How Null spreads through expressions

Null propagates through arithmetic and most functions, but not through the & operator:

Dim n As Variant
n = Null
Debug.Print n + 1            ' Null
Debug.Print Len(n)           ' Null
Debug.Print "Total: " + n    ' Null   (+ propagates)
Debug.Print "Total: " & n    ' Total: (& treats Null as "")

So a Null that enters a calculation travels to the end of it and only fails when it reaches a typed variable or a function that needs a real value. The error 94 you see may be several lines after the place where Null came in. When you meet error 94, look for the first property or field read in that expression, not the line that failed.

Handling a mixed range as a third answer

Mixed is not an error; it is information. Decide what your macro should do with it. A common rule, the one Word uses for its Bold button, is that a mixed selection becomes all bold. A toggle written only with Not cannot make that choice, because Not Null is Null:

Sub ToggleBold()
    Dim rng As Range, state As Variant
    If TypeName(Selection) <> "Range" Then Exit Sub
    Set rng = Selection
    state = rng.Font.Bold
    If IsNull(state) Then
        rng.Font.Bold = True            ' mixed: make it all bold
    Else
        rng.Font.Bold = Not state
    End If
End Sub

The same pattern applies to any property in the table above: read into a Variant, give the mixed case its own branch, then act. For column widths and row heights, the mixed branch is usually "set them all to one value"; for NumberFormat, it is usually "report which cells differ" before you overwrite anything.

Null from databases, at the border

When you read records with ADO, any NULL column arrives as Null and fails as soon as you put it in a typed variable or add it to a total. Convert it once, where it enters your code. Excel VBA has no Nz function (that is Access), so write a small one:

Function NzV(ByVal v As Variant, Optional ByVal ifNull As Variant = "") As Variant
    If IsNull(v) Then NzV = ifNull Else NzV = v
End Function

total = total + NzV(rs!Amount, 0)
customer = NzV(rs!CustomerName, "")

Writing a whole recordset to the sheet with Range.CopyFromRecordset needs no conversion: Null fields become empty cells.

The judgment call: Null is a third answer, not a bug

Most Null bugs are type declarations that left no room for "some of it". The rule that prevents them: any property read from a multi-cell range goes into a Variant, and the mixed case gets its own branch. Never compare with = Null; never feed a range-wide property straight into a Boolean, String or Double.

And do not reach for IsNull to test cells. A blank cell is Empty, a cleared input is "", a failed lookup is an Error. If your code is testing cells with IsNull, the test you want is IsEmpty, Len or IsError.

How ExcelMaster helps

Code that reads formatting usually breaks on real workbooks, where a range is never quite uniform: one bold cell, one merged header, one row a little taller than the rest.

ExcelMaster inspects the formatting of the actual range before it writes code, sees where cells disagree, and handles the mixed case explicitly instead of leaving it to error 94.

Frequently asked questions

What does IsNull do in VBA?

IsNull(v) returns True if v is a Variant holding Null, the value that means "no single answer" or "unknown". In Excel you mostly meet it from range properties over cells that disagree, and from database fields.

Why does IsNull return False for an empty cell?

Because an empty cell is Empty, not Null. Use IsEmpty(Range("A1").Value) to test for a cell that was never filled in.

What causes run-time error 94 Invalid use of Null?

Assigning Null to a variable that cannot hold it, such as a Boolean, String, Long or Double, or passing Null to a function such as CStr. Read the value into a Variant and test IsNull first.

Why does If x = Null never work in VBA?

Any comparison with Null returns Null, and If treats Null as not True. Both x = Null and x <> Null are skipped. Use IsNull(x).

How do I check if a range has mixed formatting in VBA?

Read the property into a Variant and test it: If IsNull(rng.Font.Bold) Then. Properties such as Font.Bold, NumberFormat, RowHeight and HorizontalAlignment return Null when the cells differ.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-10-04.

Related guides: VBA IsEmpty · VBA VarType and TypeName · VBA Nothing · VBA Font · VBA Number Format · VBA Column Width · VBA Data Types · VBA Error Handling