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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With