9 Ways To Run Or Call Excel Vba Sub Procedure #07

In the previous Basic VBA guide series, the Excel class has discussed the steps to create a Sub Procedure in VBA Excel . In this series, we will learn about how to run or call the Sub Procedure that we have created.

This is important because if we don’t know how to run a sub procedure, the excel macro code that we have created for a particular purpose will certainly be useless.

But before that, you need to know that in programming terms, running a Sub Procedure is also known as Execute, Run or Call, it means executing or calling. These three terms in our discussion refer to the same purpose, namely to make a macro code run certain activities as instructed.

In VBA Excel there are various ways that you can do to execute, run or call a Sub Procedure. This VBA tutorial will explain at least 9 alternative ways that you can do to run a Sub Procedure.

How to Run VBA Sub Procedure From VBE (Visual Basic Editor)

The first way to run a Sub Procedure is via VBE or the Visual Basic Editor directly.

Although not the method that you might choose when a macro or complex VBA code is ready, it is the fastest method and you will often need when in the process of compiling sub-procedures. Running a sub procedure from VBE is usually used to perform debugging or test whether the Sub Procedure that you created is as desired or even certain errors appear.

The steps are as follows:

1. Make sure VBE (Visual Basic Editor) is active. If not, you can open it by selecting the Visual Basic menu located in the Developer TAB — Code Group or simply using the Shortcut . Alt + F11

2. The next step, please position the cursor on one part of the Sub Procedure that you want to run.

3. Run the Sub Procedure by selecting ” Run Sub/UserForm ” on the Run VBE menu or simply by pressing a key F5 on the keyboard.

Pretty easy isn’t it? After all, we have conveyed this method several times in previous series.

How to Run a VBA Sub Procedure From the Macro Dialog Box

The second way is to run the Sub Procedure using the macros menu or the Macro dialog box that you can find on the Excel Developer Tab .

You can choose this method with a note that the sub procedure that you are going to run does not have a certain argument. The steps are as follows:

1. On the Developer tab, select the Macro menu in the Code group to activate the Macro dialog box. Alternatively you can also use Shortcuts to activate this Macro dialog box. Alt + F8 

2. When the Macro dialog box appears, double click on the name of the procedure you want to call or please select the name of the procedure you want to run then select the Run menu . 

Note that the VBA procedure name that appears in this Macro dialog box is only Procedures that have a public scope . Procedures that have a private scope or are in an Excel Addin will not appear in the Macro name list . However, if you choose this method you can type the name of the procedure in the Macro name section and then click Run.

How to Run VBA Sub Procedures With Shortcuts

If you want you can also run a sub procedure with a specific keyboard shortcut or shortcut. Of course, before you can use it, you must first determine what shortcut you will use.

To set a Shortcut in a VBA procedure, the method is as follows:

1. Activate the Macro dialog box with a Shortcut or on the Macro menu button as before. Alt + F8
2. Then select a procedure name that will have a shortcut and click or select the options menu .
3. When the Macro Options dialog box appears, type the letter you want to make the shortcut for.
4. Click OK and done.

In addition to the combination you can also use to Shortcut this procedure. Ctrl + Letter  Ctrl + Shift + Letter

The trick when typing letters also press the key Shift. For example, if you want to use it as a shortcut, press the key and type the letter in the shortcut key at the same time. Ctrl + Shift + KShiftK

Be careful in determining the shortcut, because this shortcut will overwrite the default excel shortcut. For example, don’t use Ctrl+c, because maybe you will often need to copy activities in excel. To be on the safe side always use it if you choose to use this method to activate a procedure. Ctrl + Shift + Letter

How to call a sub procedure from another procedure

We can also call one or more sub procedures from other procedures.

There are three methods you can use this way.

Using Sub Procedure Name

The most common way is to directly write a line of code containing the name of the procedure you want to call.

For example in a module I create 3 procedures as follows:

Option Explicit

Sub contentA1()

     ‘ Write in Sheet1 Range A1

     Sheet1.Range(“A1”) = “Learn to Run Excel VBA Sub Procedure“

End Sub

Sub contentA2()

     ‘Write on Sheet1 Range A2

     Sheet1.Range(“A2”) = “Infact Daily”

End Sub

Sub contentA1A2()

     ‘Calling procedure contentA1

      ContentA1

     ‘Calling procedure contentA2

      ContentA2

End Sub

Pay attention to the third procedure or procedure with the name contentA1A2, where the procedure calls 2 other procedures, namely the contentA1 and contentA2 procedures.

Using Call Statements

This method is similar to the previous one, except that before the procedure name add text or the ” Call ” statement . Here’s a simple example.

Option Explicit

