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:
- Activate the Macro dialog box with a Shortcut or on the Macro menu button as before. Alt + F8
- Then select a procedure name that will have a shortcut and click or select the options menu .
- When the Macro Options dialog box appears, type the letter you want to make the shortcut for.
- 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:
- Right-click anywhere on the excel ribbon then select the ” Customize the Ribbon ” menu .
- After the Customize the Ribbon option appears, create a new custom group as a container or a place to place the procedure call menu.
- Next in the ” Choose commands from ” dropdown section and select the ” Macros ” menu .
- 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:
- Right-click on the Ribbon or QAT, then select the ” Customize Quick Access Toolbar ” menu .
- After the Customize the Quick Access Toolbars option appears, in the ” Choose commands from ” dropdown section and select the ” Macros ” menu .
- 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.
Stanley Sanchez is a freelance writer, editor, and blogger for hire. He has 8 years of experience in copywriting and editing, with a focus on web content development, SEO promotions, social media marketing, and the production of blogs. He specializes in teaching blog writers how to express their stories through words. In his spare time, he enjoys reading about science and technology.