The Complete Guide to Functions and Subs in Excel VBA

“I’m happy to share what I can, because I’m in it for the love of programming.” – John Carmack, creator of Doom, Quake  and Oculus VR.

© Ariwasabi | Dreamstime.com

 

Introduction

What are Functions and Subs?

Functions and Sub are are very similar in VBA. 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 Fuctions and Sub 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 parameters
and much more

If are looking for a quick guide to using Functions and Subs then go to the first section. However make sure to come back to the post as it has important information about using Functions and Subs that you don’t want to miss out on.

 

Functions and Subs: A Quick Guide

The following table provides a simple overview of using Functions and Subs.

A Quick Guide to Subs and Functions

 Note: A Function can also be called like a Sub when you are not returning a value.

 

What is a Sub?

In Excel VBA a Sub and a Macro are exactly 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 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: Cannot return a value

Function: Returns a value

Sub: You can run it from VBA\Button\Event etc.

Function:  You cannot run it from VBA\Button\Event etc. You can run in two ways:

Call it from another Sub/Function
When you create a Function it appears in the worksheet function list for the current workbook

 

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 Function

' 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 type 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 you

So as I said in the last section you should avoid passing a variable using ByRef and instead use ByVal.

This means

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. Look at the function in the next example.

Sub MyNewFunction()
MyNewFunction = 99
End Sub

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 screensheet.

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
Use a Sub to write most of your code
Use a Function if you need to return a value
A Sub can be run in many ways
Functions must be called by a Sub/Function or used as a worksheet function
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 some of my other in-depth posts on the important elements of VBA – A Quick Guide to All the Posts