Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Create Excel Timer Using VBA

    Today I'm going to show you how to create a Excel Timer. First we need to add two buttons to the excel sheet. One is to start timer. Other one is to stop the timer. And we need to reserve one cell to display the value. Here is an example interface.


We can give it nice look by removing grid-lines


Now add new module in the VBA editor of your workbook. And add below code to it.


Public StopMacro As Boolean


Sub StartTimer()

StopMacro = False

Dim WS As Worksheet

Set WS = Worksheets("Sheet1")

Dim StartTime, timeNow

StartTime = Now

Do Until StopMacro = True
    DoEvents
    timeNow = Now
    WS.Range("F8").Value = Format(timeNow - StartTime, "hh:mm:ss")
Loop

End Sub


Sub StopTimer()

StopMacro = True

End Sub


Then assign StartTimer macro to start button and StopTimer macro to Stop button. I think you will able to understand this code easily. However I will explain few important points. In this program we have declared one public variable of type Boolean. We use that variable to detect when user click stop button. So when user click stop button, value of StopMacro variable become true. Then it is detected in do until loop.

In this program we used inbuilt Excel function call Now which returns date and time.

Value entering cell and format of the value is given by below line of code.

WS.Range("F8").Value = Format(timeNow - StartTime, "hh:mm:ss")

Also it is very important to use DoEvents within the do until loop. Otherwise user will not get any chance to click on the stop button and loop will become infinite loop.


Do Until StopMacro = True
    DoEvents
    timeNow = Now
    WS.Range("F8").Value = Format(timeNow - StartTime, "hh:mm:ss")
Loop

And this is how timer shows the time.


Contact Form

Name

Email *

Message *

Popular Posts