How to Run Macros in Excel Automatically?

Macro is a tool in Excel that helps you overcome those cumbersome, repetitive tasks easily. You can record a process using macro and automate it anytime it is needed. It is especially useful for automating reports in Excel.

Macros are a set of actions that you can run as many times as you want and even make minor changes to it when necessary. Did you know you can also automate running Macros while opening an Excel file? In this post, we’ll attempt to cover how to run Macros in Excel automatically.

How to run Macros automatically?

To create a macro in Excel, first, check to see if the Developer tab is available. Generally, this tab is not displayed by default. Thus, you can add it to the ribbon through these steps: 

1. On the file tab, go to Options > Customize Ribbon.
2. Select the Developer checkbox under the Main Tabs under the Customize Ribbon. 

Now, you can find Macros and Visual Basic in the Code section under the Developer tab. 

You can run a macro automatically on opening an Excel workbook; for example, a welcome message or guidance for people who open it. You can also do the same when closing the workbook. For both these instances, you need to have a basic knowledge of Visual Basic codes. 

In the next section, we are going to see how to make Macros in Excel. 

Run a Macro automatically on opening Excel workbook

The following steps guide you to create the simplest automatic macro in Excel. 

1. Click on the Visual Basic button under the Developer tab. 
2. A window will open. Double-click on “ThisWorkbook” to select the whole workbook for this macro. 
3. Start the code with Private Sub Workbook_open() and enter. 
4. On the next line, if you want to show a message, write MsgBox “Optional Message”. 
5. Save As the file as Macro-Enabled Workbook. So, every time you open this workbook, you will see the message. 

Run a Macro automatically on closing Excel workbook

Now that you have learned to run a macro on opening Excel assume you want to show a message to the user when the Excel workbook is closed. It’s as simple as the previous example:

1. Click on the Visual Basic button to open Microsoft Visual Basic for Applications. 
2. Double-click on ThisWorkbook to start writing the codes. 
3. Each code starts with Private Sub (function). You can select the preferred function from the drop-down menu that is on the right top of this window. 
4. To run a macro automatically on closing Excel workbook, you need to use Workbook_BeforeClose(Cancel As Boolean). You can either choose it from the drop-down menu or write it yourself. 
5. Write the message you want to show after closing Excel. 

Enhance your business efficiency with our specialized Macro Development Services, tailored to automate and streamline your operations for maximum productivity.

Automatically run Macro in Excel when cell changes

There are several options for running a macro using VBA codes. One of the most practical cases of running a macro is when a certain cell value is changed. In this case, you should write a conditional statement using If/End If. 

Unlock the full potential of your software with our custom Add-In Solutions, designed to integrate seamlessly and enhance your workflow.

Follow the steps as in previous examples and write the following codes in the VBA project window: 

1. Select SheetChange from the drop-down menu on the top right. 
2. If you want to show the cell value to the user, write the if statement as follows: 

If Target.Address = “$C$4” Then

    MsgBox Target.Value

End If

3. If you want to show a customized message to the user, write the following statement: 

If Target.Address = “$C$4” Then

    MsgBox “You Should Not Change This Value”

End If

Run Macro Automatically at a Certain Time 

You can create a Macro in Excel and make it run at a specific time. For example, you may need to have automatic reports at a certain time of the day. Follow these steps:

1. Save the Macro with the name you prefer. (E.g., Report_Macro)
2. Go to the VBA application. Remember, the macro will only run when the workbook is open. Therefore, write Private Sub Workbook_Open().
3. Write the following code: 

Application.OnTime TimeValue(“13:30:00”), “Report_Macro”

This code will run the Report_Macro at 13:30 only if the workbook is open.

 

 

Excel VBA Automatically Run Macro every 5 Minutes

It is also possible to set a time interval for a macro to run automatically. For example, you may need to run a macro every 5 minutes. Here are the VBA codes that help you reach your goal. 

Sub macro_timer()

‘Tells Excel when to next run the macro.

Application.OnTime Now + TimeValue(“00:05:00”), “my_macro”

End Sub

Sub my_macro()

‘Macro code that you want to run.

MsgBox “This is my sample macro output.”

‘Calls the timer macro so it can be run again at the next interval.

Call macro_timer

End Sub

Two separate macros are required for the purpose. In this example, the first one is called macro_timer(), and the second is my_macro(). 

The first macro sets a time interval for the second macro to run. In our example, my_macro() is going to run every 5 minutes. 

In the end, the macro_timer is called again to start from the beginning.

 

 

Bottom Line 

Whether a welcome message or a specific function that you want to perform at a certain time, there are several ways you can use macros and VBA codes in Excel and do a task automatically. The method is easy; with a basic knowledge of VBA and Excel, you can write as many functions as you want for an Excel workbook.