Schedule VBA Procedure in MS Excel Without Application.OnTime

In this article we will demonstrate an easy alternative to Application.OnTime in order to run VBA procedures of Excel repeatedly.

Let us suppose that we have connected our MS Excel Worksheet to some external data (e.g. a SharePoint List or an Access database) and the data is being automatically refreshed every 2 minutes while the Workbook is open, which can be done with use of Connections.

Let us also suppose that our Workbook processes the data, and the output should be visible to other Workbooks, for which we must save the Workbook.

Programming code 1 demonstrates how to save the Workbook each time after the external data is imported to the Worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)


? ? Dim xDiAl As Boolean
? ??
? ? xDiAl = Application.DisplayAlerts
? ??
? ? Application.DisplayAlerts = False
? ??
? ? ThisWorkbook.Save
? ??
? ? Application.DisplayAlerts = xDiAl


End Sub        

The Worksheet_Change event is triggered each time after the import of the external data (even if there are no changes) and, in turn, can trigger a subprocedure, as shown in the Programming Code 2.?

Private Sub Worksheet_Change(ByVal Target As Range)


? ??Call xSubProc


End Sub        

We can use this approach as an easy alternative to Application.OnTime and schedule VBA procedures to run repeatedly with the refresh interval of the Connection Properties.

#Excel #ScheduleVBAProcedure #ConnectionProperties #ApplicationOnTime #VisualBasicForApplications #DevelopmentForOffice365 #ImportExternalData #SharepointLists