Stopwatch code

Question

I've found the following code which works really well. However, Id like buttons to stop/start & reset the stopwatch, could anyone help me with that?

Public stopMe As Boolean

Public resetMe As Boolean

Public myVal As Variant

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    If Target.Column = 1 Then

        If Target.Value = myVal And Target.Value <> "" Then

             'Changed

            Dim startTime, finishTime, totalTime, timeRow

            startTime = Timer

            stopMe = False

            resetMe = False

            myTime = Target.Offset(, 2).Value

            Target.Offset(, 1).Select

startMe:

            DoEvents

            timeRow = Target.Row

            finishTime = Timer

            totalTime = finishTime - startTime

            Target.Offset(, 1).Value = Format(myTime + totalTime, "0.0000") & " Seconds"

            If resetMe = True Then

                Target.Offset(, 1).Value = 0

                Target.Offset(, 2).Value = 0

                stopMe = True

            End If

            If Not stopMe = True Then

                Target.Offset(, 2).Value = totalTime

                GoTo startMe

            End If

            Cancel = True

            End

        Else

             'Not Changed

            stopMe = True

            Cancel = True

        End If

    End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    myVal = Target.Value

End Sub

Answer 1

I've found the following code which works really well.

I can't agree, it works... but well?

The basic is just this little sub in a regular module:

Sub MyTimerSub()

  'Tell Excel to execute this sub in one second again

  Application.OnTime Now + TimeSerial(0, 0, 1), "MyTimerSub"

End Sub

The comment says all, you can e.g. add a line

  Range("A1") = Range("A1") + 1

and you see "a counter running" in A1, but it never stops. Try that, don't worry, if you want to stop the execution, place a breakpoint on a line in the sub, wait until the code stops and click the reset button in the VBA menu.

To do that with code is also simple, have a look:

Option Explicit

Dim TimerEnabled As Boolean

Sub MyTimerSub()

  If Not TimerEnabled Then Exit Sub

  Range("A1") = Range("A1") + 1

  'Call this sub in one second again

  Application.OnTime Now + TimeSerial(0, 0, 1), "MyTimerSub"

End Sub

Sub StartTimer()

  TimerEnabled = True

  MyTimerSub

End Sub

Sub StopTimer()

  TimerEnabled = False

End Sub

Sub ResetTimer()

  TimerEnabled = False

  Range("A1") = 0

End Sub

But Excel is not designed for such gadgets, if you edit a cell, the code stops. If you try to work with Excel (copy cells, etc.) you can't do that properly. No way to prevent that.

It is funny to write and play around with such code, you can learn a lot of things, but using in a real application... never ever.

Andreas.

Answer 2

I wonder why I can edit cells, add charts, etc., but can't do a simple cell fill color...

Eric,

I've found an older sample, based on the code above, using CommandButtons and cells:

https://dl.dropboxusercontent.com/u/35239054/Zeitz%C3%A4hler%20in%20einer%20Zelle.xls

The counter stops during cell edit, etc. you can also format cells, and after that the counter continues and shows the correct timings. Same ways as your sample.

The issue within your sample is that you did not call DoEvents inside sub UpdateLabel.

However, my biggest problem with these things is that you lose the Intellisense function in VBA. I have an old, self-written developer AddIn for Excel and I think for a long time about how I could port this to Excel 2010.

On of the functions is a button that shows a light bulb. When the light is on EnableEvents = True otherwise False. Means the button should show the current state of EnableEvents and let you switch it on or off.

I do not think, you could do that within Excel without having any losses. Maybe possible with an external DLL or XLL? But from inside such a file, how to access the ribbon and change the icon?

Too much work just for a try. Any ideas are welcome.

Andreas.

Answer 3

Here is a simple example of timer code using ActiveX controls and a stopwatch class.  

https://onedrive.live.com/redir?resid=C94F206ED361B07A!201&authkey=!AP2S5rU_r78J3wI&ithint=file%2c.xlsm

HTH,

Eric

Andreas - with this code, I can edit cells but cannot do other things like format cells.  Yes, the code stops while you're editing, but the timing continues until the STOP button is pressed.  I wonder why I can edit cells, add charts, etc., but can't do a simple cell fill color...