| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
TIME reference - Excel 2003
Greetings Everybody!
At another Excel forum I had posted a thread regarding a problem I had using the Time() function in Excel 2003 (our office just recently upgraded, which is causing me to have to re-write all of my macros...grr). I was advised to change the code to use the Format function in conjunction with the now() function instead, which worked, for a little while. Now it's not working again, and the reason has something to do with the TIME reference. The first time I run the macro after opening the spreadsheet, it won't work, and I get an error that highlights the following code : RightNow = Format(Now(), "Hh") (RightNow is a variable declared at the beginning of the macro). If I move the TIME reference up or down, any amount of spaces, on the references table, and then rerun the macro, it works PERFECTLY. No errors, no problems, nothing. This has me perplexed, because if I close the file down, and open in again, the SAME thing happens. Doesn't work the first time, then if I move the TIME reference at all, in any direction, it works just fine. How do I fix this so it works all the time, without having to move the reference every time I want to run the macro?? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
TIME reference - Excel 2003
What do you mean by the "time reference"? 'Time' is simply a VBA
function that returns the time of day. (our office just recently upgraded, which is causing me to have to re-write all of my macros...grr). You shouldn't have had to rewrite anything. Code from previous versions works fine in later versions. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "xFiruath" <u15901@uwe wrote in message news:5799f77cbf165@uwe... Greetings Everybody! At another Excel forum I had posted a thread regarding a problem I had using the Time() function in Excel 2003 (our office just recently upgraded, which is causing me to have to re-write all of my macros...grr). I was advised to change the code to use the Format function in conjunction with the now() function instead, which worked, for a little while. Now it's not working again, and the reason has something to do with the TIME reference. The first time I run the macro after opening the spreadsheet, it won't work, and I get an error that highlights the following code : RightNow = Format(Now(), "Hh") (RightNow is a variable declared at the beginning of the macro). If I move the TIME reference up or down, any amount of spaces, on the references table, and then rerun the macro, it works PERFECTLY. No errors, no problems, nothing. This has me perplexed, because if I close the file down, and open in again, the SAME thing happens. Doesn't work the first time, then if I move the TIME reference at all, in any direction, it works just fine. How do I fix this so it works all the time, without having to move the reference every time I want to run the macro?? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
TIME reference - Excel 2003
If you go to Tools-References in the VBE you will find a reference titled
"TIME". -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200511/1 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
TIME reference - Excel 2003
You shouldn't have had to rewrite anything. Code from previous
versions works fine in later versions. Well, the code worked just fine in Excel 2000. After we upgraded to Excel 2003, it no longer works. And now, I'm having to rewrite the code. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200511/1 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
TIME reference - Excel 2003
What are you using the TIME reference functions for? They are not
basic time manipulation function. (TIME = Timed Interactive Multimedia Extensions). My guess is that you don't want the TIME reference at all. When VBA attempts to find a data type or method, it searches the libraries listed in the references, in the order that they appear in the dialog. As soon as it finds a matching names, it uses that reference. That may not be the item you intend. If you are using TIME objects, prefix them with the TIME library extension. So instead of Dim X As IServiceProvider use Dim X As TIME.IServiceProvider -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "xFiruath" <u15901@uwe wrote in message news:5799f77cbf165@uwe... Greetings Everybody! At another Excel forum I had posted a thread regarding a problem I had using the Time() function in Excel 2003 (our office just recently upgraded, which is causing me to have to re-write all of my macros...grr). I was advised to change the code to use the Format function in conjunction with the now() function instead, which worked, for a little while. Now it's not working again, and the reason has something to do with the TIME reference. The first time I run the macro after opening the spreadsheet, it won't work, and I get an error that highlights the following code : RightNow = Format(Now(), "Hh") (RightNow is a variable declared at the beginning of the macro). If I move the TIME reference up or down, any amount of spaces, on the references table, and then rerun the macro, it works PERFECTLY. No errors, no problems, nothing. This has me perplexed, because if I close the file down, and open in again, the SAME thing happens. Doesn't work the first time, then if I move the TIME reference at all, in any direction, it works just fine. How do I fix this so it works all the time, without having to move the reference every time I want to run the macro?? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
TIME reference - Excel 2003
What specifically doesn't work? I've written tens of thousands of
lines of code that runs in any version of Excel. You shouldn't need to rewrite anything. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "xFiruath via OfficeKB.com" <u15901@uwe wrote in message news:57a6f9e3cc608@uwe... You shouldn't have had to rewrite anything. Code from previous versions works fine in later versions. Well, the code worked just fine in Excel 2000. After we upgraded to Excel 2003, it no longer works. And now, I'm having to rewrite the code. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200511/1 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
TIME reference - Excel 2003
Ok, here's exactly what I'm doing, and the solutions I've already tried:
I have a macro that retrieves info from a web page, then pastes it into an e- mail with all the e-mail addresses and the current time already in all the relevant fields. When we had Excel 2000 on the computers using this macro, the part of the code that retrieved the hour looked like this : Dim MyTime As Date Dim RightNow As Integer MyTime = TIME RightNow = Hour(MyTime) When I ran the macro after we upgraded to Excel 2003, I got an error stating "COMPILE ERROR! Can't find project or library". I thought this might have something to do with references, so I added the TIME reference. It still didn't work. At another message board, someone suggested changing my code to look like this : RightNow = Format(Now(), "Hh") Instead of using the Time() function. That didn't work either. I thought maybe it was calculating the time at the wrong moment or something, so I tried fiddling with where the reference was on the references list. That is when I discovered that if I moved the TIME reference (up or down, doesn't matter) it would work. At yet another message board, someone suggest that I possibly have missing references. I checked, and there are none flagged as missing. It was suggest that I change the code to look like this instead: Dim RightNow As Date RightNow = Time Selection = Format(RightNow, "hh") This doesn't work either. I get the same error message "COMPILE ERROR! Can't find project or library" I have checked the "disabled items" list (just in case), I made sure all the computers have their drives mapped exactly as they had before (they do), and they all have "calculations" set to automatic. I can't think of anything else that would need to be troubleshot. The single factor in all this? Excel 2003. ALL of these methods of solving the same problems work on any computer using Excel 2000. NONE of them work on the computers using 2003. If anyone has any idea what the problem is, I would be incredibly greatfull for a solution. -- Message posted via http://www.officekb.com |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
TIME reference - Excel 2003
Oh, forgot to add, the code that was highlighted when I got the error message
was either Time or Format (depending on which version of the code I was using) -- Message posted via http://www.officekb.com |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
TIME reference - Excel 2003
In case it might help, I'm including the full code below. The code listed is
the version using Format() instead of Time(). I've had several people inform me now that the TIME reference really has nothing to do with system time or date calculations, but if this is the case, why does the macro only work in the 2003 version of excel if both a) TIME is included and b) the program runs once, errors out, and then works once the TIME reference's priority is moved in any way on the reference list??? What is it about this reference that makes it work only if it's been moved? Here is the full code (it's a button that collects data and then puts the date into a pre-formated e-mail) : Code:
Private Sub CommandButton1_Click()
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
RightNow = Format(Now(), "Hh")
' 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 here"
CCTo = "e-mail address here"
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 here"
CCTo = "e-mail addresses here"
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
Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200511/1 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Is there a quick reference guide for excel 2003 -2007 | Excel Discussion (Misc queries) | |||
| How to combine two list on one reference line Excel 2003 | Excel Discussion (Misc queries) | |||
| Excel 2003 - Copy Cell Reference | Excel Worksheet Functions | |||
| Missing Reference in Excel 2003 | Excel Discussion (Misc queries) | |||
| using a reference in vlookup with excel 2003 | Excel Worksheet Functions | |||
![xFiruath[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
