Excel VBA - Intro to Sub Procedures

November 08, 2017

As you continue your development in Excel VBA, it’s crucial to understand how Sub Procedures work. They are the building blocks of your VBA code and knowing how to work with them efficiently will help you make much better VBA solutions. In today’s post, we dig into what Sub Procedures are, how to call them, passing parameters, and we’ll also cover best practices.

Let’s get started.

What is a Sub Procedure?

Think of a sub procedure as the container for your macro. Each sub procedure can be thought of as its own macro. They can also modify the workbook’s contents which is different than VBA function procedures (or simply, functions) where they can pretty much only pass back a value (more on functions in a future post).

You can give data to sub procedures to work on by passing in parameters when calling the it.

A sub procedure is also called a subroutine.

Does this article help you? If so, please consider supporting me with a coffee ☕️

Buy Me a Coffee at ko-fi.com

Syntax for Creating Sub Procedures

The basic syntax for a sub procedure looks like this:

' Public Sub Procedure
Public Sub TestSub()
  ' code goes here
End Sub

' Private Sub Procedure
Private Sub TestSub()
  ' code goes here
End Sub

' Public Sub Procedure - notice that the default is Public
Sub TestSub()
  ' code goes here
End Sub

The examples above show different ways we can declare the subroutine. Typically, you will either use Public or Private. There are other types, but they are less-often used and we can dig into those in a future post.

Notice that the default visibility is Public. I always explicitly declare the sub procedure’s visibility so it’s very clear what’s going on. In my examples, you will probably always see me put Public or Private.

What Does Public and Private Mean?

The Public and Private accessibility modifiers are used to either make your sub procedures visible in all modules (Public) or only visible within the current module (Private).

Public Accessibility Modifier

The Public accessibility modifier allows the subroutine to be visible throughout all modules in the workbook. Here’s an example where we have 2 modules (Module1 and Module2) where the sub procedure in Module1 calls the sub procedure in Module2:

' In Module1
Option Explicit

Public Sub Module1Test()
    Call Module2Test
End Sub

' ----------------
' In Module2
Option Explicit

Public Sub Module2Test()
    Debug.Print "I'm in module 2"
End Sub

Here is the output of that code:

Module1 calls Module2 sub procedure

The Public accessibility modifier also allows your macro to be called from the workbook itself, as long as the module does not have the Option Private Module statement listed.

Here are two sub procedures in Module1. One of them is public and the other is private. When we go to the Developer tab and click on Macros, we only see the public one.

Option Explicit

Public Sub MyPublicSub()
    MsgBox "Hello!"
End Sub

Private Sub MyPrivateSub()
    MsgBox "Hi there!"
End sub

Public sub procedures are visible to end users

Private Accessibility Modifier

The Private accessibility modifier allows your sub procedure to be visible to all other sub procedures, but only with the module that it lives in.

Also, as you saw in the previous section, private subroutines don’t show up to the end user when they click on the Macros button in the workbook.

Why is this useful?

If you have a sub procedure that does a lot of work, it would be best to split that work up into smaller bits by creating other sub procedures. However, you wouldn’t want those smaller bits be able to be called from the end user because maybe you have to call all of these smaller sub procedures in a specific order, or maybe you don’t want to confuse the end user with lots of subroutines to choose from. The Private accessibility modifier allows you to hide those smaller bits.

Let’s take a quick look at calling a private sub procedure from within the same module.

Option Explicit

Public Sub MyPublicSub()
    Call DoSomeWork
    Call MyPrivateSub
End Sub

Private Sub DoSomeWork()
    Debug.Print "Work work work work work"
End Sub

Private Sub MyPrivateSub()
    MsgBox "Work complete!"
End Sub

Here, we have a public subroutine that we’ll display to the end user. This calls a private subroutine called DoSomeWork that outputs some text to the Immediate Window. Then it calls MyPrivateSub to display a message to the end user.

Calling private sub procedures

Also, if you try to call a private subroutine that lives in another module, you will get an error:

' In Module1
Option Explicit

Public Sub MyPublicSub()
    Call Module2PrivateSub
End Sub

' ---------------------
' In Module2
Option Explicit

Private Sub Module2PrivateSub()
    Debug.Print "In Module2PrivateSub"
End Sub

Calling a private sub procedure in another module

Calling a Sub Procedure From Another Sub Procedure

Calling a sub procedure from another one is pretty straightforward. There are two ways that you can call another subroutine from another:

Option Explicit

Public Sub MyPublicSub()
    Call MyPrivateSub ' use the Call keyword
    MyPrivateSub      ' just call the sub procedure
End Sub

Private Sub MyPrivateSub()
    Debug.Print "In MyPrivateSub"
End Sub

At first glance, you might think that the Call keyword doesn’t really make a difference. However, there are 2 things to note:

  1. The Call keyword lets you know that another sub procedure is being called rather than a variable.
  2. The Call keyword allows you to use parenthesis when passing parameters to another subroutine.

Take a look at how to pass parameters to other sub procedures:

Option Explicit

