TIME reference - Excel 2003

[Firuath]

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

 

[SkipVought]

Hi,

I depends on your objective. You mentioned being advised to use the Format function. For WHAT? Do you want to DISPLAY a STRING value or do you need to preserve the underlying TIME VALUE in order that it can be used in Date/Time calculations? In the latter case, you ought NOT to use the Format Function.

You capture the time in HOURS as a STRING in RightNow -- not very granular. What do you use it for? It would not help me in the least to know that my procedure began sometime between 8 and 9 o'clock.

What "reference" are you moving up and down where?

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[Firuath]

The reference I'm moving is called TIME (in all caps), it's located in Tools->References in the VBE and I'm moving it up and down the Reference table at the same location.
I've gone over this problem with several other people on several other forums, all to no avail. I do however, have a post from one of those forums that explains exactly what I'm doing and what I've done thus far to try to fix it. Here it is, and hopefully this should answer all your quesetions :


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.

 

[Firuath]

I've corresponded with several other individuals who stated that the code I have listed compiles, and they were using office 2003. The only things I can think of now that could cause it to not work for me, but work for someone else would be either I have a reference checked or unchecked that they don't, or possibly our IT installed office 2003 incorrectly, we are missing a patch or update, or maybe our security settings don't get along with macros in the new version of office?

Any thoughts anyone? This is driving me crazy. I've been to 4 different message boards, several excel websites, a couple of VBA books, and spoken directly with several excel experts, but no one can seem to figure out the problem!