PDA

View Full Version : Clock not working properly



gurunath
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
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
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

gurunath
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?