15 Ways to Run a VBA Macro in Microsoft Excel

Do you want to learn how to run a VBA macro in Microsoft Excel? This guide will show you all the methods you can use to run your VBA code.

Microsoft Excel is a powerful spreadsheet application that offers users a variety of features and capabilities. One of the most popular features of Excel is the ability to create and run VBA macros.

A macro is a small scripts written in the VBA (Visual Basic for Applications) programming language that can be run in your desktop Excel app.

Macros can save you a lot of time and energy when working in Excel. They can be used to automate tedious tasks and save you hours of work each week.

But in order to leverage this time saving tool, you will need to know how to run your VBA macros.

Follow this guide and you’ll be able to start running macros like a pro in no time!

Run VBA Macro from the Developer Tab

The most common method for running a macro is from the Developer tab in the Excel ribbon.

This tab is hidden by default, so you will need to enable the Developer tab in your desktop Excel app first.

Follow these steps to run a VBA macro from the Developer tab.

  1. Go to the Developer tab.
  2. Press the Macros command in the Code section.

This will open the Macro menu which lists all the macros available to run.

  1. Select the macro which you want to run.
  2. Press the Run button.

That’s it! Your chosen macro code will now execute!

💡 Tip: Use the Macros in dropdown option to select the location of macros to run. You can select a specific workbook, All Open Workbooks, or This Workbook.

Run VBA from the View Tab

The Macro command is also available in the View tab.

Go to the View tab and press the Macros button to launch the Macros menu.

This opens the same Macro dialog box as before and you can select the macro and press the Run button.

Run VBA Macro from Macro Menu with a Keyboard Shortcut

There is an easier way to open the Macro menu! There’s no need to use the Developer or View tab since there is a dedicated keyboard shortcut to open the Macro menu.

You can use the Alt + F8 keyboard shortcut to open the Macro menu.

Run VBA Macro from a Keyboard Shortcut

You can entirely bypass the Macro dialog box by assigning a keyboard shortcut to your desired Macro.

If this is a macro that you want to use a lot, then assigning a shortcut is a good way to avoid the many clicks needed when running a macro through the Macro menu.

Follow these steps to assign your macro a dedicated keyboard shortcut.

  1. Open the Macro menu.
  2. Select the macro to which you want to assign a keyboard shortcut.
  3. Press the Options button.

This will open up the Macro Options menu where you can add a description for the macro and assign a keyboard shortcut.

  1. Add a character into the Shortcut key input box.
  2. Press the OK button in the Macro Options menu.
  3. Press the Cancel button in the Macros menu.

⚠️ Warning: This chosen shortcut key will override any existing keyboard shortcut, so you should avoid using keys taken by commonly used shortcuts such as copy, paste etc.

A lot of Ctrl and single key combinations are already taken with commonly used commands, so you might want to create a Ctrl + Shift shortcut instead.

💡 Tip: Hold the Shift key while entering a key in the Shortcut key input to create a Ctrl + Shift shortcut.

Run VBA Macro from a Form Control Button

If other people are using your spreadsheet solution, they might not realise they can run your macros to help complete their work.

This is where a button is the preferred choice to run a macro. It makes the act of running your macro easy and obvious!

You can create a Form Control Button to run the macro when you click the button. This can be placed anywhere in the spreadsheet since is floats over top of the grid. This means it won’t interfere with the rest of your data or formulas.

Follow these steps to insert a Form Control Button and assign a macro to it.

  1. Go to the Developer tab.
  2. Click on the Insert command.
  3. Choose the Button option found in the Form Controls section.

This will not actually insert a button yet. You will notice your cursor has now turned into a small black plus sign. This will allow you to draw a button in your sheet.

  1. Left click and drag anywhere in the sheet.

When you release the click and drag action, the Assign Macro menu will immediately pop up and you will be able to assign your macro to the button.

  1. Select your macro.
  2. Press the OK button.

Now you have a button in your sheet which will run your select VBA macro when clicked.

