Come along to see 10 different ways to run a VBA macro in Excel. This could be a recorded macro, or a macro you’ve created or copied into a standard module. The macro must be a public procedure in a public module, otherwise most of the methods won’t work. Any standard module is public unless specified otherwise. A public procedure starts with Sub or Public Sub, as opposed to Private Sub.
First of all, go to the Excel VBA editor (Alt+ F11), insert a standard module (Module1 by default), and add a macro (find an example of a public Sub below). A public procedure or macro can be run from any other place in the document, and even from other workbook.
Sub is the type of procedure, a sub-procedure, which is the most common type of procedure. Learn more about that, and the basics of Excel macros and VBA in this training for beginners. Alternatively, get the Excel VBA Guide for Beginners book to learn the basics in no time (just 75 pages to read with lots of examples), or watch the Tutorial for beginners on YouTube (Excel Macro Mania).
1. Run Excel macro from View Macros
The first way to run the macro is going to View -> Macros -> View Macros, select the macro and run. We can also view macros under the Developer tab. If you don’t have the developer tab in Excel, see how to add it here.
2. Run Excel macro from a keyboard shortcut
We can add a keyboard shortcut to run the macro in Excel. We do that going to View -> Macros -> View Macros again, and then Options to open a new dialog to choose a shortcut key that is used along with the Control key to run the selected macro. For example, if you enter the letter “m”, the macro runs when you press Ctrl + m in the keyboard.
3. Run Excel macro with debug Play button in the VBA editor
We can use the Play button in the Excel VBA editor from the Run folder, or directly from the icon on the standard toolbar (if added), or also pressing F5 while on the VBA Editor and with the cursor inside the macro code we want to run. The button says Run Sub/Userform, and it is generally used while writing code, testing, and debugging.
4. Run Excel macro from a Button or any other Shape
We can run a macro from a button, or any other shape. Add a shape in Excel under Insert -> Shapes, then right-click the shape and click assign macro. Select a macro and click ok; that macro will run when clicking the shape. Similarly, we can add any Form Control going to Insert in the Excel Developer Tab and selecting any form control, for example, a command button. Then we right-click the button and assign the macro as previously.
5. Run Excel macro from the Quick Access Toolbar
We can run a macro from an icon in the quick access toolbar, which is at the top of the Excel window next to the save, undo, redo buttons. Right-click there and select customize the quick access toolbar, then select Macros under Choose Commands From, and select and add the target macro to the toolbar along with the other buttons in Excel. An icon associated with that macro will show up in the quick access toolbar, and when clicked, it will run the macro.
6. Run Excel macro from the Ribbon
We can also run a macro from an icon added to the ribbon. Right-click the ribbon and choose to customize the ribbon. We get a dialog quite similar to the one we’ve seen before with the quick access toolbar. Then choose macros in the drop-down on the left, but now, we need to create a custom group to add the macro to, it won’t let you add it to a regular tab. On the bottom right corner select New Tab, rename it as needed, and then also a New Group (which shows by default), and rename it if appropriate. Now select that group, add the macro and click OK. We can also change the name or the symbol for the macro. A new tab will appear in the Excel ribbon with the group and the macro underneath.
7. Run Excel macro from a Worksheet event
We can run a macro as a result of a worksheet event. That means, the macro can run when selecting a worksheet, or even when deleting the worksheet, when changing the selected cell or range, or when changing the value in a cell or range, etc. We need to go the worksheet module, select worksheet on the left drop-down, and then choose the event on the right. By default, we get the selection change event procedure, so if we call the macro from within this procedure, it will run when the selection changes. We can just write MacroExample, or use Call MacroExample.
Now, whenever we select another cell or range, the macro will run. We can do the same when the value changes adding the Worksheet_Change event procedure. We can also target a particular cell or range, setting the Target parameter as needed. For example, we can run the macro only when the value in cell B2 changes as indicated below.
8. Run Excel macro from a Workbook event
We can run a macro as a result of a workbook event. It could run when the workbook opens, or before saving, or closing. It’s quite similar to what we’ve seen before. We need to go to ThisWorkbook, and select workbook on the left drop-down, and then a particular workbook event on the right. We get the open event procedure by default. If we call the macro from within this procedure, it will run when the workbook opens. We can use any other event in the same way.
9. Run Excel macro from a Hyperlink
We can run a macro from a hyperlink. This could be useful to run a piece of code doing some background check or something when following a hyperlink. We need first to add a hyperlink to a cell or shape in the Excel worksheet, and link it to the destination (that can be a location in the workbook, or other workbook, or a file, a website, etc. Then we need to add an event procedure to the worksheet, as we’ve done before, to FollowHyperlink, and from that event procedure we call the macro, which will run when the hyperlink is clicked.
10. Run Excel macro from other macro or procedure
And finally, we can call a macro from another macro or procedure. We’ve actually kind of done that already with event procedures, but now we’ll do it from a procedure in an standard module (Module1 for example). We have another macro, the CallerMacro, where we gonna call the MacroExample. So now if we run the CallerMacro, with any other different methods we’ve seen in this article, it runs and calls the other macro, that displays the message box.
And these are 10 different ways to run a VBA macro in Excel!
No comments:
Post a Comment