Hi All. In this post I’m going to be describing how to create function procedures to create your own functions in VBA.

Note 1: This is an intermediate level VBA post. I assume that you know how to use variables, arrays and loops in this post. If you do not, it would probably help to read the writeup guides we have on the subject. You can see those guides here in the VBA section.

Note 2: The terms "parameter" and "argument" are not the same and refer to different things. This post uses those terms interchangeably, which is fairly common.

Functions are one of two types of procedures in VBA: the other are subroutines, which are occasionally called macros. There are two types of functions: Worksheet functions and VBA functions. I will be discussing both in this post.

If you have even some of the most basic knowledge of Excel, you’ve used functions before: They’re what you use in your formulas. Functions typically take a number of parameters (although some take none) and return a value. So a user-defined worksheet function is a function that you can call from the worksheet (or VBE) in the same way that you do with native Excel worksheet functions.

Excel has so many functions available to you. You may wonder what the point would be to create your own functions. There are several reasons for creating your own functions:

1. You have complicated formulas that, perhaps invoke several different formulas and parameters. You can simplify this by putting all of the logic in your own user-defined function.

2. Once you create a function, you can make use of it in multiple cells, worksheets, workbooks, etc. Just like you can with native worksheet functions (if they’re in an addin)

3. You want to get the result of a value on the worksheet, perhaps once or several times, without deleting your undo stack

4. You want to assign a value to a variable or array in VBA that is processed in some way.

Now that we have an introductions to functions and why you’d want to create your own, let’s look at function scope.

Public and private functions

Just like subroutines (macros), functions are public by default. For functions, this means that they’re available for use within the Excel worksheet, and within other modules in VBA. You can make the function private by using the private keyword before the function keyword like so:

Private Function ADDSFIVE()
‘code goes here
End Function

A private function is only available for use within the same module where it’s defined and is not displayed by the worksheet intellisense.

Differences between function and subroutines

There are a number of differences between subroutines and functions. Let’s take a look at some of them below:

1. Only function procedures can return values. Note the use of my word can here. Function procedures are not required to return values. In Excel, if I call a function with no return value, it just returns the default value of whatever its return type is (e.g. false for bool, 0 for long, etc.)

2. You can call function procedures to return values without deleting your undostack in the worksheet. This can’t be done with macros.

3. Functions need to be defined in the current workbook or an addin to be called from the worksheet / VBE. They cannot be used if defined in your personal macro workbook. You can also qualify the workbook name with the function name to call them. (One thing you can do however, is define them in a module in your personal macro workbook, and then just drag that module into the VBProject in the workbook you want those functions in.)

4. Any code which makes any modification to Excel is disabled in a function when called from a worksheet cell. Such code works when called from VBE.

5. Typically, macros crash and break on the line where a runtime error occurred. This does not happen in functions and can make them harder to debug.

6. When you provide arguments to a subroutine, they are passed by reference by default. This means that VBA modifies the value of the original variable that was provided to the subroutine. In functions, they are passed by value. This means that a copy of the function uses a copy of the original value. You cannot pass a value in a function by reference with the exception of arrays, which must be passed by reference. However, you can work around this easily by assigning a variable to the value that’s returned by the processed function of itself. You can see an example of that below:

dim A as long
A = processFunction(A)

Note: this does not work with arrays

So now we have some background with functions, let’s start with some examples:

First function example: ADDFIVE()

Option Explicit
Function ADDFIVE(val As Long)
ADDFIVE= val + 5
End Function

In this example, a function is created which accepts one parameter. In the function, there is just one line of code. The single line of code is an expression. If you notice, the name that we’re assigning the value of val + 5, ADDSFIVE, has the same name as the name of the function. In the body of this function, ADDFIVE is essentially just a special type of variable. Since the ADDFIVE variable has the same name as the function, it is the value that’s ultimately returned by the function.

So if you use this function in the Excel worksheet, the ADDFIVE function would return the value of the val parameter + 5, because that’s the value we assigned to the function.

One thing you may notice is that option explicit is enabled, but there’s no dim statement in the function to declare the variable ‘val’. The dim statement is not required for variables declared in the parentheses next to the function name. It is however required for all variables declared between the function and end function statements.

Another thing you may notice is that, like native Excel worksheet functions, ADDFIVE is in all capitals. You can name your functions in lowercase, capitals, or use mixed case.

One thing we may wonder about ADDFIVE is, what if we assign the value to val to be something that’s not numeric, like a string? Let’s take a look at an example in the next section.

