Last Updated on January 10, 2022 – 2 min read
Do macro run automatically? What are automatics VBA procedures?
Some macros with specific names are related to actions in Excel. They allow you to launch code automatically. You have to write your VBA code in a specific procedure. Your code will be executed automatically when the Excel action will be realized.
Automatic procedure launched when opening a workbook
The procedure named Auto_open is automatically activated when you open your workbook. It allows you to launch a VBA macro automatically at the opening.
Sub Auto_Open() 'This name of Sub run automatically 'at the opening of the workbook MsgBox "Bitcoin price is " & Cells(1,1).Value2 End Sub
You can also use the name Workbook_Open for running this macro.
Sub Workbook_Open() 'This name of Sub run automatically 'at the opening of the workbook MsgBox "Bitcoin price is " & Cells(1,1).Value2 End Sub
Automatic procedure launched when closing a workbook
The procedure Workbook_BeforeClose is activated automatically before closing your workbook with the following code. It allows you to launch the code automatically at the closing.
In the example below, the code checks the range A1. If the value is equal to ‘Wait’ the closing of the workbook is canceled. Else, the workbook is saved and closed.
Private Sub Workbook_BeforeClose(Cancel As Boolean) 'This name of Sub run automatically the macro 'at the closing of the workbook If Range("A1") = "Wait" Then MsgBox "The game is not over." 'Cancel the closing of the workbook if "Wait" in A1 Cancel = True Else 'If different of "Wait" in A1 save and close the wbk ThisWorkbook.Save End If End Sub
Automatic procedure launched at a specified time
How do you run a macro at a specific time?
The procedure below allows you to launch a macro automatically at a specific time. The code only works if your workbook with the macro is already open.
In the example below, the SpecificTime procedure will launch at 10 p.m. It will call the CloseWbk macro to close the workbook.
Sub SpecificTime() 'At the specific hour, the Sub CloseWbk is called h = "22:22:00" Application.OnTime EarliestTime:=TimeValue(h), Procedure:="CloseWbk" End Sub Sub CloseWbk() 'Close and save the current workbook ThisWorkbook.Close savechanges = True End Sub