Excel VBA to Create Timer Loop (3 Unique Examples)

In Microsoft Excel, we often need to run certain VBA codes that help to create a timer loop. This timer loop can be different based on the required output. But the common thing is it creates a repetitive time format according to the condition given. In this article, we will learn to apply Excel VBA to create a timer loop. We will discuss it with 3 unique examples.

3 Unique Examples of Excel VBA to Create Timer Loop

The timer loop is a very useful command in Excel. It makes it easier to work on time frames in different situations. Following are 3 examples of how to create it with VBA in Excel.

Example 1: Create Timer Loop with Intervals in Excel

In this first example, we will create a timer loop that will generate a Message Box after a certain interval of time. Let’s see how to do this.

First, go to the Developer tab and select Visual Basic from the Code section.

Second, select ThisWorkbook in the Project – VBAProject window and insert this code.

Private Sub Workbook_Open()
'Call timer_loop
End Sub

This code will run the process of showing the message box when we will give a command with a command button.

Third, open Module from the Insert tab.

Now, insert this code in the Code window and press Ctrl + S to save it.

Public interval As Date
Sub timer_loop()
interval = Now + TimeValue("00:00:5")
Application.OnTime interval,"timer_macro"
End Sub
Sub timer_macro()
MsgBox "This is a timer loop output."
Call timer_loop
End Sub
Sub end_macro()
On Error Resume Next
Application.OnTime earliesttime:=interval, procedure:="timer_macro", schedule:=False
End Sub

From this code, we can see that there are 3 parts of code. Sub timer_loop will start the process to show the message box. Then Sub timer_macro generates the message box. Lastly, Sub end_macro helps to stop the code.

Next, go to the Insert tab and select Rectangle:Rounded Corner from the Shapes group.
Otherwise, you can choose any other shape according to your preference.

Following this, insert shapes as buttons in the worksheet like this.

At this stage, right–click on the START button and choose Assign Macro from the Context Menu.

Then, select timer_loop macro from the Assing Macro window and press OK to confirm the process of assigning.

Similarly, assign end_macro code to the END button.

Finally, press the START button and after every 5 seconds, this message box will appear in your workbook.

Lastly, press the END button to stop the process.

Example 2: Make Timer with Reset Option in Excel

Let us make a stopwatch timer with VBA in Excel that will work in a loop. To do the task, follow the process carefully.

In the beginning, make a dataset with the following titles like this.

Then, insert this formula that will define the Time Difference between the Start Time and Now time.

=C6-C5

Now, open the VBA Editor by pressing Alt + F11 on your keyboard.
Then, open Insert > Module and type this code on the Code page.

Sub Timer_Start()
Dim wksh As Worksheet
Set wksh = ThisWorkbook.Sheets("Timer with Reset")
wksh.Range("C4").Value = "Start"
If wksh.Range("C5").Value = "" Then
   wksh.Range("C5").Value = Now
End If
x:
VBA.DoEvents
If wksh.Range("C4").Value = "Stop" Then Exit Sub
wksh.Range("C6").Value = Now
GoTo x
End Sub

This code will operate the whole process to start the timer and insert time values in the C5 and C6 Cells. Along with this, we will get the state of the timer whether it is running or not in Cell C4 with this code.

Following this, open a new Module window and insert this code that will stop the time.

Sub Timer_Stop()
Dim wksh As Worksheet
Set wksh = ThisWorkbook.Sheets("Timer with Reset")
wksh.Range("C4").Value = "Stop"
End Sub

Similarly, open another Module window and type this code that will reset the timer to 0.

Sub Timer_Reset()
Dim wksh As Worksheet
Set wksh = ThisWorkbook.Sheets("Timer with Reset")
wksh.Range("C4").Value = "Stop"
wksh.Range("C5:C6").ClearContents
End Sub

Next, assign buttons with the Insert > Shapes command and choose any shape from the drop-down list.

Accordingly, insert 3 buttons like this.

After this, type 0 in Cell F6.

Then, right–click on it and select Format Cells from the Context Menu.

Afterward, choose any time format from the Number > Time > Type section in the Format Cells window.

