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