Null values for variables in VBA

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

More Related Contents:

  1. How does one decompile and recompile a database application?
  2. What are the rules governing usage of parenthesis in VBA function calls?
  3. Autonumber value of last inserted row – MS Access / VBA
  4. Code to loop through all records in MS Access
  5. Bang Notation and Dot Notation in VBA and MS-Access
  6. How do I force VBA/Access to require variables to be defined?
  7. VBA: Difference in two ways of declaring a new object? (Trying to understand why my solution works)
  8. How to diagnose MS access crashes
  9. Equivalent cURL in VBA?
  10. How do I access the selected rows in Access?
  11. Run-time error ‘3061’. Too few parameters. Expected 1. (Access 2007)
  12. Prevent enabling of shift bypass – Access 2010
  13. VBA procedure to import csv file into access
  14. Transform multiple rows into columns with unique key
  15. how to show query results in a datasheet in the same form?
  16. Access VBA: Find max number in column and add 1
  17. MS Access prepared statements
  18. Create sequential ID value based on the year that a record is added
  19. UrlDownloadToFile in Access 2010 – Sub or Function not Defined
  20. Using a centralized VBA module in multiple Access databases
  21. How to execute a query in ms-access in VBA code?
  22. How to add a menu item to the default right click context menu
  23. VBA – Run Time Error 3271 using DAO object
  24. Trouble with InputBoxes
  25. Get Name of Current VBA Function
  26. How do I use parameters in VBA in the different contexts in Microsoft Access?
  27. Best way to test a MS Access application?
  28. Running Microsoft Access as a Scheduled Task
  29. How to use system username directly in MS Access query?
  30. MS Access trigger?

Leave a Comment