VBA Programming Series...25

Working with dates

Another data type you may find useful is Date. You can use a string variable to store dates, but then you can’t perform date calculations. Using the Date data type gives your routines greater flexibility. For example, you might need to calculate the number of days between two dates. This would be impos-sible if you used strings to hold your dates.

A variable defined as a Date can hold dates ranging from January 1, 0100, to December 31, 9999. That’s a span of nearly 10,000 years and more than enough for even the most aggressive financial forecast. You can also use the Date data type to work with time data (seeing as VBA lacks a time data type).

These examples declare variables and constants as a Date data type:

Dim Today As Date

Dim StartTime As Date

Const FirstDay As Date = #1/1/2010#

Const Noon = #12:00:00#

In VBA, place dates and times between two hash marks, as shown in the pre-ceding examples.

Date variables display dates according to your system’s short date format, and they display times according to your system’s time format (either 12- or 24-hour formatting). The Windows Registry stores these settings, and you can modify them via the Regional and Language Options dialog box in the Windows Control Panel. Therefore, the VBA-displayed date or time format may vary, depending on the settings for the system on which the application is running.

When writing VBA code, however, you must use one of the U.S. date formats (such as mm/dd/yyyy). So the following statement assigns a day in October to the MyDate variable (even if your system is set to use dd/mm/yyyy for dates):

MyDate = #10/11/2009#

When you display the variable (with the MsgBox function, for example), VBA shows MyDate using your system settings. So if your system uses the dd/mm/ yyyy date format, MyDate will be displayed as 11/10/2009.