Troubleshooting “Invalid use of Null” in Database-Linked Sheets

by

Troubleshooting “Invalid use of Null” in Database-Linked Sheets

The VBA macro reads customer data from an Access database into Excel. For 990 of 1,000 rows it works. On row 174, the macro halts: “Run-time error ’94’: Invalid use of Null.” The row contains a NULL value in the Phone column. Database NULL doesn’t translate cleanly to VBA’s string or number types, and unguarded operations on NULL produce error 94.

Before You Start: The 60-Second Diagnostic

Three checks:

  • Identify the failing line: Click Debug → which assignment or comparison fails?
  • Inspect the offending value: Locals window shows Null (capital N).
  • Check the column type: NULL is allowed in nullable database columns; string columns may be NULL.

Step-by-Step Solution

H2: Use IsNull to Guard Operations

Before using a value that could be NULL, check:

If IsNull(rs!Phone) Then
  phoneStr = ""  ' Default value
Else
  phoneStr = rs!Phone
End If

IsNull is the VBA function specifically for detecting NULL values from database queries.

H2: Use Nz for Default Substitution

The Nz function (built into VBA for Access scenarios) provides defaults:

phoneStr = Nz(rs!Phone, "")  ' Returns "" if NULL, else the value

For Excel VBA without Access reference loaded:

Function Nz(value As Variant, Optional defaultValue As Variant = "") As Variant
  If IsNull(value) Then
    Nz = defaultValue
  Else
    Nz = value
  End If
End Function

Use throughout your code:

firstName = Nz(rs!FirstName, "")
phoneStr = Nz(rs!Phone, "")
amount = Nz(rs!Amount, 0)

H2: Distinguish NULL from Empty

Three “missing value” states in VBA:
– Null: from database, distinct from empty string.
– Empty: Variant variable never assigned.
– Empty string “”: zero-length string.

Three different detection functions:

If IsNull(x) Then ...      ' Is x NULL?
If IsEmpty(x) Then ...     ' Was x never assigned?
If x = "" Then ...          ' Is x an empty string?

For comprehensive checks:

Function IsMissing(value As Variant) As Boolean
  IsMissing = IsNull(value) Or IsEmpty(value) Or _
              (VarType(value) = vbString And value = "")
End Function

This treats Null, Empty, and “” all as “missing”.

H2: Handle NULL in Concatenation

Concatenation with NULL produces NULL (not error in all cases, but unexpected results):

result = firstName & " " & lastName  ' If lastName is NULL, result is NULL

Use Nz inside concatenation:

result = Nz(firstName, "") & " " & Nz(lastName, "")

Or use the + operator which differs subtly:

result = firstName + " " + lastName  ' Errors if any is NULL (not silent)

The & operator coerces non-strings; + requires strings throughout. For mixed-type concatenation, & is preferred.

H2: Handle NULL in Arithmetic

NULL in arithmetic operations propagates:

total = price * quantity  ' If price is NULL, total is NULL

Coerce to a numeric default:

total = Nz(price, 0) * Nz(quantity, 0)

For mathematical correctness, you might want NULL to propagate (NULL is “unknown” — multiplying unknown by anything is unknown). VBA doesn’t enforce this consistently, so defensive coding is needed.

H2: Audit Database Queries

For database-linked data, audit which columns can be NULL:

SELECT COLUMN_NAME, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTable'

For SQL Server. For Access, similar metadata queries exist. Knowing which columns can be NULL guides your defensive coding.

For frequently-NULL columns, consider modifying the query to apply defaults at the database:

SELECT CustomerID, COALESCE(Phone, '') AS Phone, COALESCE(Email, '') AS Email
FROM Customers

COALESCE returns the first non-NULL argument. Default values come from the query itself; VBA sees clean strings instead of NULL.

Information Gain Box: The Hidden Empty vs Null Returned from Excel

Here is what catches developers reading Excel cells as if they were database results: Excel cells with no value are returned as Empty (not NULL) from VBA, but cells set to ="" (empty string formula) are returned as vbString zero-length strings.

Three states for an Excel cell:
– Truly empty: never had a value → VBA returns Empty.
– Empty string from formula: ="" → VBA returns "" (string).
– Cleared cell: had a value, then deleted → VBA returns Empty again.

Detection:

Dim val As Variant
val = Range("A1").Value

If IsEmpty(val) Then
  Debug.Print "Truly empty"
ElseIf val = "" Then
  Debug.Print "Empty string from formula"
ElseIf IsNumeric(val) Then
  Debug.Print "Number: " & val
Else
  Debug.Print "Text: " & val
End If

The distinction matters for database-style logic: an Excel cell that *looks* empty might be either truly empty or a formula returning empty. They behave differently in subsequent operations.

For consistency, treat any “empty-looking” value as missing:

Function IsCellMissing(c As Range) As Boolean
  IsCellMissing = IsEmpty(c.Value) Or _
                  (VarType(c.Value) = vbString And c.Value = "") Or _
                  IsNull(c.Value)
End Function

This unified missing-detection covers all three states. Use throughout code that processes data from cells.

Comparison Table: Wrong Way vs. Correct Way

Pattern Wrong Way (#94) Correct Way
Reading from DB Direct use of recordset field Wrap in IsNull check
Default substitution Manual IF/ELSE per field Use Nz function consistently
Concatenation & with possibly-NULL Wrap each part in Nz
Arithmetic price * qty Nz(price, 0) * Nz(qty, 0)
Missing detection Single function (IsNull) Combined IsNull, IsEmpty, “” checks
Database queries Trust column non-null Use COALESCE in SQL for defaults
Excel vs DB Treat same Excel returns Empty, DB returns Null — handle both

Original Image Descriptions

Screenshot 1: Show VBA editor paused on phoneStr = rs!Phone with “Run-time error ’94’: Invalid use of Null” dialog. The Locals pane shows rs!Phone = Null. Draw a red circle around the Null value. Add a red annotation: “Database NULL cannot be assigned to a string variable without coercion.”

Screenshot 2: Show the corrected line: phoneStr = Nz(rs!Phone, ""). The macro runs successfully through all rows. Draw a red circle around the Nz wrapper. Add a red annotation: “Nz substitutes a default for NULL — safe assignment.”

Frequently Asked Questions

Q: Why does VBA have so many “missing value” concepts?
A: Historical accumulation. Null came from Variant types for COM compatibility. Empty is from uninitialized variables. Empty string is normal string handling. Each has its own purpose, but they overlap confusingly in practice. For consistent code, define your own “missing” check function and use it everywhere.

Q: Will the Nz function work without Microsoft Access installed?
A: The built-in Nz requires Access reference. For Excel VBA standalone, define your own Nz function (as shown above) and use it. Most VBA codebases include a utility module with custom Nz, IsBlank, and similar helpers — distribute as a shared module across projects.

Q: How can I prevent NULL values from being inserted into the database in the first place?
A: At the database level, declare columns as NOT NULL with default values: Phone VARCHAR(50) NOT NULL DEFAULT ''. This guarantees the column never has NULL. From the application side, validate inputs before INSERT: ensure all required fields have values. Defense in depth: validate at multiple layers.