I'm trying to make a VBA code that allows me to calculate the time ellapsed between two clicks/buttons.

Here is the scenario I'm looking for :

1. I select an empty cell in the column Time
2. I click on the START button (to start the counter)
3. I click on the STOP button (to stop the counter)
4. The ellapsed time (in the format "hh:mm:dd") has to be put in the selected cell in step 1

Expected output :

I started making a code for the START button but I don't know how the one's for the STOP button.

Sub CalculateTime()

Dim startTime As Double
Dim TimeElapsed As String

  startTime = Timer

  TimeElapsed = Format((Timer - startTime) / 86400, "hh:mm:ss")
  
  ActiveCell.Value = TimeElapsed

End Sub

Do you have any suggestions, please ?
Any help will be appreciated !

EDIT :

Here is the code of my two subs :

Sub StartButton()

Call CalculateTime(True)

End Sub

'**************************************

Sub StopButton()

ActiveCell.Value = CalculateTime(False)

End Sub

CodePudding user response：

There are various ways to tackle this. One way is to use a global variable (startTime) which is set in your Start button click event. Your Stop button then executes your method and (sans the startTime = Time line obviously).

But I prefer to avoid global variables, so therefore you could rewrite your Sub as a Function and call it twice. Once from the Start button: Call CalculateTime(True). And once from the Stop button: ActiveCell.Value = CalculateTime(False)

Function CalculateTime(ByVal bolStart As Boolean) As String

    ' Declaring a local variable as Static preserves their value between calls
    ' of the method.
    Static startTime As Double

    If bolStart = True Then
    ' This is the start of the measurement, we simply store the time
       startTime = Timer
    Else
    ' This is the end of the measurement, so we calculate the time elapsed
    ' and return it as a string.
        CalculateTime = Format$((Timer - startTime) / 86400, "hh:mm:ss")
    End If

End Function