Clock not working

[gurunathblessings]

<style type="text/css">p { margin-bottom: 0.1in; line-height: 120%; }</style> Currently I am using the following VBA programme.


Initially this programme will work fine for the first 1 or 2 hrs. The clock will stop at the 10th seconds and the 11th seconds will continue in the following row. After 1 or 2 hrs, the clock will stop in either 11th or 12th seconds. The error will continue and multiply and may end up 1 or 2hrs different from what I require. I require the clock to stop at every 10th second exactly everytime without any interruption or error for 32hrs. I notice that this programme is very volatile and sometimes simply stop or even shutdown the excel and the entire computer automatically while in process. I am using Microsoft Excel 2007/2010/2013 XML. I hope someone will help me to solve this problem. I need to solve this problem very quickly. Thanking in advance.

   	 	 	 	<style type="text/css">p { margin-bottom: 0.1in; line-height: 120%; }</style>   
Dim Seconds As Integer
 Dim Minutes As Integer
 Dim CurrentRow As Integer
 Dim CurrentColumn As Integer
 Dim Initalised As Boolean
 

 

 Private Sub Recalc()
     If (CurrentRow <> 0 And CurrentColumn <> 0) Then
         Cells(CurrentRow, CurrentColumn).Value = Format(Now, "dd/mm/yyyy")
         Cells(CurrentRow, CurrentColumn + 1).Value = Format(Time, "hh:mm:ss AM/PM")
          
         Seconds = Seconds + 1
          
         If (Seconds = 10) Then
             Minutes = Minutes + 1
             CurrentRow = CurrentRow + 1
             Seconds = 0
         End If
          
         ' Stop repeating after 32 hours (1920 minutes)
         If (Minutes < 1920) Then
             Call SetTime
         Else
             Initalised = False
         End If
     End If
 End Sub
 

 

 Sub SetTime()
     If (Not Initalised) Then
         Initalised = True
          
         ' Initialise variables.
         Seconds = 0
         Minutes = 0
         CurrentRow = ActiveCell.Row
         CurrentColumn = ActiveCell.Column
     End If
 

 

     SchedRecalc = Now + TimeValue("00:00:01")
     Application.OnTime SchedRecalc, "Recalc"
 End Sub

 

[alansidman]

crosspost: Clock not working properly

 

[Rick Rothstein]

   	 	 	 	<style type="text/css">p { margin-bottom: 0.1in; line-height: 120%; }</style>   
Dim Seconds As Integer
 Dim Minutes As Integer
 Dim CurrentRow As Integer
 Dim CurrentColumn As Integer
 Dim Initalised As Boolean
 [/QUOTE]
What happens if you change the Integer declarations to Long instead?

 

[Macropod]

gurunathblessings: Please read our policy on Cross-Posting in rule #13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[joeu2004]

Initially this programme will work fine for the first 1 or 2 hrs. The clock will stop at the 10th seconds and the 11th seconds will continue in the following row. After 1 or 2 hrs, the clock will stop in either 11th or 12th seconds.

You are probably seeing the effects of "clock skew". I will explain below. But first....


1. As much as I agree with Rick that type Long is better than type Integer in general, I do not believe type Integer causes any problem here as long the initial ActiveCell.Row is less than 21247.

Your intent is to increment currentRow every 10 seconds for 32 hours. That is 11520 rows (32*3600/10). So if the initial row is 21247, currentRow should get incremented to 32767 (21247+11520) before stopping the Recalc process (setting Initialised to False).

Nevertheless, I reiterate: it is better to always use type Long instead of type Integer, arguably except when building huge arrays and you are sure the values are between -32768 and 32767. But even then, the difference of 4 Mbytes or so makes little difference in modern computers that contain multi-Gbytes of memory.


2. Your code will run for only 5 hours 20 seconds, not 32 hours.

That is because you increment Minutes every 10s, not every 60 seconds. So 1920*10/3600 = 5 1/3 hours


3. The variable SchedRecalc is not declared.

That might not be an error, since the default type is Variant. However, it will be a local variable in the SetTime procedure. That may or may not be an error, depending how you might try to use SchedRecalc elsewhere, not included in your posting.

Nevertheless, it is prudent to get in the habit of putting Option Explicit at the top of each module. In fact, there is a VBA option that will cause that to happen automatically.


4. I have no intention of running your code for even 5 hours, much less 32 hours. But to demonstrate that your code does indeed write a new row every 10 seconds, as intended, we can make the following changes (in red) so that the code runs in much less time.

Dim Seconds As Integer
Dim Minutes As Integer
Dim CurrentRow As Integer
Dim CurrentColumn As Integer
Dim Initalised As Boolean
Dim SchedRecalc As Date
Dim myNow As Date

