ExcelBanter - TIME reference

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - TIME reference - Excel 2003 (https://www.excelbanter.com/excel-programming/346062-time-reference-excel-2003-a.html)

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??

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??

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

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

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??

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

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

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

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