How do you create an automatic timer using Excel 2016 VBA macro editor?

Question

I need to create a timer in Excel 2016 VBA editor that will go-off every second and display a message.

I have a simple code that will do just that; however when I run the code a message box appears and then an error follows. My code is presented below:

Private Sub Workbook_Open()
RunEveryTwoMinutes
End Sub

Sub RunEveryTwoMinutes()
MsgBox ("Stop!")
Application.OnTime Now + TimeValue("00:00:01"), "RunEveryTwoMinutes"
End Sub

The error I receive states: "Cannot run the macro 'C:user|generic\Book1.xlsm'. The macro may not be available in this workbook or all macros may be disabled.

I am not sure what is occurring. I simply need to find out a way to create a one second timer that initiates as soon as the code initializes.

YowE3K · Accepted Answer · 2017-02-28 19:34:25Z

1

Place this code in your workbook code module:

Private Sub Workbook_Open()
RunEveryTwoMinutes
End Sub

and place this code in a standard code module:

Sub RunEveryTwoMinutes()
MsgBox ("Stop!")
Application.OnTime Now + TimeValue("00:00:01"), "RunEveryTwoMinutes"
End Sub

Alternatively, you can have all the code in your Workbook code module, but you need to qualify the macro name:

Private Sub Workbook_Open()
RunEveryTwoMinutes
End Sub

Sub RunEveryTwoMinutes()
MsgBox ("Stop!")
Application.OnTime Now + TimeValue("00:00:01"), "ThisWorkbook.RunEveryTwoMinutes"
End Sub

I believe the first method is preferable.

answered Feb 28, 2017 at 19:34

YowE3K

24k7 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

A.S.H Over a year ago

Nice answer, but why do you prefer the function to be in a standard module?

YowE3K Over a year ago

@A.S.H - IMO it leads to less problems down the track if functions in the ThisWorkbook module are just specific to the workbook, and functions in forms are just specific to the form, and functions in worksheets are just specific to the worksheet, and functions in classes are specific to the class, and everything else gets put into generic modules.

A.S.H Over a year ago

That's a good point. Standard modules are also easier to export, hence to reuse. Kudos :)

Collectives™ on Stack Overflow

How do you create an automatic timer using Excel 2016 VBA macro editor?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related