Date/Time Values Stored in Variants
Variant variables can also contain Date/Time values. This is a floating point number-the integer part represents the days since 31-Dec-1899, and the decimal part represents the hours, minutes, and seconds expressed as a proportion of 24 hours. For example, 37786.75 represents 14-June-2003 at 18:00. The difference between 31-Dec-1899 and 14-June-2003 is 37,786 days, and 0.75 of 24 hours is 18 hours.
Adding or subtracting numbers adds or subtracts days. Adding decimals increases the time of day-for example, adding 1/24 (0.0416) adds one hour. A number of functions handle date and time.
Note that the interpretation of day and month is dependent on the Regional Options settings within the Windows Control Panel. If you set your date to mm/dd/yy in Regional Options, this will be the default interpretation of day and month.
Just as you can use IsNumeric to determine if there is a numeric value, you can use the IsDate function to determine if there is a date value.
temp = "01-Feb-2009" MsgBox IsDate(temp)
This will return True (nonzero).
Empty Value
A variant that has not had a variable assigned to it will have an empty value. You can test for this using the IsEmpty function.
MsgBox IsEmpty(MyTest)
This example will return True (nonzero) because MyTest has not been assigned a value.
Null Values
A variant can contain a special value of Null. The Null value is used to indicate unknown or missing data. Variables are not set to Null unless you write code to do this. If you do not use Null in your application, you do not have to worry about Null.
NOTE: Null is not 0. This is an easy but incorrect assumption to make when you're starting to learn VBA. The safest way to check for a Null value in your code is to use IsNull. Other methods, such as the statement Is Null, may not give correct results.
Sub TestNull() temp=Null Msgbox IsNull(temp) End Sub
The result here will be True (nonzero).
Other Data Types
Why use data types other than Variant? Because Variant may not use the best data type for the purpose. If you want to create concise fast code, you need other data types. For example, if you are doing lots of mathematical calculations on relatively small integer numbers, you can gain an enormous speed advantage by using the data type Integer instead of Variant. You can use Variant by default, but Variant will not necessarily assume it is an integer being used. It could assume it is a floating point number, in which case calculations will take far longer, although the same result will be produced.
Memory considerations should also be taken into account. Each double number takes up 8 bytes of memory, which may not seem like a lot. However, across a large array it can use a large amount of RAM, which would slow the process down. This will use up memory in the computer, which Windows could be using as virtual memory for its graphical display.