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”)
 
Search JabSto ::




Custom Search