4.8 C
New York
Tuesday, February 7, 2023

Buy now

Run a VBA sub procedure in Microsoft Excel

Visual Basic code list.
Image: PatrickCheatham/Adobe Stock

Once you dip your toe in the VBA waters, you will realize how powerful and flexible VBA is. Not only is the language flexible, but so are the ways you run a VBA sub procedure, Microsoft Excel are plentiful and none of the methods are complex. This flexibility means you can provide users and yourself with the most efficient way to perform procedures within the context of the task.

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

SEE: Google Workspace vs. Microsoft 365: a side-by-side analysis with checklist (TechRepublic Premium)

I use Microsoft 365 on a Windows 10 64-bit system, but you can use earlier versions of Excel. Excel for the web does not support VBA. Although I use 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 drugs, it’s for your own good, but you don’t have to like it.

Because VBA procedures can pose a security risk, you should not enable macros in an Office app unless you are sure it is secure. You don’t need to enable VBA to view and edit the code. However, you must enable VBA to run the code.

You can find these settings in the Trust Center. If you do not have access to these settings, please contact your administrator. You can’t do anything 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 Trust Center Settings.

4. In the left pane, click Macro Settings.

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

Image A

Use this setting to run VBA procedures.
Use this setting to run VBA procedures.

This is a setting that the person using the file must set. Now we are ready to run VBA procedures.

Method one: Run a VBA sub procedure from Developer and View tabs in Excel

Excel’s .xlsx ribbon has been with us for a long time, but you may not be familiar with the Developer tab because it’s not available by default. You 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 in the left pane.

3. Click Customize Ribbon in the left pane.

4. In the list of main tabs, select Developer (Figure B).

Figure B

Check the Developer tab.
Check the Developer tab.

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

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

Figure C

Choose the procedure you want to perform.
Choose the procedure you want to perform.

This method only allows access to subprocedures in the current file and Personal.xlsb. To open the VBE without running 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: Run a VBA sub procedure by clicking a button in Excel

Another group on the Developer tab is the Controls group, which contains a button control. You can associate the procedure with a button and a quick click will perform the procedure.

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

1. Click the Developer tab.

2. In the Controls group, click the Insert drop-down list, and then click the first option, Button.

3. Drag in the sheet to position the button. Doing so displays the Assign Macro dialog box.

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

Figure D

Connect the button to the VBA procedure you want to run.
Connect the button to the VBA procedure you want to run.

5. Click OK.

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

Click the button and it will perform the procedure which will display a message box saying “Hello” as shown in Figure E.

Figure E

Click the button to perform the procedure.
Click the button to perform the procedure.

Method Three: Run a VBA Sub-procedure with a Keyboard Shortcut in Excel

Shortcuts provide shortcuts to routine tasks, and most of us have a few favorites. Mine is Ctrl + Z or Undo. I use it many times during 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 box, select the procedure and then click Options.

4. Enter the character from the keyboard you will be using with the Ctrl key (Figure F). I entered Z.

Figure F

Enter the keyboard character that completes the shortcut.
Enter the keyboard character that completes the shortcut.

5. Click OK.

6. Click Cancel to close the Macros dialog box.

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

This method comes with a small caveat: if you enter a character in step 4 that is already in use with a keyboard shortcut, you will cancel the existing shortcut and Excel will not warn you. You may want to test your combination ahead of time to make sure you don’t overwrite something you could miss later.

If you overwrite something that you want back, delete the shortcut the same way you created it, but remove the character in step 4. Doing so allows Excel to reuse the original shortcut.

Method Four: Run a VBA Sub-procedure by Clicking a Shape in Excel

Office apps provide several forms that you can use to perform a VBA procedure, similar to the second method described above. You simply place the mold 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 drop-down list and choose Shapes.

3. Click the shape you want to display as a ‘macro button’. I chose an oval.

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

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

Figure G

Choose Assign Macro from the submenu.
Choose Assign Macro from the submenu.

6. Select the macro in the resulting dialog box.

7. From the drop-down list, choose Macros in This Workbook (Figure H).

figure H

Choose the VBA procedure.
Choose the VBA procedure.

To perform the procedure, just click on the shape.

There are many ways to execute a VBA sub procedure. You cannot use these methods to perform 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 Add Office Macros to the QAT Quick Access Toolbar.

Source link

Related Articles


Please enter your comment!
Please enter your name here

Stay Connected


Latest Articles