“Computers are useless. They can only give you answers.” – Pablo Picasso.
This post provides a complete guide to using the VBA Sub. I also cover VBA functions which are very similar to Subs.
If you want some quick information about creating a VBA Sub, Function, passing parameters, return values etc. then check out the quick guide below.
If you want to understand all about the VBA Sub, you can read through the post from start to finish or you can check out the table of contents below(if not visible click on the post title).
Quick Guide to the VBA Sub
1. Can run from Button/Event etc.
2. Cannot return a value.
1. Cannot run from Button/Event etc.
2. Can return a value or object.
3. Can run as Worksheet function.
|Create Sub||Sub CreateReport
|Create Function||Function GetTotal As Long
|Create Sub with parameters||Sub CreateReport(ByVal Price As Double)
Sub CreateReport(ByVal Name As String)
|Create Function with parameters||Function GetTotal(Price As Double)
Function GetTotal(Name As String)
|Call Sub||Call CreateReport
|Call Function||Call CalcPrice
|Call Sub with parameters||Call CreateReport(12.99)
|Call Function with parameters||Call CalcPrice(12.99)
|Call Function and retrieve value|
(cannot use Call keyword for this)
|Price = CalcPrice|
|Call Function and retrieve object||Set coll = GetCollection|
|Call Function with params and retrieve value/object||Price = CalcPrice(12)
Set coll = GetCollection("Apples")
|Return value from Function||Function GetTotal As Long
GetTotal = 67
|Return object from Function||Function GetCollection As Collection
Dim coll As New Collection
Set GetCollection = coll
|Exit Sub||If IsError(Range("A1")) Then
|Exit Function||If IsError(Range("A1")) Then
|Private Sub\Private Function|
(available to current module)
|Private Sub CreateReport|
|Public Sub\Public Function|
(available to entire project)
|Public Sub CreateReport|
The VBA Sub is a essential component of the VBA langauge. You can also create Functions which are very similar to Subs. They are both procedures where you write your code. However, there are differences and these are important to understand. In this post I am going to look at Subs and Functions in detail and answer the vital questions including
- What is the difference between them
- When to use a Sub and when to use a Function?
- How do you run them?
- Can I return values?
- How do I pass parameters to them?
- What are optional parameter?
- and much more
Let’s start by looking at what is the VBA Sub?
What is a Sub?
In Excel VBA a Sub and a Macro are essentially the same thing. This often leads to confusion so it is a good idea to remember it. For the rest of this post I will refer to them as Subs.
A Sub/Macro is where you write your VBA code. When you run a Sub all the lines of code it contains are executed.
The following is an example of an empty sub
Sub WriteValues() End Sub
You declare the Sub by using Sub and the name. When you give it a name keep the following in mind
- The name must begin with a letter and cannot contain spaces.
- The name must be unique in the current workbook.
- The name cannot be a reserved word in VBA.
The end of the Sub is marked by the line End Sub.
When you create your Sub you can add some code like the following example shows
Sub WriteValues() Range("A1") = 6 End Sub
What is a Function?
A Function is very similar to a Sub. The major difference is that a Function can return a value – a Sub cannot. There are other differences which we will look at but this is the main one.
You normally create a function when you want to return a value.
Creating a Function is similar to creating a Sub
Function PerformCalc() Function Sub
It is optional to add a return type to a function. However it is considered good practice to do so. If you accidentally return the wrong type then it will be flagged as an error by VBA which is good.
The next example shows you how to specify the return type
Function PerformCalc() As Long Function Sub
You can see this is simple to how you declare a variable. You can return any type you can declare as a variable including objects and collections.
A Quick Comparison
- cannot return a value.
- can be called from VBA\Button\Event etc.
- can return a value but doesn’t have to.
- can be called it from VBA\Button\Event etc. but it won’t appear in the list of Macros. You must type it in.
- if public, will appear in the worksheet function list for the current workbook.
Note: You can use “Option Private Module” to hide subs in the current module. This means that Subs won’t be visible to other projects and applications. The also won’t appear in a list of Subs when you bring up the Macro window on the developer tab.
Function: How to Return Values
To return a value from a function you assign the value to the name of the Function. The following examples demonstrates this
Function GetAmount() As Long ' Returns 55 GetAmount = 55 End Function Function GetName() As String ' Returns John GetName = "John" End Function
When you return a value from a function you will obviously need to get it back to the calling Function/Sub. You do this by assigning the Function call to a variable. The following example shows this
Sub WriteValues() Dim Amount As Long ' Get value from GetAmount function Amount = GetAmount End Sub Function GetAmount() As Long GetAmount = 55 End Function
You can easily test your return value using Debug.Print. This will write values to the Immediate Window. To view select View->Immediate Window(shortcut Ctrl + G).
Sub WriteValues() ' Print return value to Immediate Window Debug.Print GetAmount End Sub Function GetAmount() As Long GetAmount = 55 End Function
Note: When you assign the return value of a function you need to use parenthesis around the function arguments
Arguments are passed to Subs and Functions in the same way. One important thing to keep in mind is that if you use parenthesis when calling the Function/Sub then passing by reference(ByRef) does not work. We’ll have a look at this later in the section.
The following shows how to declare a parameter for a Sub and Function
Function CalcValue(x As Long) End Function Sub WriteValue(x As Long) End Sub
You can see it is similar to creating a variable except that we don’t use Dim. You can specify two ways of passing a variable: ByRef or ByVal.
' Pass by value Sub WriteValue1(ByVal x As Long) End Sub ' Pass by reference Sub WriteValue2(ByRef x As Long) End Sub ' No type used so it is ByRef Sub WriteValue3(x As Long) End Sub
If you don’t specify the type then ByRef is the type as you can see in the third sub of the example.
The different between these types is:
ByVal – Creates a copy of the variable you pass.
This means if you change the value of the parameter it will not be changed when you return to the calling Sub/Function
ByRef – Creates a reference of the variable you pass.
This means if you change the value of the parameter variable it will be changed when you return to the calling Sub/Function.
The following code example shows this
Sub Test() Dim x As Long ' Pass by value - x will not change x = 1 Debug.Print "x before ByVal is"; x SubByVal x Debug.Print "x after ByVal is"; x ' Pass by reference - x will change x = 1 Debug.Print "x before ByRef is"; x SubByRef x Debug.Print "x after ByRef is"; x End Sub Sub SubByVal(ByVal x As Long) ' x WILL NOT change outside as passed ByVal x = 99 End Sub Sub SubByRef(ByRef x As Long) ' x WILL change outside as passed ByRef x = 99 End Sub
The result of this is:
x before ByVal is 1
x after ByVal is 1
x before ByRef is 1
x after ByRef is 99
You should avoid passing basic variable types using ByRef. There are two main reasons for this
- The person passing a value may not expect it to change and this can lead to bugs that are difficult to detect
- Using parenthesis when calling prevents ByRef working – see next sub section
Therefore you should always declare your parameters as ByVal for basic types.
A Little-Known Pitfall of ByRef
There is one thing you must be careful of when using ByRef parameters. If you use parenthesis then the Sub/Function cannot change the variable you pass even if it is passed ByRef . In the following example we call the Sub first without parenthesis and then with parenthesis. This causes the code to behave differently.
Sub Test() Dim x As Long ' Call using without Parenthesis - x will change x = 1 Debug.Print "x before (no parenthesis): "; x SubByRef x Debug.Print "x after (no parenthesis): "; x ' Call using with Parenthesis - x will not change x = 1 Debug.Print "x before (with parenthesis): "; x SubByRef (x) Debug.Print "x after (with parenthesis): "; x End Sub Sub SubByRef(ByRef x As Long) x = 99 End Sub
As I said in the last section you should avoid passing a variable using ByRef and instead use ByVal.
- The variable you pass will not be accidentally changed
- Using parenthesis will not affect the behaviour
Custom Function vs Worksheet Function
When you create a function it appears in the function list for that workbook.
Have a look at the function in the next example.
Function MyNewFunction() MyNewFunction = 99 End Function
If you add this to a workbook then the function will appear in the function list. Type “=My” into the function box and the function will appear as shown in the following screenshot.
The main points of this post are
- Subs and Macros are the same thing in VBA
- You can use Functions for everything.
- Functions appear in the workbook function list for the current workbook
- ByRef allows the Function\Sub to change the original argument
- If you call a Function\Sub with parenthesis then ByRef will not work
This post provided and in-depth look and functions and subs. I hope you found it beneficial. You may want to check out other popular posts like The Complete Guide to Using Arrays in Excel VBA . You can see all the posts by category here
Get the Free eBook
Please feel free to subscribe to my newsletter and get exclusive VBA content that you cannot find here on the blog, as well as free access to my eBook, How to Ace the 21 Most Common Questions in VBA which is full of examples you can use in your own code.