💡 Tip: Righ click on the button and select Edit Text to change the text displayed on the button.

Run VBA Macro from any Shape, Icon, or Image

Form Control buttons are pretty ugly and outdated. They also don’t have many options to customize the look, but thankfully they aren’t the only way to make a button to run your macros!

There are some much more stylish options like using an image, shape, or icon as a button to run your macros.

Follow these steps to assign a macro to any object such as an image, shape, or icon.

  1. Right click on the object.
  2. Select Assign Macro option from the menu.
  3. Select the macro from the Assign Macro menu.
  4. Press the OK button.

Now when you click on the shape, image, or icon it will execute the macro code!

Run VBA Macro from a Quick Access Toolbar Command

Another option is to add your most frequently used macros to the Quick Access Toolbar.

The Quick Access Toolbar is a customizable set of commands that are always visible so you can easily use them at any time. You can even add a macro so it can be run with a click.

Follow these steps to add a macro to your Quick Access Toolbar.

  1. Right-click anywhere on the Quick Access Toolbar.
  2. Select the Customize Quick Access Toolbar option from the menu.

This will open the Excel Options menu on the Quick Access Toolbar section.

  1. Select Macros from the Choose commands from dropdown.
  2. Select the macro you want to add to your Quick Access Toolbar.
  3. Press the Add button.
  4. Press the OK button.

When you press the Add button you will see the selected macro gets added to your list of commands. You can use the Up and Down arrow buttons to adjust the order this macro will appear in your commands.

💡 Tip: Press the Modify button in the Excel Options to change the icon and label of the macro that will appear in your Quick Access Toolbar!

Now you should have a new icon available in the Quick Access Toolbar. Click on this to run your select macro.

💡 Tip: An easy way to use the commands in your Quick Access Toolbar is with the Alt hotkey shortcuts. In this example, the command is in the 8th position starting from the undo command, so you can press the Alt + 8 to run the macro.

Run VBA Macro from a Custom Ribbon Command

If you have an entire repertoire of macros and you’re running out of room in the Quick Access Toolbar, then adding a custom ribbon tab to organize your macros could be the ideal solution.

Excel allows you to add your own custom ribbons and fill them with your favorite macros as well as any other commands you frequently use.

Follow these steps to add a macro to the Excel ribbon.

  1. Right click anywhere on the Excel ribbon.
  2. Select the Customize the Ribbon option from the menu.

This will open the Excel Options menu on the Customize Ribbon section.

  1. Press the New Tab button to create your new ribbon tab.
  1. Press the Rename button to give your tab a name.

Each tab will need at least one group, and this is automatically created when you create a new tab. You can also rename the group. Both of these names will be displayed in your Excel ribbon.

Now you will be able to add a macro into the new tab and group.

  1. Select the Macros option from the Choose commands from dripdown.
  2. Select the macro which you want to add into the ribbon.
  3. Press the Add button.
  4. Press the OK button.

💡 Tip: Select the tab and use the Up or Down arrow buttons to adjust the position of the new tab in your ribbon.

You now have a new custom tab that can hold all your most frequently used macros. 😃

Run VBA Macro from Visual Basic Editor Run Menu

The visual basic editor (VBE) is the environment where you write VBA code, so it makes sense that you should be able to also run your code from it.

A lot of people like to test their code as they develop their solutions and this means frequently running your macros from the the editor. Your current code can always be run from the Run menu in the VBE.

Follow these steps to run your macro from the Run menu in the visual basic editor.

  1. Select the macro you want to run.

You can select the macro by either placing the cursor in the code or selecting the macro name from the dropdown menu in the top right.

  1. Go to the Run menu.
  2. Select the Run Sub/UserForm option from the menu.

This will run your selected macro!

Run VBA Macro from Visual Basic Editor Toolbar

The visual basic editor comes with a toolbar for easy access to the most frequently used commands.

