Time and If...

[MB]

I am getting the time, minute by minute from the US Naval
Observatory Master Clock Time. Occasionally, (five times today),
I fail to get a time and in these circumstances,
I would like to use the previous time obtained + 1 minute.

I thought I'd got the code correct, but that doesn't
seem to be the case. When I fail to get a time from the website
it shows as "00:00" and not the previous time +1 so that when I try
later in the procedure to refer to "RightTime", I get
"Run-time error 1004".

I would be grateful if someone could have a look at the code
and point me in the right direction.

Thanks

Martin

===========Start Code=============================

Dim NavyTimePos1 As Integer
Dim NavyTimePos2 As Date
Dim NavyTimeString As String
Dim RightTime As Date

NavyTimePos1 = 0
NavyTimePos2 = 0
NavyTimeString = ""
RightTime = 0

'Finds the string "UTC/GMT" and sets NavyTimePos1 to
'the position of the string within the webpage
NavyTimePos1 = InStr(IE_1.document.body.innerhtml, NavyTimeString)

'Counts back 9 from NavyTimePos1 (String position)
'and gets the string containing UTC time

NavyTimePos2 = Mid(IE_1.document.body.innerhtml, NavyTimePos1 - 9, 5)

NavyTimeRng.Offset(0, 1).Value = NavyTimePos2

'If time is not 0
If NavyTimeRng.Offset(0, 1).Value <> 0 Then

'Because UTC time is one hour behind present U.K. time
'adjust the UTC time

With NavyTimeRng.Offset(0, 1)
.Value = .Value + TimeValue("01:00:00")
.NumberFormat = "hh:mm"
End With

Else
'If NavyTimeRng.Offset(0, 1) = 0 Then

NavyTimeRng.Offset(0, 1) = NavyTimeRng.Offset(-4, 1) + TimeValue("00:01:00")
NavyTimeRng.Offset(0, 2).Value = "US Navy Time (Adjusted) and working as
expected"
End If

'Set correct time as "RightTime" to be used for all others
RightTime = NavyTimeRng.Offset(0, 1)

==============End Code===========================

 

[Rob van Gelder]

I'm confused as to why you even need to query the master clock. That's what
NTP clients do.

If you install an NTP client (I believe Windows XP comes with one as
standard), then you can use the system clock.

 

[MB]

The procedure runs all day, once a minute and is not only to be used on one
machine. It's not practical to specify that any other machine should have an
NTP client to run it.

Martin

 

[keepITcool]

I agree with Rob,

I wouldnt'be use excel as my master timekeeper..

That said, why dont you:

get the NavyTime
if not 0 compare it with the systemtime
if diff > 1 secs update the system clock

update the range from the systemclock


if navytime <> 0 then
if navytime - time > #00:00:01# then
time=navytime
endif
endif

rng.value=time



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

The procedure runs all day, once a minute and is not only to be used
on one machine. It's not practical to specify that any other machine
should have an NTP client to run it.

Martin

Rob van Gelder wrote in message

 

[keepITcool]

s/b if abs(navytime - time) > #00:00:01# then

keepITcool

 

[Rob van Gelder]

What you're trying to achieve by querying the time server every minute is a
very basic time synching system.

Note that your approach won't be all that accurate due to the overhead of
VBA, and pulling time via HTTP.

If you require that sort of time sensitive accuracy, you really should use
an NTP client.
Existing NTP clients will perform more accurate and more efficient time
synching than your approach will.

Additionally, think that you will have to keep maintaining your product with
bug fixes, support, etc...
Why bother with that hassle when NTP clients are already well supported, bug
fixed, and some good ones are FREE.

Check out http://www.ntp.org and
http://www.boulder.nist.gov/timefreq/general/softwarelist.htm


--
Rob van Gelder - http://www.vangelder.co.nz/excel

The procedure runs all day, once a minute and is not only to be used on one
machine. It's not practical to specify that any other machine should have an
NTP client to run it.

Martin

 

[MB]

I understand your comments, another reason why I am avoiding system time is
that over the course of a day, whilst this set of procedures are running, my
system clock loses between four and twelve minutes. Once the procedures have
finished, if I reboot, then my system clock returns to the normal time.
Thanks to Dave Peterson and Dana DeLouis, I have the options of VBA and VBS
for updating the system clock.

I have run your code and mine and the conclusion I have come to is that the
solution to my problem is probably in the area of "NavyTimePos1 " or
"NavyTimePos2", rather than the "If" portion of the code. I'll put specific
error traps in and see what comes back - difficult to test as I'm looking
for an anomaly that can occur 4/5 times in 12 hours. If you think I'm on the
wrong track completely, can you please say so.

Finally, sorry to go on, what is the difference between
TimeValue("00:01:00") and #00:01:00# and when and why should one version be
used rather than the other?

Thanks
Martin

keepITcool wrote in message ...

 

[Tom Ogilvy]

Timevalue is a function in VBA that would return the time for the string
supplied as an argument

#00:01:00# is a VBA time constant. I suspect using the constant would be
faster than using the function

For example,
vVal = 1
would be faster than

vVal = GetOne()
.. . .
End Sub

Function GetOne()
GetOne = 1
End function

 

[MB]

Thank you
Martin

Timevalue is a function in VBA that would return the time for the string
supplied as an argument

#00:01:00# is a VBA time constant. I suspect using the constant would be
faster than using the function

For example,
vVal = 1
would be faster than

vVal = GetOne()
. . .
End Sub

Function GetOne()
GetOne = 1
End function

--
Regards,
Tom Ogilvy

"MB" <[email protected]> wrote in message

<SNIP>

 

[David McRitchie]

I think the usual reason for computer clock to be off is from
a bad battery. (computer clock/CMOS batteries). What people
usually see is that the time is good as long as the computer is on.

Though you seem to notice the problem with the computer on
for some time. I don't know anything about overclocking did
someone change the clock.

A dead battery could prevent you from powering up, so I'd
certainly try to find the cause as well as solving your immediate
problems. Replacing a battery yourself is a cheap fix.

I assume you've fixed your immediate problem.

Why bother [with your own subroutine] with that hassle when
NTP clients are already well supported, bug fixed, ... FREE.

Check out http://www.ntp.org and
http://www.boulder.nist.gov/timefreq/general/softwarelist.htm

The procedure runs all day, once a minute

 

[Tom Ogilvy]

I have seen discussions here/KB article that supports the contention that
extensive tight processing can cause the system time to degrade slightly.

--
Regards,
Tom Ogilvy

I think the usual reason for computer clock to be off is from
a bad battery. (computer clock/CMOS batteries). What people
usually see is that the time is good as long as the computer is on.

Though you seem to notice the problem with the computer on
for some time. I don't know anything about overclocking did
someone change the clock.

A dead battery could prevent you from powering up, so I'd
certainly try to find the cause as well as solving your immediate
problems. Replacing a battery yourself is a cheap fix.

I assume you've fixed your immediate problem.

Why bother [with your own subroutine] with that hassle when
NTP clients are already well supported, bug fixed, ... FREE.

Check out http://www.ntp.org and
http://www.boulder.nist.gov/timefreq/general/softwarelist.htm

The procedure runs all day, once a minute