Clock not working properly

[gurunath]

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.

   	 	 	 	   
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

[Paul_Hossler]

An Integer might be too small (32k+ max). Try making them Long and see

Dim Seconds As Long
Dim Minutes As Long
Dim CurrentRow As Long
Dim CurrentColumn As Long
Dim Initalised As Boolean

[gurunath]

Dear Mr. Paul,

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'!'!Sh eet1!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?