Intro to Sub Procedures

Excel VBA – Intro to Sub Procedures

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:

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. Personally, 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:

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.

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.

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:

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:

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:

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:

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:

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:

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:

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:

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.

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

Are you ready to master Excel Dashboards?

Learn Excel Dashboard Course Power BI Webinar

Still not convinced? Check out my review of the course!