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.
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:
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
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.
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 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:
Callkeyword lets you know that another sub procedure is being called rather than a variable.
Callkeyword 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
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.
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
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
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
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