Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time and If...
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=========================== |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time and If...
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. -- Rob van Gelder - http://www.vangelder.co.nz/excel "MB" wrote in message ... 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=========================== |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time and If...
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 ... 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. -- Rob van Gelder - http://www.vangelder.co.nz/excel |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time and If...
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 "MB" wrote: 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 ... 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. -- Rob van Gelder - http://www.vangelder.co.nz/excel |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time and If...
s/b if abs(navytime - time) #00:00:01# then
keepITcool |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time and If...
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...ftwarelist.htm -- Rob van Gelder - http://www.vangelder.co.nz/excel "MB" wrote in message ... 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 ... 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. -- Rob van Gelder - http://www.vangelder.co.nz/excel |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time and If...
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 ... s/b if abs(navytime - time) #00:00:01# then keepITcool |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time and If...
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" wrote in message ... 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 ... s/b if abs(navytime - time) #00:00:01# then keepITcool |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time and If...
Thank you
Martin Tom Ogilvy wrote in message ... 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" wrote in message <SNIP |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time and If...
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. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Rob van Gelder" wrote in 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...ftwarelist.htm "MB" wrote in message... The procedure runs all day, once a minute |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time and If...
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 "David McRitchie" wrote in message ... 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. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Rob van Gelder" wrote in 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...ftwarelist.htm "MB" wrote in message... The procedure runs all day, once a minute |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time calculations for Scheduled Time vs. Actual Time Worked | Excel Discussion (Misc queries) | |||
straight time, time and a half, and double time | Excel Discussion (Misc queries) | |||
Calculate Ending time using Start Time and Elapsed Time | Excel Worksheet Functions | |||
template or formula for start time -finish time -total hours ple | New Users to Excel | |||
Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions |