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:
- On the file tab, go to Options > Customize Ribbon.
- 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.
- Click on the Visual Basic button under the Developer tab.
- A window will open. Double-click on “ThisWorkbook” to select the whole workbook for this macro.
- Start the code with Private Sub Workbook_open() and enter.
- On the next line, if you want to show a message, write MsgBox “Optional Message”.
- 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:
- Click on the Visual Basic button to open Microsoft Visual Basic for Applications.
- Double-click on ThisWorkbook to start writing the codes.
- 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.
- 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.
- 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:
- Select SheetChange from the drop-down menu on the top right.
- 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
- 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:
- Save the Macro with the name you prefer. (E.g., Report_Macro)
- Go to the VBA application. Remember, the macro will only run when the workbook is open. Therefore, write Private Sub Workbook_Open().
- 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.
Generally, macros are turned off in Excel due to security features. You may see a warning that tells you this file has a macro that might be harmful to your system. If you are sure there is nothing wrong with the macro you want to run, enable the macro on this warning window.
You can also click on Macro Security under the Code section and select Enable all macros.
Press the Alt + F8 shortcut to see the macros your Excel workbook has. Then select the preferred one and click Run.
Generally, the VBA codes are saved in the macros. Therefore, to run VBA codes, you can press Alt + F8 shortcut and select the VBA code (your macro name) you want. To write new VBA codes, press Alt + F11 to open the VBA Application.
It is not possible to run a macro at a certain time if the workbook is not open. However, you can write a VBA script to apply the changes in the Excel file, then run the VBA script to see the result. It’s more complicated than running a macro; you should be more familiar with VBA codes.
Our experts will be glad to help you, If this article didn't answer your questions.
We believe this content can enhance our services. Yet, it's awaiting comprehensive review. Your suggestions for improvement are invaluable. Kindly report any issue or suggestion using the "Report an issue" button below. We value your input.