Building User-Defined Functions in VBA

While subroutines can be called within other subroutines, their environments are generally segregated — a subroutine cannot directly provide any information to another subroutine. Using functions, however, permits the transfer of a value, array, or object from one procedure to another.

Overview

This post within the Getting Started with VBA series continues the focus on Functions and Subroutines in VBA.

Functions, in one form or another, are a core component of most programming languages. In VBA, their behavior is similar to that of subroutines, although with one very distinct difference: functions can return values, arrays, or objects. These returned items can behave like any variable or literal statement — being assigned to other variables, used in conditional statements, or manipulated with other intrinsic/user-defined functions.

VBA already has a host of intrinsic functions which you may already be familiar with, such as Left, Len, and Replace. These functions are built-in and do not need to be declared. Prior posts, such as this one on String and Numeric Functions provide an overview for a few of the many intrinsic functions. While intrinsic and user-defined functions behave similarly with regards to function calls, this post is specifically about user-defined functions, which must be declared and manually scripted.

Sample File

The following macro-enabled Excel file contains all the example scripts presented in this post. Macros must be enabled upon opening.

Functions Vs. Subroutines

Earlier in this part of the series, the overview on subroutines provided a simple comparison of Subroutines vs. Functions from the perspective of a subroutine. As a quick overview, the post explains that a function can return an actual value, while a subroutine cannot. Now looking from the perspective of a function, it’s important to point out another difference — the tools available for calling a function are also very different from subroutines. Given the nature of functions, functions cannot be called through a traditional call statement (with some exceptions), through a form control, or directly through the Macro dialog box. While this may seem like a hindrance, it’s worth pointing out that functions can be called in-line with assignment operators or conditional statements, providing a value (or array, or object) that can be used directly within a script. Generally, if you desire to have a function called as if it were a subroutine, you’re not utilizing the core feature of a function and you should simply change the procedure to a subroutine.

Using Functions

While a subroutine is a stand-alone procedure to accomplish an action, a function is a procedure used in-line within a script to return a specific value. Syntactically, functions are very similar to subroutines in that they:

  1. Must be placed in modules as independent procedures.
  2. Can contain a script of any length or complexity, including calls to other subroutines and functions.
  3. Can have values (arguments) passed through parameters.
  4. Can declare their own internal variables and objects.

The primary difference, as previously discussed, is the simple fact that a function can return a value. Within a standard subroutine, calling a function will execute a script within that function which produces a value. That value will then be passed to the caller subroutine for use as a variable, array, or object.

When using the application-side of Excel, functions are commonly used, such as SUM and VLOOKUP. While you enter the function name and arguments into the formula bar, a specific value is returned in place of the formula. This works similarly in VBA, although instead of returning the value in a cell, the value will be assigned to a variable or used within a script. In fact, some user-defined VBA functions can also be used within the application-side of Excel (more on this will be discussed in a future post).

Basic Function

Before writing a simple script within a function, you must add a module in the VBA editor. Here are instructions on how to open the VBA editor and create a new module.

The following is a simple function which will multiply two numbers:

As can be seen, the overall structure of a function is very similar to that of a subroutine. The main differences are as follows:

  1. Function is used in place of Sub in the declaration and end statement.
  2. The function itself is assigned a data type after the parameter declarations. This data type should fit the the value being returned by the function. In this example, if the function were to return a String value, a “Type Mismatch” error will occur.
  3. Within the procedure (typically at or near the end), the function name is assigned a value. This is how the function knows to return a specific value from the function.

Calling a Function

One of the primary differences between a function and subroutine relates to how they are called. While a subroutine can be called through a number of means, a function can only be called from within another subroutine or function. A function will never work as a stand alone procedure like a subroutine does. In the basic function example above, attempting to run the function with the “Run Sub” button will bring up the Macros dialog box. Likewise, the function itself will not be listed in the Macros dialog box. A function must always be called from a “caller” subroutine.

Variable Assignment

Simple message box displays function output.
Simple message box displays function output.

In the following example, the Subroutine Caller_Product contains a procedure calling the Mult_Product function through a variable assignment. On the right side of the assignment operator, the function name exists along with its arguments. Given that the function returns a value, the finalProduct variable will be assigned the value when the procedure runs. As a final step, the variable is assigned to a message box for display to the user.

Conditional Test

Functions are excellent tools for streamlining conditional statements and making them much more readable. If a subroutine contains complex If-Then-Else statements, consider placing some of the conditional mechanics in a separate function. While the following example is not complex (and quite redundant), it shows how a simple function call can be used within a subroutine’s conditional statement.

This approach is especially useful if multiple procedures need to follow the same conditional tests. Instead of re-creating the test for each instance, a call to this function will recycle the same code, always returning a Boolean value based on specific arguments. This is especially useful when validating data inputs.

Do Loops

