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?

Erik A
  • 31,639
  • 12
  • 42
  • 67
HorusKol
  • 8,375
  • 10
  • 51
  • 92

1 Answers1

46
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.

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
  • 3
    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. – Joel Goodwin Apr 12 '11 at 07:16
  • 1
    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. – HorusKol Apr 12 '11 at 07:17
  • 3
    I've heard that in Office 2013, `Variant` will also take values of `Void`, `Absent`, `Unspecified`, `None`, and `Vacuum`. – Jean-François Corbett Apr 12 '11 at 07:24
  • 2
    @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). – jtolle Apr 12 '11 at 14:26
  • 1
    @jtolle Yeah, I'm clear about their roles, it's just the kind of thing that makes me despair about programming sometimes, to have concepts which all look like the same thing yet have completely distinct meanings. On the flip side, debate still rages over whether Null in SQL means "missing" "no data" or "unknown". I'm a SQL NULL hater in my spare time, incidentally. I think I've just published my psychological profile on Stack Overflow. – Joel Goodwin Apr 12 '11 at 15:00
  • @JoelGoodwin - my colleagues and I have always taken the position that NULL in SQL is to mean 'no data, and we do not care'... then the database is designed around where we might not have data, but we do care... – HorusKol Apr 12 '11 at 23:19
  • @HorusKol, as long as you have your own consistent interpretation that's the main thing. Half the time projects switch meaning mid-table. I could go on and on about NULLs but I pretty much put out my feelings down here: http://stackoverflow.com/questions/1034925/is-an-overuse-of-nullable-columns-in-a-database-a-code-smell/1035455#1035455 – Joel Goodwin Apr 13 '11 at 07:13
  • 2
    It would be nice if VBA could at least add the option of int? (or Nullable), long? (or Nullable) etc. One of the many reasons why I loath VBA (though, unfortunately, I'm often forced to work with it). – William Jan 21 '15 at 19:14
  • what would be the null equivalent for an unassigned string in VBA? – BenKoshy May 25 '16 at 12:36
  • Not sure what you mean by "equivalent". If you're asking, what's the value of a string variable that has been declared (`Dim`) but not explicitly assigned any value, then the answer is a zero-length, empty string, `""`. – Jean-François Corbett May 26 '16 at 07:33
  • @Jean-FrançoisCorbett It's a pitty they didn't include `Undefined`. JavaScript has it! We need to tell Microsoft they need to add this useful feature! – m93a Aug 22 '17 at 15:56
  • @Jean-FrançoisCorbett I am not sure if you are being sarcastic. When I goolge those keywords I get nothing except *this* question. – René Nyffenegger Aug 03 '18 at 06:40
  • @RenéNyffenegger Let me explain: that was an attempt at a form of communication known as "humour". Read more: https://en.wikipedia.org/wiki/Humour – Jean-François Corbett Aug 03 '18 at 07:48