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.
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.
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
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”
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().
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.
‘Tells Excel when to next run the macro.
Application.OnTime Now + TimeValue(“00:05:00”), “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.
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.
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.
Why is Excel macro not running automatically?
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.
How to run a macro in Excel with a shortcut?
Press the Alt + F8 shortcut to see the macros your Excel workbook has. Then select the preferred one and click Run.
How to run VBA code in Excel automatically?
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.
Can I run a macro automatically at a certain time without opening Excel?
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.
Contact us today at – and speak with our specialist.
BSUPERIOR SYSTEM LTD. provides custom software and technology solutions to small and medium sized businesses. We have been helping businesses to operate more efficiently, reduce money and time spend on redundant repetitive daily tasks.