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.
14 Answers
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.)
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.
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.
in VBA strings uses vbNullString value for Null