Public Sub MyPublicSub()
    Call MyPrivateSub("John", 28)
    MyPrivateSub "John", 28
End Sub

Private Sub MyPrivateSub(name As String, age As Long)
    Debug.Print name + " is " & age & " years old."
End Sub

With the Call keyword, you can specify parenthesis. If you’re familiar with other programming languages (like C#) you might feel more comfortable with this.

Speaking of passing parameters to your subroutine, let’s go over that.

Passing Data to Your Sub Procedures

As you do more development with macros, you will definitely want your sub procedures work with data. The best way to do that is by passing in data to your subroutines. Yes, you can technically share global variables across subroutines, but honestly, it’s just not good practice in my opinion. Stick to passing parameters to your subroutines. You’ll find maintaining your code much easier later on.

So how can we pass parameters to our sub procedures? There are a few ways you can approach this, the main two being with ByVal and ByRef.

Passing Parameters with ByVal

When passing parameters with ByVal (short for By Value), this means that the argument that is passed in is a copy of the data that was passed to it. Any changes to the argument variable will be lost after the procedure finishes.

For example, let’s say we have this code:

Option Explicit

Public Sub DoWork()
    Dim name As String
    name = "John"

    Call PrintName(name)
    Debug.Print "DoWork: " & name
End Sub

Private Sub PrintName(ByVal name As String)
    name = "Mr. " & name
    Debug.Print "PrintName: " & name
End Sub

' Output
' PrintName: Mr. John
' DoWork: John

Here, we call a sub procedure with an argument and change that argument within the called subroutine. However, notice that the variable that we passed in does not change after the private subroutine finishes.

PLEASE NOTE: even though we named our parameter name which is the same as the variable name in our public subroutine, they are not connected to each other by their variable name. We can accomplish the exact same thing with the following code:

Option Explicit

Public Sub DoWork()
    Dim name As String
    name = "John"

    Call PrintName(name)
    Debug.Print "DoWork: " & name
End Sub

Private Sub PrintName(ByVal someName As String)
    someName = "Mr. " & someName
    Debug.Print "PrintName: " & someName
End Sub

' Output
' PrintName: Mr. John
' DoWork: John

Passing Parameters with ByRef

When passing parameters with ByRef (short for By Reference), this means that the variable that you pass to another subroutine will get a copy of the address in memory to that variable. What that means is that the argument that is passed into the sub procedure has the ability to modify the calling variable’s data.

Let’s demonstrate this with an example:

Option Explicit

Public Sub DoWork()
    Dim name As String
    name = "John"

    Call PrintName(name)
    Debug.Print "DoWork: " & name
End Sub

Private Sub PrintName(ByRef someName As String)
    someName = "Mr. " & someName
    Debug.Print "PrintName: " & someName
End Sub

' Output
' PrintName: Mr. John
' DoWork: Mr. John

Here, we used the same code as in the ByVal section above, but we changed the parameter to use the ByRef keyword. Now when we change the data, it’s reflected back in the calling subroutine.

The ByRef keyword can save you a lot of memory. For example, if you have to pass a very large string to another sub procedure, using ByVal will cause your code to create a copy of the entire string. This can quickly consume a lot of memory if you’re not careful. However, you also need to be careful when handling variables passed in with ByRef because if you inadvertently change the data and you didn’t mean to, you can cause bugs in your own code.

Declaring Optional Parameters and Setting Defaults

If you would like to create an optional parameter, you can put the Optional keyword before the parameter name like so:

Option Explicit

Public Sub DoWork()
    Call PrintNumber
End Sub

Private Sub PrintNumber(Optional ByVal number As Long)
    Debug.Print number
End Sub

' Output
' 0 (this is because the default for Long is 0)

This shows that you do not have to specify a parameter if you do not want to.

You can also specify a default for the optional parameter like so:

Option Explicit

Public Sub DoWork()
    Call PrintNumber
End Sub

Private Sub PrintNumber(Optional ByVal number As Long = 10)
    Debug.Print number
End Sub

' Output
' 10

I would recommend always setting a default if possible.

Also, if you need to check if that variable was passed in, you can use the IsMissing() function.

Option Explicit

Public Sub DoWork()
    Call PrintNumber
End Sub

Private Sub PrintNumber(Optional ByRef number As Variant)
    If (IsMissing(number)) Then
        Debug.Print "number was not passed"
    Else
        Debug.Print number
    End If
End Sub

' Output
' number was not passed

Note that this only works when you have an optional parameter of the type Variant.

Some Best Practices

Let’s close off this post with some best practices to remember:

  • Always declare Public or Private on your sub procedures
  • Always declare ByVal or ByRef for your parameters
  • Use ByRef to help you save memory in your macros, but beware of modifying that parameter’s data
  • Always specify a default value for optional parameters if possible

Wow, you read the whole article! You know, people who make it this far are true learners. And clearly, you value learning. Would you like to learn more about Excel? Please consider supporting me by buying me a coffee (it takes a lot of coffee to write these articles!).

Buy Me a Coffee at ko-fi.com

Written by Joseph who loves teaching about Excel.

© 2022, Spreadsheets Made Easy