Visual Basic has a number of date manipulation functions. In this section we are going to look at them in some detail. It might seem like I am telling you something you already know, but I have seen too many supposedly good Visual Basic developers remain unaware of the range of tools that are in the box.
Date tools
Visual Basic provides a lot of properties and functions that support comparison and manipulation of dates. These properties and functions are all designed to work with the Visual Basic Date data type and should be used in preference to all other methods. The majority of these elements reside in the VBA library in a class called DateTime. You can see the details of the class in Figure 8-1.
Figure 8-1 The VBA.DateTime class as seen in the Visual Basic Object Browser
TIP
With all the conversion functions, you would do well to use IsDate to test your expression before you perform the conversion.
The Calendar property This property exposes the calendar system currently in use within your application. By default this is set to vbCalGreg, the Gregorian calendar in use throughout most of the western world. Currently the only alternative is vbCalHijri, the Hijri calendar.
The Now, Date, Date$, Time, and Time$ properties All these properties perform similar tasks. They retrieve or assign the system date or time. By far the most used is the read-only Now property, which returns the current system date and time as a Visual Basic Date that can be assigned directly to a Date data type variable without conversion.
The Date and Time properties can be used to assign or return just the date or time part of the current system date. When assigning, the Date property expects to be passed a date expression containing the date you want to set the system date to. Any time information is ignored. The date must be within the range shown in the table below. Dates outside this range will result in a run-time error (5 - Invalid Procedure Call Or Argument). The Date$ property returns and assigns dates from Strings, with the equivalent Date property using Variants.
| Range for VBA.DateTime.Date | Windows 9x | Windows NT |
| Minimum Date | January 1 1980 | January 1 1980 |
| Maximum Date | December 31 2099 | December 31 2099 |
The Time and Time$ properties perform a task similar to Date and Date$, exposing the system time.
The Timer property This property returns the number of seconds that have elapsed since midnight.
The DateDiff function This function performs a comparison of two dates. The value that is returned-the difference between the two dates-is reported in a time or date unit of the caller's choosing. An important point to note is that the answer will correctly reflect the fact that the year 2000 is a leap year. The following code displays the difference, in number of days (specified by the first argument), between the current system date and December 1, 2000.
' Display the number of days until Dec 1 2000.
MsgBox DateDiff("d", Now, #12/1/2000# _
, vbUseSystemDayOfWeek, vbUseSystem)
The fourth and fifth arguments are both optional, allowing you to specify the first day of the week and the first week of the year. Both will default to the system values if omitted.
The DateAdd function This function is used to modify a Visual Basic Date, with the value returned being the new Date following modification. Again this routine is fully aware of the leap year rules. The following line of code adds one month to the date January 31 2000 and returns the result February 29 2000, correctly calculating that February will have 29 days in the year 2000.
' Add one month to Jan 31 2000.
MsgBox DateAdd("m", 1, CDate("31 Jan 2000"))
The Year, Month, and Day functions The Format$ function is often abused when a programmer needs to get only part of the information held in a date. I still come across newly written code where Format$ has been used to do this.
' Getting the month of the current date, the old way iMonth = CInt(Format$(Date, "MM")) ' And how to do it the new, more efficient way iMonth = Month(Date)
Visual Basic provides the Year, Month, and Day functions to return these numeric values when passed a Date.
The Hour, Minute, and Second functions Not surprisingly, these functions perform a similar task to the Year, Month, and Day functions described above, except that they will return the numeric values representing the components of the time held in a Visual Basic Date.
The DatePart function This function returns the part of a passed date that you request in the unit of your choice. The above Year, Month, Day, Hour, Minute, and Second functions can perform the majority of the tasks that DatePart can, but the DatePart function does give you more flexibility, as demonstrated in the following code:
' Get the quarter of the current date.
MsgBox DatePart("q", Now, vbUseSystemDayOfWeek, vbUseSystem)
The third and fourth arguments are both optional, allowing you to specify the first day of the week and the first week of the year. Both will default to the system values if omitted.
The Weekday function This function will return the day of the week of the Date passed in as the first argument. The second optional argument allows you to specify the first day of the week.
' Get the current day of the week. MsgBox Weekday(Now, vbUseSystemDayOfWeek)
The DateValue and TimeValue functions These two functions perform conversions from a String date expression to a Date data type; in this case the conversion will be of only the date for DateValue and the time for TimeValue. These functions are useful if you want to separate the two parts of a date for separate storage.
One point to note with these two functions is that you can get a Type Mismatch error if any part of the expression you are converting is not valid, even the part you are not interested in. Executing the code below will result in this error, even though the time part of the expression is valid.
' Try this; it causes a Type Mismatch error!
MsgBox TimeValue("29 02 1900 12:15")
The DateSerial and TimeSerial functions DateSerial and TimeSerial are less flexible than DateValue and TimeValue, requiring three numeric parameters to define the date or time you want to convert. The three parameters of the DateSerial function are the year, month, and day, in that order. TimeSerial expects hours, minutes, and seconds.
' Assign April 12 1998 to the date. dteMyDate = DateSerial(1998, 4, 12) ' Alternatively, assign the time 12:00:00. dteMyDate = TimeSerial(12, 00, 00)
Both these functions have an interesting ability to accept values outside the normal range for each time period (excluding years). For instance, if you pass the year 1998 and the month 14 to the DateSerial function, it will actually return a date in the second month of 1999, having added the 14 months to 1998. The following line of code illustrates this. (Your output might look different depending on your system settings, but the date will be the same.)
Debug.Print "The Date is " & Format$( _
DateSerial (1998, 2, 29), "Long Date")
The Date is 01 March 1998
In this instance, DateSerial has correctly worked out that there is no February 29 in 1998, so it has rolled the month over to March for the extra day. We can use this ability to write a function that tells us whether any year is a leap year.
Public Function IsLeapYear(ByVal inYear As Integer) As Boolean
IsLeapYear = (29 = Day(DateSerial(inYear, 2, 29)))
End Function
