18.4 Introduction to VBA User-Defined Functions (UDFs)
Let's learn to write our own VBA functions. These are user-defined functions (UDFs). Excel provides a lot of functions that will accomplish most of what we want to do, but sometimes you get to the point where you wish you had something that would do a particular job for you, but you can't find it anywhere. So, you may decide to write your own custom function. Like any function, a UDF can be as simple or as complex as any other function.
Area of a Rectangle Function
Let's begin with a blank spreadsheet here, and we'll write a function to calculate the area of a rectangle. I know this is easy to do, but we'll do it together to learn how to develop a function.
Before we get started, save your spreadsheet as a macro-enabled workbook. Then, switch into the VBA editor and add a module. The first thing to know about functions is that a function needs to be in a module.
Add the Option Explicit command at the top of your module. Then, let’s define a function. Similar to sub procedures, we have a beginning and end of the function block. Instead of Sub, we use the Function keyword. The names of most functions in Excel use all capital letters. We will follow that same pattern. Here is the code so far.
Option Explicit
Function AREA()
End Function
A function typically will receive some data as input, do some processing, and then return a value as a result. Parameters for functions come immediately after the name of the function. In a future lesson, we will discuss how to declare variables of different data types, but we won’t explicitly do that here.
What information would we need to pass to determine the area of a rectangle? We’d need to pass values that represent the length and height. We can define the names as parameters as follows.
Option Explicit
Function AREA(Length, Height)
End Function
Eventually, when a user calls this function, they will pass values to these two parameters. The call of the function might look like =AREA(5,10), or it could look like =AREA(C2, B3). For the first example, Excel will assign the Length variable the value of 5 and the Height variable the value of 10. In the second example, Excel will get the values associated with cells C2 and B3 and then assign those values to Length and Height, respectively.
So, we have set up a way for the function to receive input. What about the processing and the output? For the processing, we will do the math calculation of Length * Height. To return a value from a function, simply assign the function’s name a value, which will return the value to whichever cell called the function. In our case, we will use these functions on our worksheets, so the function output will be saved as a value in the cell containing the AREA function. We could separate the processing from the return of the value, but in our case, we will combine those steps.
Option Explicit
Function AREA(Length, Height)
AREA = Length * Height
End Function
Go ahead and try out your new UDF. Switch back to your worksheet, choose a cell, and try =AREA(5,10), or put some values in some cells and reference the cells instead. You’ve just created your own function that you can use in the worksheet! This is such a powerful concept. If a function doesn’t already exist in Excel, we can write our own to do whatever we’d like!
You may have noticed that when you typed your function that IntelliSense didn’t pop up and tell you the function names. You can still use the fx button by the formula bar to see the parameter names, but it’s missing a description of what those parameters are. In a future lesson, we will highlight how we can add help to our function.
Adjusting the Function to Handle Rectangles and Squares
Let’s adjust our AREA function to be able to handle calculating a square in addition to a rectangle. For squares, we only need to pass one parameter instead of two. We can make a parameter optional by using the keyword Optional. Optional parameters have to come at the end of the parameter list. So, if you have parameters that are always required, you will need to list them first. Let’s add the Optional keyword to the height parameter.
Now, we need to alter our code in the main block to accommodate either one or two parameters. We can use the IF statement to check whether or not the height parameter was passed. If it wasn’t passed, we will calculate the area using just the length. If it was passed, we will calculate the area using both length and height.
There is a function in VBA that tells us whether a value was passed or not. We use the IsMissing function to see if the value is missing or, in other words, wasn’t passed to the function. This code will handle calculations for both squares and rectangles.
Function AREA(Length, Optional Height)
If IsMissing(Height) Then
AREA = Length * Length
Else
AREA = Length * Height
End If
End Function
Notice how the function uses the Else keyword as part of the IF statement. If the height is missing, it will calculate the area using just the length; else, it will calculate the area with both length and height. After making these adjustments, return to your worksheet and try calculations for both squares and rectangles.
Creating a USER Function
Now, let’s create a simple function that returns the name of the current Excel user. What property contains that information? Well, you might not know that. Of course, you could search for it on Google, and you may be able to find it relatively quickly. However, we can also employ the trick we used to print the envelope to find the appropriate property.
On the File menu, under Options, you can see the User name field, as displayed below. Go ahead and record a macro and see if you can find out what the property is called. Of course, you will need to make a change to that field so the code changing it will be generated, so go ahead and change the user name as you record the macro.
Now, examine the recorded code and see if you can identify the property.
Sub Macro1()
'
' Macro1 Macro
'
'
Application.UserName = "Tom"
End Sub
It should be pretty obvious that Application.UserName contains the name of the current Excel user. Of course, we can’t just use this line of code because we aren’t going to set the name; we just want our function to return it. However, we know how to do that already. Go ahead and create a function named USER that returns that information.
Function USER()
USER = Application.Username
End Function
Save your function and then test it out. First, try using the function alone and then try it in conjunction with other text functions like the following.
=USER()
="Hi, " & USER()
=LEN(USER())
Notice that when you call a function, unlike when you use a name or a named range, you always include the parentheses, even if you aren’t passing any parameters.
In this lesson, we’ve introduced you to VBA, sub procedures, and UDFs. You now have enough of a foundation to begin creating your own functions that can benefit you in your work. Future lessons will cover additional control flow and introduce you to more of VBA’s object model so you can do even more.