VBA Subroutine in Excel – How to Call Sub in VBA?
When a specified action is performed on a worksheet with the help of a collection of code known as a VBA Subroutine. It also helps to read an external file, also it can open other applications from Excel. A large piece of code can be broken into small parts so that we can manage it easily. Let’s learn why to use submarines:
- Converts large piece of codes into small parts so that the computer ignores all kind of complexities that arises because of large codes
- Reusability of code suppose we in a program have to access the database frequently so instead of writing the code again and again we can create a function to access the database
- Subroutines are self-documenting functions which means a coder can easily say what the program does by looking into the name of the function
Naming Rules of Subroutines
- It can start with a letter or an underscore but it cannot start with a number or a special character.
- It cannot contain any space in the name.
- The name of the subroutine cannot be a keyword like Private, Sub, End, etc.
Syntax
Private Sub function_name( ByVal arg1 As String, ByVal arg2 As String)
End Sub
Syntax Explanation
|
Code |
Action |
|---|---|
| “Private Sub function_name(…)” | Private is the keyword which is representing the scope of the subroutine. The name of the subroutine i.e function_name will be mentioned after the Sub keyword which starts the body of the subroutine. |
| “ByVal arg1 As String, ByVal arg2 As String” | Two parameters of data type String are declared whose names are arg1 and arg2. |
| “End Sub” | The body of the subroutine is ended. |
How to call Sub in VBA?
Step 1: Select the developer tab and click Insert drop-down list from the control box.
Step 2: Choose the command button from ActiveX Controls Box.
Step 3: Draw the command button in the excel sheet.
Step 4: Press Alt + F11 to open the VBA code.
Step 5: Write the following subroutine with the name “display” which will display a name in the message box once its function gets called.
Step 6: To call the subroutine we need to right-click on the command button and then select view code.
Step 7: Write the following code to call the display function by clicking the command button.
The following code displays the overall code of the program.