How to execute a VBA sub procedure in Microsoft Excel

Once you stick your toe into the VBA waters, you’ll realize how powerful and flexible VBA is. Not only is the language flexible, but the ways you execute a VBA sub procedure in Microsoft Excel are plentiful, and none of the methods are complex. What this flexibility means is that you can offer users and yourself the most efficient way to execute procedures within the context of the task.

In this tutorial, I’ll show you four ways to execute a VBA procedure in Microsoft Excel. First, we’ll discuss the Microsoft Trust Settings so procedures run when executed. Throughout this tutorial, I will use the term “procedure” and “sub procedure” instead of “macro,” although you will often see them used interchangeably. However, function procedures should never be referred to as a macro.

SEE: Google Workspace vs. Microsoft 365: A side-by-side analysis w/checklist (TechRepublic Premium)

I’m using Microsoft 365 on a Windows 10 64-bit system but you can use earlier versions of Excel. Excel for the web doesn’t support VBA. Although I’m using Excel for demonstration purposes, some of these methods are available in other Office apps. You can download the Microsoft Excel and .cls demonstration files.

How to control when Excel runs VBA procedures

The biggest problem with VBA these days is all the built-in protection. Like taking icky medicine, it’s for your own good, but you don’t have to like it.

Because VBA procedures can pose a security risk, don’t enable macros in an Office app unless you’re sure it’s safe. You don’t have to enable VBA to view and edit the code. However, you will have to enable VBA if you want to run the code.

You’ll find these settings in the Trust Center. If you can’t access these settings, contact your administrator. There’s nothing you can do to run VBA if the administrator has disabled it. You must change these settings for each Office app individually.

To view or change your procedure settings, do the following:

1. Click the File tab.

2. Click Options in the left pane.

3. Click Trust Center and then click Trust Center Settings.

4. In the left pane, click Macro Settings.

5. Click the Disable VBA macros with notification options. This setting blocks procedures but allows you to enable them on a case-by-case basis (Figure A).

Figure A

This is a setting that the person using the file must set. Now we’re ready to execute VBA procedures.

Method one: How to run a VBA sub procedure from the Developer and View tabs in Excel

Excel’s .xlsx ribbon has been with us for a long time, but you might not know about the Developer tab because it isn’t available by default. You’ll use this tab to interact with VBA procedures and the Visual Basic Editor. If you don’t see the Developer tab, do the following to add it:

1. Click the File tab.

2. Choose Options from the left pane.

3. Click Customize Ribbon in the left pane.

4. Under the list of Main Tabs, select Developer (Figure B).

Figure B

5. Click OK. The Developer tab is between the View and the Help tabs.

To execute a VBA sub procedure from the Developer tab, click Macros in the Code group. In the resulting dialog, shown in Figure C, choose the procedure you want to run and click Run.

Figure C

This method provides access only to sub procedures in the current file and Personal.xlsb. To open the VBE without running any code, click the Visual Basic option in the Code group.

You can use the View tab to do the same: Click the View tab and then click Macros in the Macros group.

Method two: How to run a VBA sub procedure by clicking a button in Excel

Another group on the Developer tab is the Controls group, which includes a button control. You can attach the procedure to a button and a quick click executes the procedure.

To add a button control to a sheet, do the following:

1. Click the Developer tab.

2. In the Controls group, click the Insert dropdown and then click the first option, Button.

3. Drag in the sheet to position the button. Doing so will display the Assign Macro dialog.

4. Select the macro you want this button to execute (Figure D).

Figure D

5. Click OK.

6. With the button still selected, replace the button text with “Say Hello.”

Click the button and it runs the procedure that displays a message box with the text “Hello,” as shown in Figure E.

Figure E

Method three: How to run a VBA sub procedure using a keyboard shortcut in Excel

Keyboard shortcuts offer shortcuts to routine tasks, and most of us have a few favorites. Mine is Ctrl + Z, or Undo. I use it many times throughout the day. You can easily assign a shortcut to a VBA sub procedure as follows:

1. Click the Developer tab.

2. In the Code group, click Macros.

3. In the resulting dialog, select the procedure and then click Options.

4. Enter the character from the keyboard that you’ll use with the Ctrl key (Figure F). I entered Z.

Figure F

5. Click OK.

6. Click Cancel to close the Macros dialog box.

To execute the VBA procedure, press Ctrl + Z, or whatever character you entered in Step 4.

This method comes with a small warning: If you enter a character in Step 4 that’s already in use with a keyboard shortcut, you will cancel the existing shortcut, and Excel won’t warn you. You might want to test your combination beforehand to ensure you don’t overwrite something you might miss later.

If you do overwrite something you want back, simply remove the shortcut the same way you created it but delete the character in Step 4. Doing so will allow Excel to use the original shortcut again.

Method four: How to run a VBA sub procedure by clicking a shape in Excel

Office apps offer several shapes that you can use to execute a VBA procedure, similar to the second method outlined above. You simply insert the shape and then connect the procedure to it. Let’s try that now:

1. Click the Insert menu.

2. In the Illustrations group, click the Illustrations dropdown and choose Shapes.

3. Click the shape you want to represent a “macro button.” I chose an oval.

4. Click inside the sheet and drag to size and position this shape.

5. Right-click the shape and choose Assign Macro from the resulting submenu (Figure G).

Figure G

6. In the resulting dialog, select the macro.

7. From the Macros In dropdown, choose This Workbook (Figure H).

Figure H

To execute the procedure, simply click the shape.

There are many ways to run a VBA sub procedure. You can’t use these methods to run function procedures.

You can also run VBA procedures from the Quick Access Toolbar and a custom group on the ribbon. To learn more about these methods, read How to add Office macros to the QAT toolbar for quick access.