A VBA Function can accept parameters and return results. Functions, however, can’t be executed directly. On the other hand a VBA Sub procedure can be executed directly and can also accept parameters. Procedures, however, do not return values.
We often use Subs and Functions often not thinking about their true potential and how much we can squeeze out of a regular VBA Function. Let’s start with a reminder how both differ from each other and then dive into the details.
VBA Sub, VBA Function Syntax
Below structure resembles a VBA Sub and VBA Function:
'This is a Sub (procedure). Subs can only take arguments, they don't return results Sub SomeSub(...) ... End Sub 'This is a simple Function Function SomeFunc(...) as ... 'Functions can take arguments and can return results ... End Function
Below are simple examples of Subs and Functions:
'Example Sub Sub SayHello(name as String) Debug.Print "Hello " & name End Sub 'Example Function Function Add2Numbers(num1 as Long, num2 as Long) as Long Add2Numbers = num1 + num2 End Function 'Below Sub tests the Function Add2Numbers Sub TestAdd2Numbers() Debug.Print Add2Numbers(1,2) End Sub 'Result: 3
VBA Sub procedure syntax
VBA Function procedure syntax
VBA Function vs VBA Sub
We often tend to mix up procedures, Subs and Functions in VBA. So let’s get it right this time. There are 2 main differences between VBA Procedures (Subs) and VBA Functions:
- VBA Functions return values, VBA Subs don’t
- You can execute a VBA Sub, you can’t execute VBA Functions – they can only be executed by VBA Subs
Executing Functions and Subs
Although I provided examples above there are multiple ways to execute a VBA Function and a VBA Sub:
'An example VBA Procedure Sub TestSub(arg as Long) .. End Sub 'An example VBA Function Function TestFunction(arg as Long) as String ... End Function 'HOW TO RUN FUNCTIONS AND PROCEDURES Sub Test() Dim result '---RUN A FUNCTION--- 'Example 1: Run a Sub with brackets with the Call operator Call TestSub (10) 'Example 2: Run a Sub without brackets and without the Call operator TestSub 10 '---RUN A FUNCTION--- 'Example: Functions are meant to return values so then need to be used with brackets result = TestFunction(1) End Sub
Passing arguments ByVal and ByRef
The common knowledge is that only VBA Functions can return values. That indeed is mostly true. However, instead of a value (the result of the Function), we can assign values to variables that can be passed to the procedure by reference. What does that mean?
Variables can either by passed to a procedure (Function, Sub etc.) by their value or their reference.
Passing by value translates to making a copy of the variable. Thus any changes to the copy will not be reflected in the original variable.
Passing by reference is passing the address of the variable to the procedure. This means that any changes to a argument will be reflected in the original variable.
ByVal and ByRef examples
Let’s look at some examples now:
Sub SetValueByVal(ByVal someLong As Long) someLong = 10 End Sub Sub SetValueByRef(ByRef someLong As Long) someLong = 10 End Sub Sub Test() Dim someLong As Long someLong = 1 SetValueByVal someLong Debug.Print someLong 'Result: 1 (no change) SetValueByRef someLong Debug.Print someLong 'Result: 10 End Sub
When passing by value the variable someLong is not modified. However, when we pass it by reference its value is changed within the Sub procedure.
Passing arrays to Subs and Functions
You can also easily pass arrays to Subs or Functions, even redefining their length. See example below:
Sub ChangeLength(arr() As Long) ReDim arr(5) As Long End Sub Sub Test() Dim arr() As Long ReDim arr(2) As Long Debug.Print UBound(arr) 'Result: 2 ChangeLength arr Debug.Print UBound(arr) 'Result: 5 End Sub
VBA Functions and Subs permit optional parameters, ones that need not be provided when executing the Function or Sub. It is a good practice to specify the default value for such parameters. See example below:
Sub SayHi(name As String, Optional surname As String = vbNullString) Debug.Print "Hi there, " & name & IIf(surname = vbNullString, "", " " & surname) End Sub Sub Test() Call SayHi("John") 'Result: "Hi there, John" Call SayHi("John", "Smith") 'Result: "Hi there, John Smith" End Sub
You can verify if a parameter has not been passed to an Sub or Function by using the IsMissing function. The IsMissing function works however only for Variant type parameters. See the same SayHi procedure as above, this time with the IsMissing function.
Sub SayHi(name As String, Optional surname As Variant) Debug.Print "Hi there, " & name & IIf(IsMissing(surname), "", " " & CStr(surname)) End Sub Sub Test() Call SayHi("John") 'Result: "Hi there, John" Call SayHi("John", "Smith") 'Result: "Hi there, John Smith" End Sub
Dynamic parameter list
Say you want to create a VBA function like the Excel SUM or AVERAGE formulas – that can be provided with a dynamic list of parameters. VBA extends a neat solution for such a scenario called the ParamArray.
Public Function MySUM(ParamArray args()) For Each arg In args MySUM = MySUM + arg Next arg End Function Sub Test() Debug.Print MySUM(1, 2, 3, 4) 'Result: 10 End Sub
The ParamArray transforms a list of Variant variables passed as parameters into a neat Variant array.