Page 240 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

Now is not the only date or time function available to you in Excel VBA. Table 9-5 lists the
functions available to you and describes them.

Table 9-5. Useful Functions for Finding All or Part of a Date or Time
Function Description
Date Returns the current date.
Time Returns the current time.
Now Returns the current date and time.
Timer Returns the number of seconds since midnight.
DateValue Given a character string, such as "August 2, 2004",
returns a date (for example, 8/2/2004).
TimeValue Given a character string, such as "19:30", returns a
time (for example, 7:30:00 P.M.)
DateSerial (year, month, day) Given a year, month, and day (for example, DateSe­
rial(2004, 8, 2) returns the date (8/2/2004).
TimeSerial (hour, minute, second) Given an hour, minute, and second, (for example,
TimeSerial(19, 30, 24), returns the time (7:30:24 PM).
Hour, Minute, Second Given a time, returns the hour, minute, or second com­
Chapter 9
ponent of that time.
Year, Month, Day Given a date, returns the year, month, or day compo­
nent of that date.
Weekday Given a date, returns the weekday (Sunday = 1, Mon­
day = 2, and so on) of that date.
MonthName Given an integer, returns the month corresponding to
that integer (for example, 8 returns August).

With the exception of the Weekday and MonthName functions, writing the result of any of the
mentioned date and time functions to a worksheet cell means that Excel will format the cell
with its default Date format.
There are a number of other date-related and time-related functions that you can use to per-
form calculations in your Excel VBA code. The two most useful functions are DateAdd and
DateDiff. DateAdd, as the name implies, lets you add a time period to a date or time. Yes, you
can add three days to a date with simple addition, as in the following procedure:

Public Sub AddDays()

Dim datFirst, datSecond As Date
datFirst = #1/9/2004#
datSecond = datFirst + 3
MsgBox ("The new date is: " & datSecond & ".")
End Sub

214
Part 4: Advanced VBA