Microsoft Office Tutorials and References
In Depth Information
About Excel’s date bug
It is commonly known that Excel has a date bug: It incorrectly assumes that the year 1900 is a
leap year. Even though there was no February 29, 1900, Excel accepts the following formula and
displays the result as the 29th day of February, 1900:
VBA doesn’t have this date bug. The VBA equivalent of Excel’s DATE function is DateSerial .
The following expression (correctly) returns March 1, 1900:
Therefore, Excel’s date serial number system doesn’t correspond exactly to the VBA date serial
number system. These two systems return different values for dates between January 1, 1900,
and February 28, 1900.
Here are some examples of declaring variables and constants as Date data types:
Dim Today As Date
Dim StartTime As Date
Const FirstDay As Date = #1/1/2010#
Const Noon = #12:00:00#
Dates are always defined using month/day/year format, even if your system is set up to
display dates in a different format (for example, day/month/year).
If you use a message box to display a date, it’s displayed according to your system’s short date
format. Similarly, a time is displayed according to your system’s time format (either 12- or
24-hour). You can modify these system settings by using the Regional Settings option in the
Windows Control Panel.
An assignment statement is a VBA instruction that makes a mathematical evaluation and assigns
the result to a variable or an object. Excel’s Help system defines expression as “a combination of
keywords, operators, variables, and constants that yields a string, number, or object. An
expression can perform a calculation, manipulate characters, or test data.”
I couldn’t have said it better myself. Much of the work done in VBA involves developing (and
debugging) expressions. If you know how to create formulas in Excel, you’ll have no trouble
creating expressions in VBA. With a worksheet formula, Excel displays the result in a cell. The result of a
VBA expression, on the other hand, can be assigned to a variable or used as a property value.