ADDFIVE() revised

=ADDFIVE(“Hello World!!”)

The error Excel gives me is a #VALUE! Error when I use this formula in the worksheet. As I said before, functions do not break on the line where the error occurred. So, if you’re function was relatively long, it can be difficult to find where the error is.

One thing we can do is check for the value type supplied and run different code depending on what is supplied. Let’s take a look at the ADDFIVE function with additional logic to check user input:

Function ADDFIVE(val As variant)
    dim temp as variant
    If IsNumeric(val) Then
            temp = val + 5
    Else
        temp = “Error: val Parameter Is Not numeric”
    End If
    ADDFIVE = temp
End Function

This function is a bit more complicated than the first function, so let’s start breaking it down. The first thing that's done is declare a temp variable of the variant data type (variant is used because we can return a number type or string type.) Unlike our first function, which just returned an expression, this function does some type checking. The first thing this function does is check if the val variable is numeric. If it isn’t, then the temp variable is assigned the error message and returned by the function. If it is, then the temp variable is assigned the expression val + five and the function returns this value.

It can be helpful to use a temp variable like this in case you ever need to rename a function. If so, you only need to replace the function name in one place, which will be at the end of the procedure.

Once you have this function entered in a module in VBA, you can call this function like a regular Excel function in the worksheet. You can also call this function in VBA. With ADDSFIVE defined in a module, you can run the following code in the immediate window:

debug.print ADDFIVE(5) ‘returns 10

Now that we have an idea of how functions work, let’s take a look at return types.

Function return types

By default, if you don’t declare datatypes for variables, they can be set to any type. Similarly, for functions, if you don’t declare a return type, they can return any type. Because of this, it’s recommended that you explicitly return the datatypes for your functions. This can be done like so:

Function ADDFIVE(val As Long) As String
‘code goes here
End Function

The ‘as string’ declaration sets the return type of the function to a string. So whether add five returns the sum of two numbers, or an error, it will return a string either way.

One thing that’s important to note about function return types is that they do not require the function to return values. So you can write code like so:

Function ADDSFIVE() As String
‘no code in this function
End Function

Now, what happens if we run this function? Will it return an error? Unfortunately, no error is returned. There is no error because functions do not require return types. So what does the function return? Well, as I said earlier, what the function returns depends on depends on the datatype the function is set to return. Since we’re saying the function return type will be a string, if no value is returned, the value will be set to an uninitialized string which is “”.

Because of this, you should always have option explicit turned on when you write function procedures. A simple typo can change your function from having a return value to having no return value at all. And if you don’t have option explicit turned on, VBE will not warn you of this mistake.

Nesting functions

Just like you can with Excel worksheet functions, you can also nest function procedures you from your own user-defined functions. In VBA, this is done by passing one function as the parameter to another function. You can see an example below

Function HW()

HW = "Hello world"

End Function

Function EXCLAM(val As String)

EXCLAM = val & "!!"

End Function

‘in immediate window
Debug.print debug.Print EXCLAM(HW())
‘prints Hello world!!

Exit function statement

One thing that’s important to note about functions is that, even if you return a value, this does not necessarily exit a function. You may, for example, be in a loop, and the loop may continue processing even after you assign the value of a function. To deal with this, we have the exit function statement which allows us to exit at a point of our choosing. Let’s take a look at such a function in an example below:

Function INRANGE(val As String, rang As Range) As Boolean
Dim cell As Range
For Each cell In rang
    If val = cell.Value Then
        INRANGE = True
        Exit Function
    End If
Next cell
INRANGE = False
End Function

The INRANGE() function accepts two parameters: a val parameter and a range parameter. And the INRANGE function will ultimately return a Boolean value. A variable named cell is declared of the range datatype. Using this variable, a for-each-next loop is done that compares the value of the cells in the range to val. If any of the cells have a value that matches val, INRANGE is set to true, and the function exits. Since the value of INRANGE is set to true if the function exits using the exit function statement, true is returned by the function. If no such match is found, and the loop exits, INRANGE is set to a value of false before it exits the function in a natural way. And since false is the value its set to before the function exits, that’s the value that’s returned by the function.

Functions that have optional parameters

When you write functions, sometimes you want to provide optional parameters that can be utilized as necessary when the function is called. Optional parameters are specified with the optional keyword. Their data type must be variant. And they must be provided after all mandatory parameters. When you use optional parameters, you have to check whether they’re provided with this ismissing function as you can see below as you can see below:

