Automate Calculations with Time using VBA

How to automatically perform calculations every 10 seconds using VBA.
The Application.OnTime Method schedules a procedure to run at a specific time or after a specific amount of time has elapsed.
Examples: We can use
Application.OnTime Now + TimeValue(“00:00:10”), “my_macro”
to run 10 seconds from now

We can also use:
Application.OnTime TimeValue(“9:00:00”), “my_macro”
to run a macro at at 9 am

To stop my_macro we can use:
Application.OnTime EarliestTime:=TimeValue(“17:00:00″), _
Procedure:=”my_macro”, Schedule:=False

Watch the video below:

Watch this video on YouTube.

Here’s the complete VBA code to automate calculations with time.

First we write the code for the workbook_open event:

Private Sub Workbook_Open()
count = 0
Range(“A4:A8”) = “”
nextScheduledTime = Now + TimeValue(“00:00:10”)
Application.OnTime nextScheduledTime, “Calculate”
End Sub

We create a module ‘Calculate’ with two public variables – nextScheduledTime and count. Both these variables will be accessible to all modules in the workbook.

Public nextScheduledTime
Public count As Integer

Sub Calculate()

‘ We reschedule the onTime function to execute in 10 seconds
nextScheduledTime = Now + TimeValue(“00:00:10”)
Application.OnTime nextScheduledTime, “Calculate”
‘ Code to calculate
Range(“B2”) = (11 – 9) * Rnd() + 9
Dim erow As Long
erow = Sheet1.Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1) = Range(“B3”)
Debug.Print Now
count = count + 1
If count = 5 Then
stopCalc
End If
End Sub

Finally we create a module stopCalc to be able to stop the ‘Calculate’ macro whenever we wish to.

Sub stopCalc()
Application.OnTime nextScheduledTime, “Calculate”, , False

End Sub

Further reading:

Find last row column cell in an Excel worksheet with VBA

Random Numbers

Application.OnTime Method (Excel)

Leave a Reply

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