Excel VBA Subroutine: How to Call Sub in VBA with Example

By : Charlotte Miller Charlotte Miller
Hours Updated

⚡ Smart Summary

Excel VBA Subroutine groups a block of code that performs a task without returning a value. This page covers naming rules, Sub syntax, calling a Sub from a command button, and how a Sub differs from a Function.

  • 📦 Definition: A Subroutine performs a specific task and returns no value to the code that called it.
  • ♻️ Reusability: One Subroutine can be called many times from anywhere in the project.
  • 🔤 Naming Rules: A name contains no spaces, starts with a letter or underscore, and is never a VBA keyword.
  • 🧾 Syntax: Private Sub name(ByVal arg As String) opens the block and End Sub closes it.
  • 🖱️ Calling: A command button Click event calls the Subroutine and passes its arguments.
  • ▶️ Running Directly: F5, the Macros dialog, or the Call keyword all run a Subroutine without a button.
  • 🔁 Sub or Function: Choose a Function when a value must be returned, otherwise choose a Sub.
Read More

Excel VBA Subroutine

What is a Subroutine in VBA?

A Subroutine in VBA is a piece of code that performs a specific task described in the code but does not return a result or a value. Subroutines are used to break down large pieces of code into small manageable parts. Subroutines can be recalled multiple times from anywhere in the program.

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 VBA Call Subroutine is appropriate in such a scenario because you do not want to return any results.

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.

Before writing one, the compiler expects the name to follow a short set of rules.

Rules of naming Subroutines and Functions

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

  • A subroutine or VBA call function name cannot contain space
  • An Excel VBA Call Sub 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.

With a valid name chosen, the declaration itself follows a fixed pattern.

VBA Subroutine Syntax

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 Controls in Excel

HERE in the syntax,

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

Syntax explanation

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 Sub Procedure. Let see this.

RELATED ARTICLES

How to Call Sub in VBA

Below is a step by step process on how to Call Sub in VBA:

  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.

How to Call Sub in VBA

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.

How to Call Sub in VBA

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) Calling the subroutine

Calling 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

How to Call Sub in VBA

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

Private Sub btnDisplayFullName_Click()
    displayFullName "John", "Doe"
End Sub

Your code window should now look as follows

How to Call Sub in VBA

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.

How to Call Sub in VBA

Step 5) Click on the command button ‘FullName Subroutine’.

You will get the following results

How to Call Sub in VBA

Download the above Excel Code

A button is convenient, but it is not the only way to start a Subroutine, and during development it is rarely the fastest.

How to Run a Subroutine Without a Button

While you are still writing code, adding a control to the sheet just to test a Subroutine wastes time. VBA offers four other ways to run one, and each suits a different moment in the development cycle.

  • Press F5 in the editor: Place the cursor anywhere inside the Sub and press F5. It runs immediately. This only works when the Sub takes no arguments, because VBA has no values to supply.
  • Step through with F8: The same run, one line at a time, so you can watch each variable change in the Locals window. Use it when a result is wrong and you need to see where.
  • Use the Macros dialog: On the Developer tab, click Macros, pick the name, and click Run. Only Public Subs without arguments appear in this list, which is why a helper Sub is usually declared Private.
  • Call it from another Sub: This is the method used for any Sub that takes arguments, and the one the command button above relies on.

The last option has two accepted forms. Writing the name followed by bare arguments works, and so does the explicit Call keyword, but the bracket rule differs between them.

Sub RunTheGreeting()
    ' Form 1: no Call keyword, no brackets
    displayFullName "John", "Doe"

    ' Form 2: Call keyword, brackets required
    Call displayFullName("Jane", "Roe")
End Sub

Both lines do exactly the same thing. Mixing the two forms, by writing brackets without the Call keyword, is the single most common cause of the “Expected: =” compile error when a Sub takes more than one argument.

Difference Between a Sub and a Function in VBA

Sub and Function are the two procedure types in VBA, and beginners often pick the wrong one. The deciding question is simple: does the calling code need a value back?

Feature Sub Function
Returns a value No Yes, assigned to the function name
Declared with Sub … End Sub Function … End Function
Called as A statement on its own line Part of an expression, for example x = myFunc(2)
Usable in a worksheet cell No Yes, as a user defined function
Typical use Format a sheet, clear inputs, show a message Calculate a total, convert a value, look data up

Use a Sub when the code acts on the workbook, and a VBA Function when the code produces an answer that something else consumes.

FAQs

A Private Sub can be called only from inside the module that declares it. A Public Sub is visible to every module in the project and is the only kind that appears in the Macros dialog.

ByVal passes a copy, so the Subroutine cannot alter the caller’s variable. ByRef passes the variable itself, so any change is visible to the caller. ByRef is the default when neither keyword is written.

Use the Exit Sub statement. It stops the procedure at that point and returns control to whatever called it, which is useful for leaving early when a validation test fails.

Yes. Paste the macro and an AI assistant groups related lines, suggests a descriptive name for each new Sub, and rewrites the original as a short sequence of calls. Test the result on a copy of the workbook.

Yes. Give it the error text and the calling line, and an AI assistant identifies causes such as brackets used without the Call keyword, a wrong argument count, or a mismatched data type, then supplies the corrected line.

Summarize this post with: