Working with dates

You can use a string variable to store a date, but if you do, it's not a real date (meaning you can't perform date calculations with it). Using the Date data type is a better way to work with dates.

A variable defined as a date uses 8 bytes of storage and can hold dates ranging from January 1, 0100 to December 31, 9999. That's a span of nearly 10,000 years - more than enough for even the most aggressive financial forecast! The Date data type is also useful for storing time-related data. In VBA, you specify dates and times by enclosing them between two hash marks (#).

Note The range of dates that VBA can handle is much larger than Excel's own date range, which begins with January 1, 1900. Therefore, be careful that you don't attempt to use a date in a worksheet that is outside of Excel's acceptable date range.

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/2007# Const Noon = #12:00:00#

Caution 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).

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 does not 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 does not 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._

If you use a message box to display a date, it is 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.

CD- The companion CD-ROM includes a workbook that contains several VBA worksheet ROM functions that enable you to work with dates prior to January 1, 1900. The file is named %

extended date functions . xlsm. You'll also find a Word document (® extended date functions help. docx) that describes the functions..

4 prev next

0 0

Post a comment