Excel VBA: Call a Subroutine

Details

What is Subroutine?

A subroutine is a piece of code that performs a specific task and does not return a result. Subroutines are used to break down large pieces code into small manageable parts.

Let's say you have created a user interface with text boxes for accepting user input data. You can create a subroutine that clears the contents of the text boxes. A subroutine is appropriate in such a scenario because you do not want to return any results.

In this tutorial, you will learn-

Why use subroutines

  • Break code into small manageable code: An average computer program has thousands and thousands of source code lines. This introduces complexity. Subroutines help solve this problem by breaking down the program into small manageable chunks of code.
  • Code reusability. Let's say you have a program that needs to access the database, almost all of the windows in the program will need to interact with the database. Instead of writing separate code for these windows, you can create a function that handles all database interactions. You can then call it from whichever window you want.
  • Subroutines and functions are self-documenting. Let's say you have a function calculateLoanInterest and another that says connectToDatabase. By just looking at the name of the subroutine/function, the programmer will be able to tell what the program does.

Rules of naming subroutines and functions

To use subroutines and functions, there are set of rules that one has to follow.

  • A subroutine or function name cannot contain space
  • A subroutine or function name should start with a letter or an underscore. It cannot start with a number or a special character
  • A subroutine or function name cannot be a keyword. A keyword is a word that has special meaning in VBA. Words like Private, Sub, Function, and End, etc. are all examples of keywords. The compiler uses them for specific tasks.

Subroutine practical example

In this section, we are going to;

  1. Look at the syntax of subroutines in VBA
  2. Create a simple program that uses a subroutine. It should display a message box when a user clicks on a command button.

You will need to enable the Developer tab in Excel to follow along with this example. If you do not know how to enable the Developer tab then read the tutorial on VBA Operators

VBA Syntax for declaring subroutines

Private Sub mySubRoutine(ByVal arg1 As String, ByVal arg2 As String)
    'do something
End Sub

HERE in the syntax,

Code

Action

  • "Private Sub mySubRoutine(…)"
  • Here the keyword "Sub" is used to declare a subroutine named "mySubRoutine" and start the body of the subroutine.
  • The keyword Private is used to specify the scope of the subroutine
  • "ByVal arg1 As String, ByVal arg2 As String" :
  • It declares two parameters of string data type name arg1 and arg2
  • "End Sub"
  • "End Sub" is used to end the body of the subroutine

The following subroutine accepts the first and last name and displays them in a message box.

Now we are going to program and execute this whole process for the subroutine. Let see this.

Sub-routines demonstrated with Example:

  1. Design the user interface and set the properties for the user controls.
  2. Add the subroutine
  3. Write the click event code for the command button that calls the subroutine
  4. Test the application

Step 1) User Interface

Design the user interface as shown in the image below

VBA Functions and Subroutine

Set the following properties. The properties that we are setting

S/N

Control

Property

Value

1

CommandButton1

Name

btnDisplayFullName

2

Caption

Fullname Subroutine

Your interface should now look as follows

VBA Functions and Subroutine

Step 2) Add subroutine

  1. Press Alt + F11 to open the code window
  2. Add the following subroutine
Private Sub displayFullName(ByVal firstName As String, ByVal lastName As String)
    MsgBox firstName & " " & lastName
End Sub

HERE in the code,

Code

Actions

  • "Private Sub displayFullName(…)"
  • It declares a private subroutine displayFullName that accepts two string parameters.
  • "ByVal firstName As String, ByVal lastName As String"
  • It declares two parameter variables firstName and lastName
  • MsgBox firstName & " " & lastName"
  • It calls the MsgBox built-in function to display a message box. It then passes the 'firstName' and 'lastName' variables as parameters.
  • The ampersand "&" is used to concatenate the two variables and add an empty space between them.

Step 3) Call the subroutine from the command button click event.

  • Right click on the command button as shown in the image below. Select View Code.
  • The code editor will open

VBA Functions and Subroutine

Add the following code in code editor for the click event of btnDisplayFullName command button. 

Private Sub btnDisplayFullName_Click()
    displayFullName "John"
End Sub

Your code window should now look as follows

VBA Functions and Subroutine

Save the changes and close the code window.

Step 4) Testing the code

On the developer toolbar put the design mode 'off'. As shown below.

VBA Functions and Subroutine

Step 5) Click on the command button 'FullName Subroutine'.

You will get the following results

VBA Functions and Subroutine

Download the above Excel Code

Summary:

  • A subroutine is a piece of code that performs a specific task. A subroutine does not return a value after execution
  • Subroutines offer code reusability
  • Subroutines help break down large chunks of code into small manageable code.

 

YOU MIGHT LIKE: