USING TIMERS - 3

Creating A Stopwatch & Countdown Clock

In this tutorial we will be creating the simple stopwatch & countdown timer shown below.

How To Create The Stopwatch And Coundown Timers.

Firstly we insert three images that we will use as the start, pause & reset buttons. They can be any image you like, it really doesn't matter.

When you have chosen/downloaded your images, save them to a location on your device. Feel free to use mine.

1. On the Menu bar Click Insert then Pictures, hover over and click "This Device" and browse to the location where your images have been saved. Select an image and click insert.

   
   
   
   
   

2. Your image will be inserted onto your spreadsheet as below.

   
   
   
   
   

3. Repeat these steps until you have all the images you require on your sheet, resize them so that they are the size required and position them as shown below.

   
   
   
   
   

4. Select Cells B8 : D9, Select "Merge & Center", and add a thick outside border. The colour of your choice.

5. Now we will change the format of the merged cell to the "Time" format. With the cell selected press the "Ctrl" & "1" keys together and the format options window will appear. Select the "Time" category on the left, and the type shown below. Then click OK and it will appear as shown below.

   
   
   
   
   

6. We will now enter the value "00:00:00" into the merged cell, and change the format of the cell so that it is centre aligned both vertically & horizontally and the font is "Impact" and size "26".

   We will also select & merge cells B2 : D2 and add the title "TIMER", formatting it so that it is centre aligned both vertically & horizontally and the font is "ARIAL BOLD" and size "14". All should now appear as shown below.

   
   
   
   
   

7. Now, to do the same for the countdown timer we shall simply select cells A1 : E10, and copy and paste them into cell A14. Select the title in cell B15 and change it to "COUNTDOWN", Select the time in cellB21 and change it to "00:15:00" to show a 15 minute time to count down from and all should now appear as shown below.

   
   
   
   
   

Now we will add the VBA code that will operate the stopwatch and countdown timers into two modules in the workbook. For each there will be three pieces of code or "Sub Procedures". One for each button, as we will be using the button images as buttons to execute our code.

To do this for the stopwatch follow the steps below:-

1. Open the VBA Editor (Alt+F11)    See how to do that here
2. Insert a Standard Module    See how to do that here  The module will be called "Module1" you could change that to something more intuitive such as "Stopwatch" if you desired but there is no real need here as we wont be writing tons of modules that we will get all twisted up with.
3. Paste the following code into module1 and hit save.



    
Dim a As Boolean

Sub StartTimer()
a = True
  Do While a
     Application.Wait (Now + #12:00:01 AM#)
       DoEvents
     Sheets(1).Cells(6, "B") = Format(DateAdd("s", 1, Sheets(1).Cells(6, "B")), "hh:mm:ss")
   Loop
End Sub

Sub PauseTimer()
    a = False
End Sub

Sub ResetTimer()
    a = False
    Sheets(1).Cells(6, "B") = "00:00:00"
End Sub
    
  





Just as you did with the Module1 above, insert a new module which will by default be named Module2 & paste the following code into the window that opens and again hit save.




    
Dim b As Boolean

Sub StartCountDown()
b = True
Do While b
    Application.Wait (Now + #12:00:01 AM#)
        DoEvents
If Sheets(1).Cells(21, "B") = #12:00:01 AM# Then Exit Sub
Sheets(1).Cells(21, "B") = Format(DateAdd("s", -1, Sheets(1).Cells(21, "B")), "hh:mm:ss")
Loop
End Sub

Sub PauseCountDown()
b = False
End Sub

Sub ResetCountDown()
b = False
Sheets(1).Cells(21, "B") = "00:15:00"
End Sub

    
  





The VB Editor window should now appear as below. Now hit save.





4. We now need to make the buttons work! We can do this by assigning one of the Macro Sub Procedures we have to each one.

• Minimise the VB editor window so that you can see the workbook again, and right click on the timer's start button. Hover over the menu that appears and select "Assign Macro". The following window will appear. from this window select "StartTimer" and click Ok.

  
  
  
  
• Right click on the Timer's Pause button & repeat the step above assigning the PauseTimer Macro
• Right click on the Timer's Reset button & repeat the step above assigning the ResetTimer Macro
• Right click on the Countdown's Start button & repeat the step above assigning the StartCountDown Macro
• Right click on the Countdown's Pause button & repeat the step above assigning the PauseCountDown Macro
• Right click on the Countdown's Reset button & repeat the step above assigning the ResetCountDown Macro

That's it, all done! Now test your stopwatch and countdown timer to see how they work.

Save the file with whatever name you see fit, and with a file extension of "XLSM" (Macro Enabled Excel File). We have called the file Timers.XLSM