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

xFiruath

New Member
Joined
Nov 11, 2005
Messages
4
(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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

anthonya2369

Active Member
Joined
Mar 10, 2005
Messages
321
try using the NOW() function. NOW() returns the current date and time.

You can set it something like this:

Code:
dim RightNow as date

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

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

xFiruath

New Member
Joined
Nov 11, 2005
Messages
4
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,477
Members
414,070
Latest member
DuncanLucas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top