Create Stock Chart with Excel VBA

Step 1: Understanding the Requirements

A stopwatch in Excel should:

• Start counting time when triggered.
• Pause and resume when needed.
• Reset to zero.
• Display the elapsed time dynamically.
• Work without freezing Excel (using Application.OnTime instead of DoEvents).

Step 2: Creating the User Interface (UI)

Before writing the VBA code, let’s create a simple UI in an Excel worksheet:

1. Insert Buttons (using Form Controls) and link them to the macro:
   • Start Button (e.g., named « btnStart »)
   • Pause Button (e.g., named « btnPause »)
   • Reset Button (e.g., named « btnReset »)
2. Designate a Cell for Display:
   • Select a cell (e.g., B2) to display the elapsed time.

Step 3: Writing the VBA Code

Now, let’s write the VBA code for the stopwatch.

1. Declare Variables

We need to track:

• The start time
• The elapsed time before pausing
• Whether the stopwatch is running

Option Explicit
Dim startTime As Double
Dim elapsedTime As Double
Dim isRunning As Boolean
Dim nextTick As Date

2. Start Stopwatch

This macro initializes the stopwatch and begins updating the display every second.

Sub StartStopwatch()
    If Not isRunning Then
        ' Capture the start time if not already running
        startTime = Timer - elapsedTime
        isRunning = True
        UpdateTime
    End If
End Sub

Explanation:

• If the stopwatch isn’t running, we capture the start time (Timer is the number of seconds since midnight).
• We subtract the previously recorded elapsedTime (to allow resuming).
• isRunning is set to True and we start updating the time.

3. Update Displayed Time

This subroutine keeps updating the elapsed time.

Sub UpdateTime()
    If isRunning Then
        elapsedTime = Timer - startTime
        Sheet1.Range("B2").Value = Format(elapsedTime, "0.00") & " sec"
        ' Schedule the next update
        nextTick = Now + TimeValue("00:00:01")
        Application.OnTime nextTick, "UpdateTime"
    End If
End Sub

Explanation:

• Calculates elapsed time dynamically.
• Updates the assigned cell (B2).
• Schedules itself to run again in 1 second using Application.OnTime.

4. Pause Stopwatch

This macro stops the timer temporarily.

Sub PauseStopwatch()
    If isRunning Then
        isRunning = False
        Application.OnTime nextTick, "UpdateTime", , False
    End If
End Sub

Explanation:

• Stops Application.OnTime, preventing further updates.
• Stores the elapsedTime so it can resume later.

5. Reset Stopwatch

This resets everything to zero.

Sub ResetStopwatch()
    isRunning = False
    elapsedTime = 0
    Sheet1.Range("B2").Value = "0.00 sec"
    Application.OnTime nextTick, "UpdateTime", , False
End Sub

Explanation:

• Stops the stopwatch.
• Resets elapsedTime to zero.
• Clears the scheduled Application.OnTime events.

Step 4: Assign Macros to Buttons

1. Right-click each button.
2. Select « Assign Macro ».
3. Link them as follows:
   • « StartStopwatch » → Start Button
   • « PauseStopwatch » → Pause Button
   • « ResetStopwatch » → Reset Button

Step 5: Testing the Stopwatch

1. Click Start → The time should begin updating.
2. Click Pause → The time should stop but remain visible.
3. Click Start again → The stopwatch should resume from where it stopped.
4. Click Reset → The timer should reset to 0.

Final Notes

• Application.OnTime ensures Excel remains responsive.
• The format « 0.00 sec » makes the output readable.
• The logic supports pausing and resuming, unlike traditional DoEvents-based loops.