Functions can be useful within loops in a number of different ways. The following is a slightly more complex operation where a Do While loop uses a function for two purposes. First, the function is used within the Do While statement itself, reading integer values greater than 0 as True. The function is then used again within the loop to alter the value of the argument passed through the original function.

The function simply divides the passed argument by 2. However, the subroutine uses a Do While loop to iterate through multiple runs of the function until the value reaches 0 (zero). Given that VBA translates the value of 0 (zero) into False, the Do While loop will terminate upon the function returning the value 0 (zero)

To better understand how this procedure and function works, the following explains the action:

  1. Variable startingTotal is assigned 100.
  2. Variable startingTotal is passed to the function Divisor_Two within the Do While statement. The Divisor_Two function takes the argument value 100, divides by 2, then returns the quotient. The Do While statement reads the returned value as 50.
  3. It’s important to note that the Do While statement simply evaluates a calculation and does not assign a new value to startingTotal. To get around this shortcoming, inside the loop, the variable startingTotal is assigned to a second call of the Divisor_Two function, assigning an updated value of 50 to variable startingTotal.
  4. Through the second iteration of the loop, the new startingTotal value of 50 is passed as an argument, returning 25.
  5. This cycle continues for 6 more iterations, reassigning values 13, 6, 3, 2, 1, then 0 to startingTotal.
  6. Variable iCount within the loop counts the number of iteration and returns the result in a message box.

One important note is that by casting the variable as an Integer, the function automatically rounds to the nearest whole number value. If fractions of a number were included in the formula by casting variable startingTotal in the Double data type, the loop would iterate a total of 1,082 times.

Other Uses of Function Calls

Given that a function call can be built to behave similar to variables (when assigned a variable data type), functions can be used in all places where variables are traditionally used. In the following example, a function call (returned value) is assigned directly to a message box and a range object value.

Two things to note:

  1. If you view the sample file, you will see that this subroutine re-uses the original Mult_Product function even as it passes two different arguments and uses the return value differently.
  2. This example cleans up code slightly when compared to the first example: Variable Assignment. In that example, the function return value was assigned to a variable, then that variable was subsequently displayed in a message box. In this example, the variable assignment is skipped, and the function return value was displayed directly in a message box. When working with functions, it’s always worth reviewing code to see if any unnecessary assignments or steps can be cleaned up.

Returning Items from Functions

As has been exemplified in the previous section, a primary feature of a function is its ability to return a value. In addition to values, functions can return arrays and objects.

Returning Traditional Data Types

The following example is reused from the previous section. In its most basic form, a function can return a traditional data type.

Assigning a data type to a function comes with the same roles and responsibilities as assigning a data type to a variable. The returned value must fit within the constraints of the data type. Failing to do so will result in a “Type Mismatch” error. In the above example, if a decimal value were to be the result from the Mult_Product function, the number would be rounded to the nearest whole number value, given that the Long data type does not permit a floating point.

Returning Arrays

On our way to a slightly more complicated use of functions, the following example returns an array from a function.

There are a few unique things occurring. Within the caller subroutine Caller_Function_Array_To_Var, the array variable fruitArray() is declared to hold the array values returned from the function, and variable fruitName is declared as the variant for the For Each loop element. Following the declarations, the function List_Fruit is assigned to the fruitArray variable. Within the List_Fruit function:

  1. The parameter fruitColor is declared as a String, which can contain the values “Red” or “Green” to determine which array is built within the function.
  2. The function itself is declared as a String data type, with open and close brackets to tell the function that an array will be returned.
  3. Within the function, the array variable fruitArray() is declared as a String array to temporarily store the array as it is built.
  4. The array is built within one of the two conditional statements depending on the argument passed. Given that the size of the array varies depending on the argument passed, a ReDim statement exists in each conditional to properly resize the array.
  5. The temporary array variable fruitArray is returned by assigning the array to the function name.

Returning to the caller subroutine, the array returned from the function is assigned the variable fruitArray. The array variable fruitArray is then used in a For Each loop to iterate through each array value. A message box will return each value individually.

There are a few important things to note with this example:

  • If an array is created within a function, it should be sized within a function. In this example, the ReDim statement is used to resize the array after initial declaration. If the array size will always be fixed, the size arguments can be used at the initial declaration.
  • Within the function, a temporary array variable should be used to create the array. Attempting to build the array using the function name will not work. The function itself cannot be provided with an array size, so an array size must be cast on the function using a temporary (and sized) array variable.
  • If the array only contains numeric values, the function should be assigned the Integer/Long/Double/etc. data type. If it contains a mix of numeric and string values, it can be assigned a Variant data type.
Cleanup!

It’s also worth noting that the function call itself can behave just like an array. The following example is a slightly more terse execution where the array variable is eliminated from the caller subroutine. Instead of using the array variable as the “collection” in the For Each loop, the function is in its place.