Function BILINGUALGREET(lang As String, Optional name As Variant)

Dim greet As String

If lang = "E" Then
    greet = "Hello"
ElseIf lang = "S" Then
    greet = "Hola"
End If

If IsMissing(name) Then
    greet = greet & "!"
Else
    greet = greet & " " & name & "!"
End If

BILINGUALGREET = greet

End Function

This function, BILINGUALGREET specifies a greeting either in English or Spanish. The optional parameter “name”, of the variant type, comes after the mandatory parameter “lang”. If “name” is provided an argument, that name is also included in the greeting.

Functions that can take varying parameters

All of the examples so far have looked at functions that have a fixed amount of parameters. With certain functions, you don’t know how many arguments will be provided until it’s called. Sometimes you may want a function that takes one parameter, sometimes three, sometimes six, etc. If you had a function with a fixed amount of parameters, the function would fail when provided with too few or too many arguments. In VBA, you can create functions that can take dynamic parameters. You can create a function that takes a dynamic number of parameters using the paramarray keyword, which you can see used in the example below:

Function SUMMY(ParamArray args() As Variant)

Dim temp As Long, i As Long

temp = 0

For i = LBound(args) To UBound(args)
    If IsNumeric(args(i)) Then
        temp = temp + args(i)
    End If
Next i

SUMMY = temp

End Function

The paramarray keyword is specified before the array named args, which is a dynamic array. Once the dynamic array is provided, the array is iterated with using a for loop. A conditional statement then checks whether the current element of the array is numeric. And if it is, it is added to the temp variable. One the loop finishes, the temp variable is assigned to the function name. And this returns the value from the function.

Volatile functions

A volatile function is a function that recalculates automatically whenever any change is made is the workbook. This also includes stepping into a cell, even if you don’t make any changes. A few examples of functions that work this way are RAND(), RANDBETWEEN(), and NOW(). You can create your volatile functions by using the volatile property of the application object as you can see below:

Function DYNAMICRAND(Optional vol As Variant)
    
Dim temp As Double

Application.volatile

If Not IsMissing(vol) Then
    If vol = False Then
        Application.volatile False
    End If
End If

temp = Rnd

DYNAMICRAND = temp

End Function

This function works similarly to the RAND() function in Excel. It is a volatile function and recalculates any time any change is made in the workbook. The main difference is that it accepts an optional boolean parameter which allows you to turn volatility off. So if you pass in FALSE argument to the DYNAMICRAND() function, it will not be volatile

One thing that’s important to note is that, if you make use of a volatile function in your own code (e.g. worksheetfunction.randbetween()), this can make your function volatile. Once your function is volatile, it can not be made non-volatile by passing false parameter to application.volatile.

Array functions

All of the examples we’ve used so far only return a single value. However, functions can also return an array. You can take a look at an example below:

Function ADDFIVEARR(ByRef arr() As Long) As Variant
Dim i As Long

For i = LBound(arr) To UBound(arr)
    If IsNumeric(arr(i)) Then
        arr(i) = arr(i) + 5
    End If
Next i

ADDFIVEARR = arr

End Function

Sub subby()

Dim numsArr(2) As Long, funcArr() As Long, i As Long

numsArr(0) = 1
numsArr(1) = 2
numsArr(2) = 3

funcArr = ADDFIVEARR(numsArr)

For i = LBound(funcArr) To UBound(funcArr)
    Debug.Print numsArr(i)
Next i

'debug.print 6, 7, 8

End Sub

The function ADDSFIVEARR takes an array parameter. It goes through each element in the array. If the element is numeric, five is added to the value of that array element. And once all of the elements in the array have been iterated, the array is returned from the function. In this example, the array is called from the subroutine Subby that provides the argument for the function. There are a few things to note about this function:

1. Once an array’s elements have been given values (like numsArr’s have) you can’t reassign those values to the array without clearing them. So, we use the dynamic array funcArr to get past this.

2. The arr() parameter in ADDFIVEARR is passed byref explicitly. As I noted earlier, if arrays are passed as arguments, they must be passed by reference. If not you’ll get a syntax error (VBE notifies that, matter-of-factly, “Array argument must be ByRef”)

3. Because arrays are passed as reference, the array that is passed (i.e. numsArr) is also modified. So in the subby macro, it doesn’t matter whether iterate through the numsArr or the funcArr arrays. They essentially point to the same array. So I can iterate through and debug.print either of them and get the same values.

Debugging functions