Private Sub Recalc()
    myNow = myNow + TimeSerial(0, 0, 1)
    If (CurrentRow <> 0 And CurrentColumn <> 0) Then
        Cells(CurrentRow, CurrentColumn).Value = Format(myNow, "dd/mm/yyyy")
        Cells(CurrentRow, CurrentColumn + 1).Value = Format(myNow, "hh:mm:ss")
         
        Seconds = Seconds + 1
        If (Seconds = 10) Then
            Minutes = Minutes + 1
            CurrentRow = CurrentRow + 1
            Seconds = 0
            Application.ScreenUpdating = True
            DoEvents
            Application.ScreenUpdating = False
        End If
         
        ' Stop repeating after 32 hours (1920 minutes)
        If (Minutes < 1920) Then
            Call SetTime
        Else
            Initalised = False
            MsgBox "stopped"
        End If
    End If
End Sub

Sub SetTime()
    If (Not Initalised) Then
        Initalised = True
         
        ' Initialise variables.
        myNow = Now
        Seconds = 0
        Minutes = 0
        CurrentRow = ActiveCell.Row
        CurrentColumn = ActiveCell.Column
        Application.ScreenUpdating = False
    End If

    SchedRecalc = Now
    Application.OnTime SchedRecalc, "Recalc"
End Sub

By setting OnTime to Now, the next Recalc event occurs immediately after executing current Recalc event (or initial SetTime).

Note that the code generates only 1920 rows before stopping, as explained in #2 above.

But more to the point: Also note that the "time" differences are consistent 10 seconds apart.


5. Clock skew is caused by the fact that VBA Now returns current time rounded down to the second.

But the system clock is normally updated every 15.625 milliseconds. So the current time might be 0.984375 seconds or more later than the time returned by Now.

(And some applications change the system clock update interval; for example, the applications that run in some webpages, although that might depend on the browser, as well.)

Moreover, there might be a delay between the scheduled event time and when Recalc is actually called. Such delays depend on how busy the system might be running other applications (many of which are scheduled at start-up automatically) and processing system interrupts (like network traffic).

So when you calculate Now+TimeSerial(0,0,1) -- better to use than TimeValue, IMHO -- Now might return a time that is 1 second (or more, albeit unlikely) than the previously-scheduled event time.

Such clock skewing should be "random" (not following a pattern). It can happen at any time, not just "after 1 or 2 hours". And subsequent scheduled events might occur at regular 10-second intervals after that.

Arguably, that differs from the pattern that you describe. But in my experience, such descriptions are not precise.

Partial work-around: instead of scheduling OnTime at Now+TimeSerial(0,0,1), schedule at initEvent + n*TimeSerial(0,0,1), where initEvent is the first OnTime event, and n is the number of previous events, incremented in Recalc.

That might cause multiple Recalc events to occur in quick succession, perhaps in the same second, if the clock skew is significant. But at least you will get the expected number of Recalc events, if that is important to you.

-----

If you do not believe that the problem can be explained by clock skew, please provide an Excel file that contains the correct VBA code as well as the Excel data that reflects a complete execution over some number of hours.

 

[gurunathblessings]

Dear Mr. Rick Rothstein

I appreciate your help. I tried for 24hrs and it worked fine.
Many Thanks!
However, I have a new problem. I run the above stated macro in workbook1. In the workbook1 I keyed in a specific required time and in the adjacent cell I keyed in IF(B2=C2,1,""). Then I cut and paste(with Link) to another workbook2 where no macros are running. The new cell in the workbook2 shows as Excel.SheetMacroEnabled.12|'C:\Users\.....\Documents\RANDBETWEEN.xlsm'!'!Sheet1!R338C4'. In the adjacent cell, in workbook2, if I key in a formula, IF(B2=A1,RANDBETWEEN(0,5),""), I get an answer but it did not stop after the first number appeared. Instead of using IF(B2=C2,1,"") in the workbook1 that is running the macro I change it to VALUE(B2). I still face the same problem in workbook2. What can I do about it? Is there a way to stop the macro from running in workbook2 that is being adopted from workbook1?

 

[gurunathblessings]

Hi Joeu2004,

I appreciate your help.
Many Thanks.
You have provided very valuable information sacrificing some of your precious time.
Again, I really appreciate your help. So far I have tried Mr. Rick's suggestion and the clock worked very fine for 24hrs. I am yet to try suggestion to learn more.
However, I have a new problem. I run the above stated macro in workbook1 after the change. In the workbook1 I keyed in a specific required time and in the adjacent cell I keyed in IF(B2=C2,1,""). Then I cut and paste(with Link) to another workbook2 where no macros are running. The new cell in the workbook2 shows as Excel.SheetMacroEnabled.12|'C:\Users\.....\Documents\RANDBETWEEN.xlsm'!'!Sheet1!R338C4'. In the adjacent cell, in workbook2, if I key in a formula, IF(B2=A1,RANDBETWEEN(0,5),""), I get an answer but it did not stop after the first number appeared. Instead of using IF(B2=C2,1,"") in the workbook1 that is running the macro I change it to VALUE(B2). I still face the same problem in workbook2. What can I do about it? Is there a way to stop the macro from running in workbook2 that is being adopted from workbook1?