The time function in Excel 2003 - My code no longer works!!

[xFiruath]

(as a side note before beginning my actual question, I wanted to bring to someone's attention that the "search" function on these message boards appears to be broken. I searched for the string "time" and it returned no matches, even though a thread was clearly visible on that page with the word "time" in it)

I created a Macro in Excel 2000 that gathers some info from a web page, then posts it into an e-mail. Our IT just recently upgraded our Office Suite to the 2003 version, and suddenly, my macro no longer functions! Part of the macro retrieves the system time using the "time" function, and then breaks it down into standard (non-military) time for posting in the e-mail. I checked the help files in Excel 2003, and they still show full support for the time function, and I've been unable to locate anything on MSDN indicating that this function should no longer be working as it had before. Anyone have any idea why this isn't working? The exact error message I reiceve is this : "COMPILE ERROR! Can't find project or library", and the debugger highlights the "time" function in the 12th line of code below.

Any help would be greatly appreciated!

The complete code for reference is below :

Private Sub CommandButton1_Click()
Dim MyTime As Date
Dim RightNow As Integer
Dim A_Or_P As String
Dim ThirdLevel As Single
Dim SchedLevel As Single
Dim CscLevel As Single
Dim NotAvail As String

' Obtain only the base hour, instead of exact time with minutes and seconds
MyTime = Time
RightNow = Hour(TheTime)

' Check to see if it is AM or PM
If RightNow < 12 Then A_Or_P = "AM" Else A_Or_P = "PM"

' Set time to 12 hour instead of 24 hour clock
Select Case RightNow
Case 13: RightNow = 1
Case 14: RightNow = 2
Case 15: RightNow = 3
Case 16: RightNow = 4
Case 17: RightNow = 5
Case 18: RightNow = 6
Case 19: RightNow = 7
Case 20: RightNow = 8
Case 21: RightNow = 9
Case 22: RightNow = 10
End Select

Range("L40").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

ThirdLevel = Range("N42").Value
SchedLevel = Range("N43").Value

If Not IsNumeric(Range("N47")) Then
CscLevel = 0
Else: CscLevel = Range("N47").Value
End If

ThirdLevel = Round(ThirdLevel, 1)
SchedLevel = Round(SchedLevel, 1)
' If CSC has no service level, input string "N/A"
If CscLevel <> 0 Then CscLevel = Round(CscLevel, 1)

Range("L40:U426").Clear

Select Case CscLevel
Case Is > 0:
ESubject = "Best Buy Service Level @ " & RightNow & ":00 " & A_Or_P & " MST"
SendTo = "e-mail addresses"
CCTo = "e-mail addresses"
Ebody = "Scheduling - " & ThirdLevel & "%" & _
vbCr & vbCr & "3rd Party - " & SchedLevel & "%" & vbCr & vbCr & _
"CSC - " & CscLevel & "%"

Set App = CreateObject("Outlook.Application")
Set Itm = App.CreateItem(0)
With Itm
.Subject = ESubject
.to = SendTo
.CC = CCTo
.body = Ebody
.Display
End With

Set App = Nothing
Set Itm = Nothing

Case 0:
ESubject = "Best Buy Service Level @ " & RightNow & ":00 " & A_Or_P & " MST"
SendTo = "E-mail addresses"
CCTo = "E-mail addresse"
Ebody = "Scheduling - " & ThirdLevel & "%" & _
vbCr & vbCr & "3rd Party - " & SchedLevel & "%" & vbCr & vbCr & _
"CSC - N/A"

Set App = CreateObject("Outlook.Application")
Set Itm = App.CreateItem(0)
With Itm
.Subject = ESubject
.to = SendTo
.CC = CCTo
.body = Ebody
.Display
End With

Set App = Nothing
Set Itm = Nothing

End Select


End Sub

 

[anthonya2369]

try using the NOW() function. NOW() returns the current date and time.

You can set it something like this:

dim RightNow as date

RightNow = format(NOW(),"Hh:Nn:Ss")

This should return the TIME portion of the NOW() function

 

[xFiruath]

Thanks!!

This works perfectly! Thank you very much! I didn't realize you could format the Now function at all (which is why I had to set up that whole switch case to change the Time function).