As I stated earlier, debugging functions can be difficult. Unlike macros, the functions don’t break at the line where the error occurred when called from the worksheet. So how do you debug them? Let’s look at some examples below

Runtime errors

Runtime errors are errors that cause a procedure to crash at runtime. This is what happens when a macro encounters such an error. It breaks and allows you to see which line of code the error occurred on. You can do this with function procedures as well. Let’s take a look at a few different options:

Immediate window: Using the immediate window (view -> immediate window), you can call the function like so: “debug.print ADDFIVE("hello world")” If no error ocurrs, the function will return the result in the immediate window. If one does occur, it will break on the line where the error occurred in VBE.

Test Macro: Another option you have is to run a test macro that calls the function like so:

Sub Test()

Range("A1").Value = ADDFIVE("hello world")

End Sub

Function ADDFIVE(val) As Long

ADDFIVE = val + 5

End Function

Running this code will give you an error in a similar error to using the immediate window. Now that we discussed some solutions for runtime errors, let’s discuss semantic errors.

Semantic errors

Semantic errors are errors that happen when a function returns a value, and so doesn’t have a runtime error, but does not return the value it should. These errors are the result of a bug in your code. Because there’s no runtime error, the code does not crash at any particular line. That makes these type of bugs significantly harder to find and debug. Let’s look at a strategy for finding such errors below:

Immediate window with debug.print statements: Like runtime errors, the immediate window can also be useful for looking at semantic errors. One strategy is to open the immediate window and use several debug.print statements in your code. You can use these statements to print the value of several variables as they’re occurring in your code to see if they have the value that they should. The good thing about this method is that these statements will be ignored when the function is called from the worksheet. So you can add them and not have to worry about your function not working as it should when it’s called from the worksheet.

Test macro with watches Let’s take a look at another test macro below:

Sub test()

Debug.Print funky()

End Sub

Function funky() As Boolean

Dim a As Long, b As Long, c As Long

a = -1
b = 2
c = 3

If a + b = c Then

    funky = True

Else

    funky = False

End If

End Function

From here, we can click the debug window and click “watch window”. With the watch window, you can create watches. Watches allow us to provide an expression and allows us to take certain action based on the options we select. Let’s assume that all variables in the funky function should have a value greater than zero. We can test for this by providing an expression like “a < 0”. For the procedure, select funky. And finally, select the option “break when true”. After we add this watch, we can run the Test procedure. This procedure will break at the line b = 2 because the value of a is less than zero. You can also add additional watches to check for other variables and other procedures. Using watch windows in this way lets you work with semantic errors in the same way that you’d work with runtime errors. And it can be very useful, especially for larger functions.

Advanced function topics

Private functions that can be used in different modules

If a function is private, it doesn’t appear as a function that you can use in the worksheet. Unfortunately, this also prevents you from using it in other modules other than the one it’s defined in. You can get around this by defining the function in a class module. While class modules sound advanced, and this may seem difficult, it’s actually pretty easy to do.

You start by inserting a class module into your project (Insert -> class module). The first thing we’re going to do in the properties window (view -> properties window) is change the name to UDF. Next, we’ll just recreate our initial ADDFIVE function in the class module below:

Function ADDFIVE(val As Long)
ADDFIVE = val + 5
End Function

With the function defined, open or insert a module in VBA. You can now create an instance of the UDF object with an object variable as you can see below:

Sub subby()

Dim u As UDF

Set u = New UDF

Debug.Print u.ADDFIVE(15)
'prints 20

Set u = Nothing

End Sub

A few things to note about this example:

1. Since the u variable is an object, it must be instantiated. Objects are instantiated using the ‘new’ keyword. You can do this in the variable declaration like so: “Dim u As New UDF”. Using this syntax, you don’t need to set the variable to the object using the ‘set’ keyword. This syntax is more convenient but is not recommended.

2. Since the u variable is an object, it should be terminated by being set to nothing when you’re done with it.

You can use the methods defined in this object in multiple modules without ever appearing in the Excel worksheet.

Windows API functions

You can functions to encapsulate the logic to utilize the Windows API to do a number of really advanced things, such as detecting the state of certain keys on the keyboard (e.g. numlock). This is really complicated though and out of the scope of this discussion. I did feel the need to mention that it was possible though. If you’re interested, you can google ‘Windows api VBA” to see some examples)

Conclusion

Thanks for reading! I hope you’ve learned by reading this post that functions can be very useful in VBA. I hope this post helps you write some of your own functions in the future!