Follow these steps to run your macro from the toolbar.

  1. Select the macro you want to run.
  2. Press the Play button in the toolbar.

This will run your selected macro!

📝 Note: If you don’t see this toolbar you might need to enable it. Go to the View menu then Toolbars and check the Standard option.

Run VBA Macro from Visual Basic Editor Keyboard Shortcut

There are a lot of very useful keyboard shortcuts for using the visual basic editor.

Running a macro from the VBE is a very common task, so it’s no surprise there is also a keyboard shortcut available for this.

Press the F5 key while in the VBE and the currently selected macro will run!

Run VBA Macro from Another Macro

You can easily run a macro from a macro in Excel.

This is a good practice when it comes to programming. Creating smaller procedures and then reusing them within your main macro can be more efficient to run and easier to maintain the code.

Sub ExampleCode()
    MsgBox ("Hello world!")
End Sub

Sub MainCode()
    Call ExampleCode
    MsgBox ("Goodbye!")
End Sub

You can easily run any macro from within a macro using a single line of code. The above example will run the ExampleCode macro from the MainCode macro.

Run VBA Macro from a Worksheet Event

Did you know you can automatically run a macro?

You can automatically run a macro based on events that happen in your Excel worksheet!

For example, you can have a macro run anytime someone changes a value in the sheet.

Follow these steps to create a worksheet event-driven macro.

  1. Select the Sheet in which you want to trigger the macro. All your workbook sheets will be listed in the Microsoft Excel Object folder of the VBE Projects.
  2. Select the Worksheet options from the dropdown menu.
  3. Select the event type that should trigger your macro.
Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

When you select the type of event, it will insert a bit of code into the editor. For example, the Change event trigger will insert the above code.

  1. Place any code you want to run when the event occurs inside the generated code.

If you have an existing macro that you want to run, you can call it using a Call YourMacroName single line of code.

This macro will now run anytime you make changes in any cell within Sheet1.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then Call ExampleCode
End Sub

You might want to limit the macro to only running when the change event occurs in a particular cell or range. This is possible by setting conditions for the Target in your code.

In the above example, the ExampleCode macro will only be called when changes are made to cell A1.

Run VBA Macro from a Hyperlink

Did you know you can trigger your macros to run when you click on a hyperlink in Excel?

This is particular worksheet event method is worth its own mention!

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If Target.Address = "B2" Then Call ExampleCode
End Sub

The above code will run every time you click the hyperlink in cell B2 in Sheet2.

The code will execute and then take you to the hyperlinked address!

Run VBA Macro from a Workbook Event

There is also the possibility to automatically run a macro based on workbook events such as when you open or close the file.

This is a great option to make sure a task is performed before you do anything else in your workbook.

Follow these steps to run a macro automatically when you open your Excel file.

  1. Select ThisWorkbook found in the Microsoft Excel Object folder of the VBE Projects.
  2. Select Workbook from the dropdown menu.
  3. Select Open from the event type dropdown menu.
Private Sub Workbook_Open()

End Sub

This will insert the above code into the code editor. You can then add any code inside which you want run when you open the file. You can also call any macro here with the Call YourMacroName single line of code.

Conclusions

VBA macros can be used to automate your tasks in Excel, so it’s important you know how to run them.

There are many methods to run your desired macros depending on your situation.

You can use the Excel ribbon, a keyboard shortcut, or a customized quick access command when you want to manually run a macro. Also, you can attach your macros to run from buttons in the workbook to make it more user friendly.

You might need to occasionally run your VBA macros while developing your solutions to test them. This can be done several ways in the visual basic editor.

You can even trigger your macros based on certain worksheet or workbook events for the ultimate in automation.

Are you using macros in Excel. Did you know all these methods to run your macros? Do you know any others? Let me know in the comments below!

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and qualified actuary with over 15 years of experience. He has worked in a variety of industries, including insurance, ad tech, and most recently Power Platform consulting. He is a keen problem solver and has a passion for using technology to make businesses more efficient.

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