Microsoft Office Tutorials and References
In Depth Information
VBA Techniques
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”)
