Many users want to know how long it takes them to do a thing in Excel, or the teacher wants to test the students’ practical ability within the specified time in the process of class, and so on.

Some users may think of the clock in the operating system, or use flash to make a clock for timing. In fact, it is not necessary. Excel software can solve this problem. We can use VBA to make a stopwatch timer in Excel, start the stopwatch before doing it, stop the stopwatch after doing it, and see how long it took, as shown in the figure. This stopwatch displays the time in the cell, which can realize the basic functions of the stopwatch: click the start button to start timing, click the stop button to pause and stop, click the start button again to continue, and click the reset button to zero.

Making stopwatch timer with Excel

Let’s take excel as an example to illustrate its production process.

During activities, we will use some timers, which sometimes need to be displayed on the projection. Everyone will choose to download all kinds of timing software online, including free, cracked and charged. It’s very troublesome! In fact, we can use the EXCEL form on hand to make a relatively simple timer, beautify it a little, and make it feel like a “watch”.

Making stopwatch timer with Excel

1. First, make a basic table to make the later clock:

Draw a table as shown in the figure. Column a represents the scale (0 to 59) of each minute when the clock rotates for 60 minutes; Column B represents the scale of 12 hours (one value every 5 lines); C \ D \ e stands for hour \ minute \ second respectively.

Just follow the data in the table without much explanation.

Making stopwatch timer with Excel

2. Generate clock graph:

1) Select b, C, D and e cell data and insert – radar chart with data mark;

2) Delete redundant elements of automatically generated radar map: label, coordinate axis, grid, chart column, etc;

3) Add the data label of hour scale (fill in 1 to 12 by yourself);

Making stopwatch timer with Excel

Making stopwatch timer with Excel

3. Here are the key steps to prepare VBA Code:

1) Start editor, insert – module

2) Enter the following code:

Copy contents to clipboard

Sub onClock()

Dim h,m,s

h=Hour(Now)

m=Minute(Now)

s=Second(Now)

DoEvents

Range( “C2:E62” ).ClearContents

Cells(s+2,5)=9:Cells(s+3,5)=0

If s=59 Then Cells(2,5)=0

Cells(m+2,4)=8:Cells(m+3,4)=0

If m=59 Then Cells(2,4)=0

If h》=12 Then h=h-12

h=h*5+Int(m/12)

Cells(h+2,3)=6:Cells(h+3,3)=0

If h=59 Then Cells(2,3)=0

Application.OnTIme Now+TImeValue( “00:00:01” ), “onClock”

End Sub

Sub offClock()

On Error Resume Next

ApplicaTIon.OnTIme Now+TimeValue( “00:00:01” ), “onClock” ,,False

End Sub

3) Save, close, don’t forget!!!

Making stopwatch timer with Excel

Making stopwatch timer with Excel

4. Add control button:

1) Select “development tool” in the menu bar, insert the form control, and draw two rectangular buttons in the table;

2) Assign the two buttons to the macro “onclock” — named “start timing” and “offclock” — to stop timing.

Making stopwatch timer with Excel

Making stopwatch timer with Excel

5. Realize the timing function and beautify the clock:

Click the start button, you will find that the clock starts to walk, and click stop to pause the timing.

The rest is to give full play to your creativity and beautify the clock as much as possible.

Making stopwatch timer with Excel

Note: the table must be made as shown in the picture.

Leave a Reply

Your email address will not be published. Required fields are marked *