Similarly, format the time format of Cell C7 as well.
At this stage, right-click on the START button and choose Assign Macro.

Then, select Timer_Start macro and press OK to assign it to the button.

Accordingly, assign the Timer_Stop and Timer_Reset macros to the STOP and RESET buttons respectively.
Lastly, insert this formula to align Cell C7 with Cell F6.

=C7

Finally, operate the timer with the buttons in a loop like this.

Example 3: Calculate Volume with VBA TIMER Function in Excel

In this example, let us assume we need to calculate the amount of water to be filled in a tank with the pump machine. For this, we will consider the Flow is 5 L/sec to fill up the tank. Therefore, insert this value in Cell C5.

First, make a box in Cell E6.
Then, insert two Arrows like this from the Insert > Shapes command.

Along with this, insert the TURN ON and TURN OFF buttons in the worksheet.

Now, open the VBA Editor > Insert > Module and insert this code that will work on the TURN ON and TURN OFF buttons.

Sub machine_on()
Range("E6").Interior.ColorIndex = 4
Range("E6").Value = "Flow"
Timer
End Sub
Sub machine_off()
Range("E6").Interior.ColorIndex = 3
Range("E6").Value = "Close"
End Sub

Following this, open another Module and insert this code.

Sub Timer()
If Range("E6").Value = "Flow" Then
Application.OnTime Now() + TimeValue("00:00:01"), "Volume"
End If
End Sub
Sub Volume()
Range("B5").Value = Range("B5").Value + Range("C5").Value
Timer
End Sub

In this code, we used the TIMER function to measure the time intervals after every 1 second based on the condition in the Volume macros.

Up next, right–click the TURN ON button > select Assign Macro > choose machine_on macros > press OK.

Similarly, assign the machine_off macros to the TURN OFF button.

Finally, you can find the Volume of the water in the tank by turning on and off the buttons filled with 5 L/sec Flow.

To start again the loop from 0, simply delete the value in Cell C5 and start the process again.

How to Use VBA Loops in Excel

Additionally, let us learn a bit about VBA loops. It helps to reoccur outputs when conditions are met. Otherwise, it gives an error and returns to the code.

Repeat Text with Do Until Loop

For illustration, we have a sample dataset here. It shows the information of 5 Employee Names in the Cell range B5:B9.

Now, we want to define the Department in which each employee is working. With the VBA code, we will insert the Department name all at once without typing individually.

To do this, open the Microsoft Visual Basic for Applications window by pressing Alt + F11.
Following this, open Insert > Module and insert this code.

Sub DoUntilTimerLoop()
Dim x As Integer
x = 5
Do Until x > 9
Cells(x, 3).Value = "Sells & Marketing"
x = x + 1
Loop
End Sub

In this code, x = 5 defines the number of rows where we want the output. Then x > 9 states that only integer values from 0 to 8 will be acceptable to this code. This condition is signified with the Do Until loop in VBA. Lastly, Cells (x,3) defines that we will have the output in the 3rd column. Along with it, x=x+1 helps to alter every row one after another to insert the text “Sells & Marketing”.

Lastly, press F5 on your keyboard to run the code.
That’s it, we have all the Department names all at once.

Insert Month & Dates with Do While Loop in Excel

In this last one, let us do a quick VBA timer with the Do While loop in excel. For this, we will get the present month along with the dates with the code.

For this, insert this code inside a new Module in the VBA Editor window.

Sub CurrentMonthDates()
Dim CMDt As Date
Dim x As Integer
x = 0
CMDt = DateSerial(Year(Date), Month(Date), 1)
Do While Month(CMDt) = Month(Date)
Range("B5").Offset(x, 0) = CMDt
x = x + 1
CMDt = CMDt + 1
Loop
End Sub

Lately, press F5 to Run the code.
Finally, we will have all the dates along with the month’s name as follows.

Download Practice Workbook

Download this practice file to try by yourself.

VBA Timer Loop.xlsm

Conclusion

Finally, we are at the end of our long article. Here we have seen 3 unique examples where we applied Excel VBA to create a timer loop. Let us know if you know more examples of this.

Get FREE Advanced Excel Exercises with Solutions!