Displaying the date and time

If you understand the serial number system that Excel uses to store dates and times, you won't have any problems using dates and times in your VBA procedures.

The DateAndTime procedure displays a message box with the current date and time, as depicted in Figure 11-14 . This example also displays a personalized message in the message box title bar.

Figure 11-14: A message box displaying the date and time.

The procedure uses the Date function as an argument for the Format function. The result is a string with a nicely formatted date. I used the same technique to get a nicely formatted time.

Sub DateAndTime()

Dim TheDate As String, TheTime As String Dim Greeting As String

Dim FullName As String, FirstName As String Dim SpaceInName As Long

TheDate = Format(Date, "Long Date") TheTime = Format(Time, "Medium Time")

' Determine greeting based on time Select Case Time

Case Is < TimeValue("12:00"): Greeting = "Good Morning, " Case Is >= TimeValue("17:00"): Greeting = "Good Evening, " Case Else: Greeting = "Good Afternoon, " End Select

' Append user's first name to greeting FullName = Application.UserName SpaceInName = InStr(1, FullName, " ", 1)

' Handle situation when name has no space

If SpaceInName = 0 Then SpaceInName = Len(FullName) FirstName = Left(FullName, SpaceInName) Greeting = Greeting & FirstName

' Show the message

MsgBox TheDate & vbCrLf & vbCrLf & "It's " & TheTime, vbOKOnly, Greeting End Sub

In the preceding example, I used named formats (Long Date and Medium Time ) to ensure that the macro will work properly regardless of the user's international settings. You can, however, use other formats. For example, to display the date in mm/dd/yy format, you can use a statement like the following:

TheDate = Format(Date, "mm/dd/yy")

I used a Select Case construct to base the greeting displayed in the message box's title bar on the time of day. VBA time values work just as they do in Excel. If the time is less than .5 (noon), it's morning. If it's greater than .7083 (5 p.m.), it's evening. Otherwise, it's afternoon. I took the easy way out and used VBA's TimeValue function, which returns a time value from a string.

The next series of statements determines the user's first name, as recorded in the General tab in Excel's Options dialog box. I used VBA's InStr function to locate the first space in the user's name. When I first wrote this procedure, I didn't consider a username that has no space. So when I ran this procedure on a machine with a username of Nobody, the code failed - which goes to show you that I can't think of everything, and even the simplest procedures can run aground. (By the way, if the user's name is left blank, Excel always substitutes the name User. ) The solution to this problem was to use the length of the full name for the SpaceInName variable so that the Left function extracts the full name.

The MsgBox function concatenates the date and time but uses the built-in vbCrLf constant to insert a line break between them. vbOKOnly is a predefined constant that returns 0 , causing the message box to appear with only an OK button. The final argument is the Greeting , constructed earlier in the procedure. CD-ROM

The DateAndTime procedure is available on the companion CD-ROM in a file named ® date and time.xlsm .

0 0

Post a comment