How to Create a Clock to Measure Task Time

[RunTime91]

Greetings Experts...

What I'm trying to accomplish is to create a clock showing hh:mm:ss that begins when I begin a task and ends once the task is complete and then of course records the time it took to complete the task (in minutes) in a spreadsheet

Yes, there are 100's of videos that dance around this concept but none that land on it. And I think it is possibly because it can't be done.

Here is the remaining context.

This clock will be embedded into a label on a userform. The same userform I will be using to perform the tasks I want to measure. (I think this may be where I enter the realm of impossibility).

So, in other words, this clock would need to start when the task begins, run in the background while I perform the task which, of course, is all code centric. Thus other sub routines would need to run simultaneously.

Like most here, I have built many different types of clocks in Excel to perform many functions, but I've never built one to do what I'm outlining here.

So, first ~ Can this be done (and without much if any volatility) ?
If yes, can you point me in a direction where I can begin to build this code...

As always, Thank You!

RT91

 

[Logit]

On your worksheet, create a CommandButton connected to a Regular Module macro named : ShowUserForm1
In the Regular Module paste the following macro code :

Option Explicit

Sub ShowUserForm1()
    On Error GoTo ErrorHandler
    UserForm1.Show
    Exit Sub

ErrorHandler:
    MsgBox "An error occurred: " & Err.Description
End Sub

Sub StartUserFormTimer()
    If UserForm1.Visible Then
        UserForm1.CommandButton2_Click
    End If
End Sub

Create a UserForm1 (use the default name, do not change it).

On the UserForm paste three textboxes. and two commandbuttons.
The names of the textboxes and commandbuttons will be the default names Excel provides ... do not change them.

In the UserForm paste the following macro code :

Option Explicit

Public Sub CommandButton2_Click()
    On Error GoTo ErrorHandler
    Dim startTime As Double
    Dim endTime As Double
    Dim totalTime As Double
    Dim i As Long
    
    
    'Your Macro Name here'   <------------------------------- Your Macro Name Here


    ' Record the start time
    startTime = Timer
    Me.TextBox1.Text = Format(startTime / 86400, "hh:mm:ss")
    DoEvents ' Refresh the form to display the start time

    ' Run the loop for 1,000,000 iterations (for testing purposes)
    For i = 1 To 1000000
        If i Mod 100 = 0 Then
            DoEvents ' Allow other processes to run every 100 iterations
        End If
    Next i

    ' Record the end time
    endTime = Timer
    Me.TextBox2.Text = Format(endTime / 86400, "hh:mm:ss")
    DoEvents ' Refresh the form to display the end time

    ' Calculate the total time
    totalTime = endTime - startTime
    Me.TextBox3.Text = Format(totalTime, "0.00") & " seconds"
    Exit Sub

ErrorHandler:
    MsgBox "An error occurred: " & Err.Description
End Sub

Public Sub CommandButton1_Click()
    On Error GoTo ErrorHandler
    Unload Me
    Exit Sub

ErrorHandler:
    MsgBox "An error occurred: " & Err.Description
End Sub

Edit the title of CommandButton2 to "START"
Edit the title of CommandButton1 to "STOP"

Go back to the worksheet, click the command button to display the userform.
Click the RUN button.


Download sample workbook : Internxt Drive – Private & Secure Cloud Storage

To use this code in your project you will need to paste your macro code inside the userform macro : Public Sub CommandButton2_Click(), at the very top/beginning of the macro. There is a
notation in the above code showing where (the workbook download does not have this note).