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