Sub contentA1()

     ‘ Write in Sheet1 Range A1

     Sheet1.Range(“A1”) = “Learn to Call Excel VBA Sub Procedures”

End Sub

Sub contentA2()

     ‘Write on Sheet1 Range A2

     Sheet1.Range(“A2”) = “Excel Class”

End Sub

Sub contentA1A2()

     ‘Calling procedure contentA1

      Call contentA1

     ‘Calling procedure contentA2

      Call contentA2

End Sub

Although the previous method is simpler, not a few also prefer this method. Because by using this Call Statement method when reading a VBA code that may have hundreds of lines, we can clearly see that a line of code is intending to call another procedure.

Using Application.Run Method

In addition to the previous two methods you can also use the Application.Run method to call an Excel VBA procedure.

Examples are as follows:

Option Explicit

Sub contentA1()

     ‘ Write in Sheet1 Range A1

     Sheet1.Range(“A1”) = “Learn Excel VBA”

End Sub

Sub contentA2()

     ‘Write on Sheet1 Range A2

     Sheet1.Range(“A2”) = “Excel Class”

End Sub

Sub contentA1A2()

     ‘Calling procedure contentA1

      Application.Run “contentA1”

     ‘Calling procedure contentA2

     Application.Run “contentA2”

End Sub

Next time we will discuss separately about the last method with this Application.Run Method .

How to Call a Sub Procedure From the Excel Ribbon

In addition to standard excel menus, we can also add a call button to run a VBA procedure. The method is as follows:

1. Right-click anywhere on the excel ribbon then select the ” Customize the Ribbon ” menu .
2. After the Customize the Ribbon option appears, create a new custom group as a container or a place to place the procedure call menu.
3. Next in the ” Choose commands from ” dropdown section and select the ” Macros ” menu .
4. Then you just choose which procedure you want to add to the excel ribbon by clicking the Add menu and OK to finish

How to Call Sub Procedure From Quick Access Toolbar

In addition to the Excel Ribbon section, you can also add a VBA sub procedure call button to the ” Quick Access Toolbars (QAT) “. You can learn more about QAT on the following page: Quick Access Toolbar in Microsoft Excel

The steps to add a menu or procedure call button to QAT are as follows:

1. Right-click on the Ribbon or QAT, then select the ” Customize Quick Access Toolbar ” menu .
2. After the Customize the Quick Access Toolbars option appears, in the ” Choose commands from ” dropdown section and select the ” Macros ” menu .
3. Next you just choose which procedure you want to add to the Excel ribbon by clicking the Add menu and OK to finish

How to Run VBA Sub Procedure When Certain Events/Activities Occur

Sub procedures in Excel VBA can also be called or run automatically when a certain event occurs.

Events or activities here, for example, when we open a workbook, when a certain worksheet is active, when a cell is active and so on.

The discussion on this matter will be quite long so we will review it on a separate page another time.

The following is an example of a sub procedure that will be active when a certain event occurs.

Option Explicit

Private Sub Worksheet_Activate()

     MsgBox “You are on Sheet1”

End Sub

Private Sub Worksheet_Deactivate()

     MsgBox “You Moved from Sheet1 to Another Sheet”

End Sub

If you want to try it put the above vba code on the Sheet1 module then try switching between sheets.

How to Execute Sub Procedure of Immediate Window VBE

in the VBE discussion series, it has been explained how to show and hide the ” Immedite Window “. So I don’t think it’s necessary to explain how to display the immediate window in Excel’s VBE (Visual Basic Editor) .

To run a sub procedure through the ” Immediate Window ” is quite easy. Just type the name of the procedure to be called then press ENTER.

How to Run a VBA Sub Procedure Using a Button or Other Object

In addition to the methods above, one of the commonly used ways to call and run a sub procedure is to use a certain key.

You can create commonly used buttons from the Button menu on the controls or activeX Controls form . You can also call sub procedures of excel objects such as shapes, text boxes, clip art, SmartArt, WordArt, charts and pictures .

How to use it is relatively easy as follows:

1.  Insert the button or object you want.

2. Set the position, layout and appearance of the button or excel object.

3. Next, right-click on the button that you created and select the ” Assign Macro ” menu . Insert excel form control button

4. Next, the Assign Macro dialog box will appear .

5. Determine which procedure will be selected when the button is clicked then select OK. Calling the procedure with the excel key

6. You can also create a new subprocedure by clicking the ” New ” menu or selecting the ” Record ” menu to record a new activity that will result in a new sub procedure.

Of the 9 ways to call or run the sub procedure above, which method have you never used?

Finally, I believe you are wise enough to choose and determine which method you think is appropriate and according to your needs. If something is still unclear, don’t hesitate to ask in the comment box provided. And don’t hesitate to share this VBA excel tutorial article so that more of your friends will benefit.