Supplying Date Values

[sukishan]

Supplying Date Values
Like some weird date-munching omnivore, VBA's expression engine can "eat" dates in any of several formats. As long as you enclose date literals within number signs (#) and format the literal in some reasonable, unambiguous way, VBA should be able to understand what you mean.

VBA understands any of the following formats (if you're running a VBA host in the United States, that is):

#January 1, 1998#
#Jan 1 1998#
#1-Jan-98#
#1 Jan 1998#
#1 1 98#VBA uses your Windows international settings to determine how to parse the value you've entered. This does, of course, cause trouble with dates entered with nothing differentiating days and months. (How is VBA supposed to know, unless you tell it otherwise, that #5/1/98# represents May 1 and not January 5?) To be completely unambiguous, especially in cases in which your application must run in various localized VBA hosts, you might consider abandoning date literals in code altogether and using the DateSerial function instead. This function, discussed in the section "Putting the Pieces Together" later in this chapter, takes three distinct values representing the year, month, and day portions of a date and returns a date value representing the selected date. Using this mechanism, you'll never have any issues with localized versions of your code parsing date literals differently than you'd expected.

When converting from other datatypes into dates, VBA stores the portion to the left of the decimal point (the whole number part) as the date and the portion to the right of the decimal point as the time. For example, if you were to write code like this:

Dim dbl As Double
Dim dtm As Date
dbl = 3005 / 12.6
dtm = dbl
Debug.Print dbl
Debug.Print dtmthe output would be

238.492063492063
8/25/1900 11:48:34 AM