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
- 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
- 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.
Read More: How to Create a Timer with Milliseconds in Excel VBA
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
- 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.
Read More: How to Create Timer Event with VBA in Excel
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
- 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
- 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.
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.
