View Full Version : Clock not working properly

09-15-2016, 07:57 PM
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
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

09-16-2016, 05:46 AM
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

09-17-2016, 06:17 AM
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?