Automatics Sub

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