26

How do I define a Null string, date or integer in VBA?

I need to be able to assign a Null value to some fields for certain records when data is incomplete or irrelevant, but if I declare a variable as a String, Date or Integer, I get errors when trying to assign a Null value.

Is the only solution to use Variant? If so, then what is the point of all other datatypes in VBA?

Improve this question

1 Answer 1

Reset to default
48 
Dim x As Variant
x = Null

Only the Variant data type can hold the value Null.

A Variant is a special data type that can contain any kind of data [...] A Variant can also contain the special values Empty, Error, Nothing, and Null.

The "point" of all the other data types is precisely that they cannot contain any ol' kind of data. This has two advantages that I can think of:

  • It's more difficult for the programmer to assign data of an unintended type to the variable by mistake, since this will be detected at compile time. This can help prevent bugs and make things clearer for you and the next person who will be maintaining your code.
  • Narrow data types save storage space. Putting integers in a Variant (16 bytes) takes up way more memory than putting them in an Int (2 bytes). This becomes significant if you have large arrays.

Of course, Variants do have their place, as other threads on this site discuss.

Improve this answer
Sign up to request clarification or add additional context in comments.

12 Comments

I've been programming VBA for a long loonnng time, but it still upsets me whenever I read that a Variant can take three different special values which all sound exactly the same: Empty, Nothing and Null.
Maybe it's my SQL and PHP experience - but I don't consider Null to be 'any old data', and it is a valid value for most SQL datatypes... oh well, variants it is then.
I've heard that in Office 2013, Variant will also take values of Void, Absent, Unspecified, None, and Vacuum.
@Joel, I think there is some history there. Doesn't Empty (as in "uninitialized Variant value") in the VB language family predate Nothing (as in "object reference that doesn't point to anything")? And as @HorusKol says, Null is a specific value (and I'm pretty sure Null in VBx was always intended to be the "database" null).
It would be nice if VBA could at least add the option of int? (or Nullable<int>), long? (or Nullable<long>) etc. One of the many reasons why I loath VBA (though, unfortunately, I'm often forced to work with it).
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.