The Complete Guide to the VBA Sub

VBA Sub

“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

Sub Example
Sub
1. Can run from Button/Event etc.
2. Cannot return a value.
Function
1. Cannot run from Button/Event etc.
2. Can return a value or object.
3. Can run as Worksheet function.
Create SubSub CreateReport

End Sub
Create FunctionFunction GetTotal As Long

End Function
Create Sub with parametersSub CreateReport(ByVal Price As Double)

Sub CreateReport(ByVal Name As String)
Create Function with parametersFunction GetTotal(Price As Double)

Function GetTotal(Name As String)
Call SubCall CreateReport
' Or
CreateReport
Call FunctionCall CalcPrice
' Or
CalcPrice
Call Sub with parametersCall CreateReport(12.99)

CreateReport 12.99
Call Function with parametersCall CalcPrice(12.99)

CalcPrice 12.99
Call Function and retrieve value
(cannot use Call keyword for this)
Price = CalcPrice
Call Function and retrieve objectSet coll = GetCollection
Call Function with params and retrieve value/objectPrice = CalcPrice(12)
Set coll = GetCollection("Apples")
Return value from FunctionFunction GetTotal As Long
    GetTotal = 67
End Function
Return object from FunctionFunction GetCollection As Collection
    Dim coll As New Collection
    Set GetCollection = coll
End Function
Exit SubIf IsError(Range("A1")) Then
     Exit Sub
End If
Exit FunctionIf IsError(Range("A1")) Then
     Exit Function
End If
Private Sub\Private Function
(available to current module)
Private Sub CreateReport
Public Sub\Public Function
(available to entire project)
Public Sub CreateReport

 
 

Introduction

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

Sub:

  1. cannot return a value.
  2. can be called from VBA\Button\Event etc.

Function

  1. can return a value but doesn’t have to.
  2. can be called it from VBA\Button\Event etc. but it won’t appear in the list of Macros. You must type it in.
  3. 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

 
 

Passing 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

  1. The person passing a value may not expect it to change and this can lead to bugs that are difficult to detect
  2. 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.

 
 
For example

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.

This means

  1. The variable you pass will not be accidentally changed
  2. 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.

 
 
Worksheet Function
If you use this function in a cell you will get the result 99 in the cell as that is what the function returns.

 
 

Conclusion

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

 
 

What’s Next?

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

How To Ace the 21 Most Common Questions in VBA

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.

Free VBA eBook

 
 

6 comments

  1. Doesn’t passing a sub a ByRef arg make the ByRef arg a variable which is local to function calling it so that the sub can change the it’s value? In effect a Private variable if the calling function is the top level “Main” function?

    Basically doesn’t a ByRef sub argument mean that a sub can indeed act as if it is “returning” a value.. just that ihe sub cannot be used in an “expression”. to define the return. e.g. A=subroutine(ByRef A) .
    more

    1. From the section Passing Arguments

      “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.”

  2. I really appreciated this clear tutorial – thanks!

    A nit regarding the “Little-Known Pitfall of ByRef” section which says:

    “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 .”

    This only seems to be true (Excel 2007) for functions when you invoke the function as a statement (without touching the return value). If you do assign or use the return value, then (of course) you must use parentheses to call the function, nonetheless ByRef seems to work. This behaviour makes sense if for instance the job of the function is to return the sum (or another calculation) on an array, which must always be passed as ByRef.

Leave a Reply

Your email address will not be published. Required fields are marked *