While this example looks almost identical to the first array example, you will notice that lines 3 and 6 have been removed, and the array variable in the For Each loop has been replaced by the function. This approach has a few advantages, as only one array needs to be declared and built, and the extra assignment statement is not required. This approach may be essential when working with extremely large arrays, as only one array will need to be stored to memory instead of two.

Returning Objects

Functions provide an opportunity to streamline procedures impacting objects. Objects include everything from Ranges, Worksheets, Form Controls, and Shapes. The following procedures manipulate the size of a shapes and output its name, height, and width on the worksheet.

Three shapes displayed on the tutorial file.

In the following script, a subroutine calls the custom Shape_Width function to modify the width of a specific shape and extract its name and dimensions.

The Shape_Width function accomplishes a few steps.

  1. The function receives two arguments from the caller subroutine — a shape color and optional shape width variable. The function itself is declared as a Shape object, meaning that instead of returning a standard data type, it will return an actual object. Within the caller subroutine, the function would either need to be assigned to a pre-set shape object, or included within a Set statement itself (which can be seen in line 5).
  2. A Select Case statement is used to determine which of the three shapes in the workbook is selected by the caller subroutine.
  3. Upon selecting the appropriate Case, the function name itself is assigned to a shape from the Set statement. This is unusual from the previous examples, as instead of assigning the function name to a variable or value, it is being Set directly to the object. This is the case with all objects, whether a Worksheet, Range, Control, etc.
  4. The shape Width is modified through the Shape object’s Width property.

Returning to the caller subroutine, the declared Shape object calledShape is now set to the Shape returned from the function. For the remainder of the procedure, the shape height is changed to 40 pixels, and the shapes name, height, and width are printed in cells F1 to F3.

There are a few important things to note with this example:

  • Within the function, even though the shape’s width is assigned after the shape is set, that width value will be updated in the Shape object returned from the function.
  • After the calledShape object is set to the shape returned from the function, all object properties and methods of the shape object will be accessible through the calledShape object.
Cleanup Round 1!

While the previous example did what was expected, there are two additional ways of approaching this same function within the subroutine. The next example is arguably less efficient than this first example, but slightly more terse.

Similar to how a function behave when it is cast as an array variable, a function cast as a shape will inherit all the properties and method of a shape object. With this, any object members associated with the shape object can be applied directly to the function call, either assigning (line 3) or extracting (lines 4-6) values. While this does eliminate the need to declare and set the shape object within the caller subroutine, this specific example makes 4 separate calls to the same function (keep in mind, one Set statement will be run in each), making the overall procedure less efficient.

If the concept of objects and their methods and properties is not clear to you, read the post Overview of Methods and Properties of the Range Object. You can also find more details about working with shape properties in the post A Financial Statement Template for April Fools Day

Cleanup Round 2!

Cleanup Round 1 provided us with a slightly condensed version, but it degraded efficiency from the original attempt as it required a total of 4 calls to the function. As a means of making object calls more efficient, using the With statement to the function (as a shape object) will eliminate the redundant function calls:

Using a With statement, VBA will only make one call to the function (requiring only one Set action), while multiple object members can be assigned or extracted from the specific Shape object returned from the function.

Cleanup Round 3!

While we’re at it, there are some cleanup opportunities within the function itself. The Select Case statement was included to provide an example of some complex mechanics which can be included in a function. However, the Select Case statement is unnecessary in this specific example. Given that we have applied a consistent naming convention to each of the shape names (specifically “[Shape Color]_Shape”), we can simply concatenate the shapeColor parameter with the shape name as it is being Set.

The end result is a simple 2 line function:

Used in conjunction with the Caller_Function_Shape_With subroutine above, we have a terse but readable execution of this procedure.

Calling the function for all shapes will resize each to 40px by 40px and output shape name, width, and height in column F.
Calling the function for all shapes will resize each to 40px by 40px and output shape name, width, and height in column F.

Using Parameters

The use and behavior of parameters in functions is identical to their use in subroutines. Click here to read information on using parameters within subroutines.

Function Scope

The method of assigning scope to functions is identical to the methods in subroutines. Click here to read information assigning scope to subroutines.

Other Notes

  • While the examples above use parameters to pass arguments through functions, functions can be created without parameters. Intrinsic functions, such as Date, do not require parameters. In user-defined functions, data can be pulled directly from a spreadsheet or system within the function, negating the need to use parameters and arguments.
  • Functions can exist (and work) without returning a value. This behavior mimics subroutines, although it may hinder the readability of your code. If a function does not return a value, it can be used within a Call statement, but cannot be called through Form Controls or within Excel’s Macro dialog. Generally, stick to a subroutine if no value is being returned.
  • While this post explains that subroutines cannot provide information to other subroutines, this can be done indirectly through global variables. This approach is considered less efficient than returning values (or passing, for the matter) as it results in additional memory usage.

Leave a Reply