Trying to resolve an "invalid use of Null" in VBA

Question

Quick snippet first:

Dim GUID As String
Dim givenNames, familyName, preferredName, gender, comments, carer, medicareNumber, patientNumber As String
Dim dob As Variant
Dim deceased, resolved, consultNotes As Boolean
Dim age As Variant

givenNames = Null
familyName = Null
preferredName = Null
gender = Null
dob = Null
comments = Null
deceased = False
resolved = False
carer = Null
age = Null
consultNotes = False
patientNumber = Null ' This is where I get the error

Any idea why this last variable would be the one to trip up? I've assigned Null to a bunch of other strings without any errors.

Solution 1

In VBA/VB6, strings cannot be set to Null; only Variants can be set to null. In addition, when you declare variables inline comma-separated like in the question, only the last one will be typed as string; all of the others are typed as variants. To declare them on one line as a type you have to include the type

Dim a As String, Dim b As String ...

That's why it makes sense to just declare them on a single line.

(Btw, it should be noted that deceased, resolved are also typed as variants for the same reason.)

Solution 2

The reason that it succeeds for givenNames, etc. is that you have unwittingly defined them as Variant type. It fails for patientNumber, because you successfully defined that as a String, and strings do not accept Null values.

Within a Dim statement, the As <type> clause applies to each individual variable in the list, so by putting it only at the end of the list, you applied the explicit type only to the last-listed variable. The implicit type of Variant is applied to the others.

Solution 3

When I ran into that problem without knowing about the implicit type of Variant, I was able to use the work around of defining an extra variable such as BogusVariable at the end of the Dim statement list.

Comments

[Steve Jorgensen]

@HorusKol - np - glad we could help.