In this tutorial, we'll cover many different ways to run a macro in Excel - from the ribbon and VB Editor, with a custom keyboard shortcut, and by creating your own macro button.
Though running an Excel macro is a simple thing for experienced users, it might not be immediately obvious to beginners. In this article, you will learn several methods to run macros, some of which may completely change your way of interacting with Excel workbooks.
One of the fastest ways to execute VBA in Excel is to run a macro from the Developer tab. If you have never dealt with VBA code before, you may need to activate the Developer tab first. And then, do the following:
Tip. If the Developer tab is not added to your Excel ribbon, press Alt + F8 to open the Macro dialog.
If you execute a certain macro on a regular basis, you can assign a shortcut key to it. A shortcut can be added while recording a new macro and to an existing one. For this, carry out these steps:
Tip. It is recommended to always use uppercase key combinations for macros (Ctrl + Shift + letter) not to override the default Excel shortcuts. For example, if you assign Ctrl + f to a macro, you will lose the ability to call the Find and Replace dialog.
Once the shortcut is assigned, simply press that key combination to run your macro.
If you aim to become an Excel pro, then you should definitely know how to start a macro not only from Excel, but also from the Visual Basic Editor. The good news is that it's a lot easier than you might expect :)
Alternatively, you can use one of the following shortcuts:
Tip. If you like operating Excel from you keyboard, this tutorial may come in handy: 30 most useful Excel keyboard shortcuts.
The traditional ways of running macros are not hard, but still might present a problem if you are sharing a workbook with someone who has no experience with VBA - they simply won't know where to look! To make running a macro really easy and intuitive for anyone, create your own macro button.
And now, you can run the macro by clicking its button. The macro we've assigned, formats the selected cells like shown in the screenshot below:
Tip. You can also assign a macro to an existing button or other Form controls such as spin buttons or scrollbars. For this, right-click the control inserted in your worksheet and choose Assign Macro from the pop-up menu.
Regrettably, it is not possible to customize the appearance of button controls, because of which the button we created a moment ago does not look very nice. To make a really beautiful Excel macro button, you can use shapes, icons, images, WordArt and other objects.
As an example, I'll show you how you can run a macro by clicking a shape:
Now you have a shape that looks like a button and runs the assigned macro whenever you click on it:
The macro button inserted in a worksheet looks good, but adding a button to each and every sheet is time-consuming. To make your favorite macro accessible from anywhere, add it to the Quick Access Toolbar. Here's how:
At this point, you can click OK to save the changes or do a couple more customizations described below.
Done! Now you have your own Excel button to run macro:
In case you have a few frequently used macros in your Excel toolbox, you may find it convenient to have a custom ribbon group, say My Macros, and add all popular macros to that group as buttons.
First, add a custom group to an existing tab or your own tab. For the detailed instructions, please see:
And then, add a macro button to your custom group by performing these steps:
For this example, I've created a new tab named Macros and a custom group named Formatting Macros. In the screenshot below, we are adding the Format_Headers macro to that group.
As an example, I've put three macro buttons to my Excel ribbon and can now run any of them with a button click:
Sometimes you may want to run a macro automatically on opening a workbook, for example, to display some message, run script or clear a certain range. This can be done in two ways.
Below are the steps to create a macro that automatically runs whenever you open a specific workbook:
For example, the following code will display a welcome message each time the workbook is opened:
Another way to run a macro automatically on workbook opening is by using the Auto_Open event. Unlike the Workbook_Open event, Auto_Open() should sit in a standard code module, not in ThisWorkbook.
Here are the steps to create such a macro:
Here's an example of the real-life code that displays a message box on workbook opening:
Note! The Auto_Open event is deprecated and available for backwards compatibility. In most cases, it can be replaced with the Workbook_Open event. For more information, please see Workbook_Open vs. Auto_Open.
Whichever event you use, your macro will run automatically every time you open the Excel file containing the code. In our case, the following message box is displayed:
Now that you know lots of ways to run a macro in Excel, you just need to choose the one best suited for your needs. I thank you for reading and hope to see you on our blog next week!
Table of contents