VBA Basics - Sub Procedures Vs Functions

Introduction

This article describes the differences between sub procedures and functions within VBA (VBA being ‘Visual Basic for Applications’ – the programming language used to write macros within Microsoft Office products such as Excel, Access etc.). It is aimed at those who have worked with at least basic macros previously, or simply those that are familiar with the terms 'sub' and 'function' but not the differences between them.

Before we look at the differences, and to serve those who may not be familiar with what a ‘Sub’ and ‘Function’ actually are, below is a very brief and basic explanation:

Both ‘Sub’ and ‘Function’ procedures are the homes to nearly all of the code you will write.These code blocks are where the computer identifies and then performs the tasks you tell it to in a top-down fashion: each line is executed one at a time starting from the top of the sub/function, all the way to its end (there are exceptions to this, but are not within the scope of this article).

So what are the differences? If you are anything like how I was when I first started learning VBA, you may have already come across articles or videos that simply define the difference between sub procedures and functions as the following:

“Functions return a value, whereas sub procedures do not…”

Whilst technically and fundamentally this is absolutely true (and an extremely succinct way of describing the core difference!), it may not mean all that much to the learner.

So, in short, here is how it works:

Sub Procedure

A sub procedure is merely a block of code that has a name to distinguish it from other blocks of code. If you created a macro that printed a message to the screen, it may look like this:

Sub PrintMessageToScreen()

 
      MsgBox "Here is my message"


End Sub  

This sub procedure is identified as a sub procedure quite clearly by the word ‘Sub’ appearing before an arbitrary name, which uniquely identifies this block of code from others (some rules apply here (such as not allowing spaces in the name), but are not the subject of this article).

Running the above sub procedure results in the following image.

Function

A function, like a sub procedure, is a block of code that executes the various lines of code within it with one key difference: it returns a value. So, let’s look at an example of a function:

Function ProvideMessage() As String

 
        ProvideMessage = "Here is a message from a function"


End Function

Two points to note here:

1.      The addition of ‘As String’, on the first line, can only be added to a function in this way. This declares the type of data (string, in this case (think letters/words)) that the function can “return”, or in other words, present back to the code that executed the function.

2.      The line ‘ProvideMessage = “Here is a message from a function”’ treats the name of the function (‘ProvideMessage’) as a variable, and assigns a value to it. Note that we should only assign data that is of the same type as what we declared on the first line. So therefore, if we stated:

Function ProvideMessage() As Integer

 
        ProvideMessage = "Here is a message from a function" 

 
End Function

This would fail due to our function stating it will return an integer (a round number within a certain range, in computer terms). We stated this with the keywords “As Integer” on the first line. Within the code, though, what we are actually returning is a string. To return the function to one that returns strings, we simply change:

Function ProvideMessage() As Integer

to

Function ProvideMessage() As String

This would set our function to one that returns a string, and works correctly.

How do we use it?

Based on our code so far – the fact that we have written a sub and a function procedure already – we could change our sub procedure slightly by writing the following:

Sub PrintMessageToScreen()

 
        MsgBox ProvideMessage()


End Sub

Now, instead of ‘PrintMessageToScreen’ printing a message itself (by using Msgbox “Here is my message”), our sub procedure now “calls” the function (tells the function to execute its code) instead, with the requirement that the function returns a value that the 'MsgBox' function can display to screen.

If the ‘ProvideMessage’ function were changed to a sub procedure, this functionality could not take place, as sub procedures cannot return a value.

The following image shows the result of running this sub procedure.

Final Words

Whilst this example uses a very basic demonstration of what a function can do, there are far more powerful uses for them where their abilities can be truly appreciated.

If you would like to replicate this example, follow these steps:

• Launch Microsoft Excel
• Press Alt + F11 to open the VBA editor.
• Click ‘Insert’ and select ‘Module’. In the module paste the code below. Click into the Sub, and press F5 to run!

 Code to paste:

Sub PrintMessageToScreen()


          MsgBox ProvideMessage()


End Sub

 

Function ProvideMessage() As String

 
        ProvideMessage = "Here is a message from a function"


End Function

Happy VBA'ing!

Jon Reyes