How To Create Your Own User-Defined Functions With VBA
Hey everyone! Chris here and I want to introduce you to author/blogger Allen Wyatt. Allen has been running a weekly Microsoft Office tips newsletter since 1997! I was absolutely ecstatic when he reached out with interest for writing a guest post for this blog. Allen has recently entered into the online course area of teaching and has put together a very high-quality course for learning how to write VBA macros. I asked if he would cover one of my favorite sections from his course, walking through custom function creation. Enjoy!
Understanding User-Defined Functions
Mention the word “function” to an Excel user, and immediately they’ll envision common functions such as SUM or DATE or even VLOOKUP. This is understandable, as Excel makes available hundreds of functions you can use in your own formulas to massage your data.
Those with experience creating macros, however, may need additional clarification when the word “function” is used. You see, there are actually three different types of functions available to Excel users. The first is the familiar Worksheet Functions like SUM or DATE or VLOOKUP. These are built into Excel and are used extensively in worksheets.
The second type of function is what I refer to as a Procedure Function, which is a common programming structure used by those developing macros. A procedure function is used to perform some operation and then return a value to whatever programming code called that function.
The third type of function is known as a User-Defined Function (UDF). It is this third type of function that makes Excel extremely powerful as it allows you to access the power of macros directly from within your worksheets.
Think about that for just a moment: You can create a macro that can be accessed from within a worksheet, just like any other worksheet function! This means that if you create UDFs you aren’t limited to only those functions provided by Excel; you can create functions that do just about anything you can imagine.
The trick to creating your own UDFs is to determine what single piece of information you want that function to return, along with what pieces of information you need to provide to it. For example, let’s say you needed to routinely determine the number of uppercase “X” characters residing in a range of cells. What you need back is a count (a number) and what you need to supply is the range of cells to check.
Here’s an example of a UDF you might come up with:
Function NumXs(rng As Range) As Integer
Dim iCnt As Integer
Dim c As Range
Dim sTemp As String
Dim J As Integer
iCnt = 0
For Each c In rng.Cells
sTemp = c.Value
For J = 1 To Len(sTemp)
If Mid(sTemp, J, 1) = "X" Then iCnt = iCnt + 1
Next J
Next c
NumXs = iCnt
End FunctionParts Of A User Defined Function
For those who know how to program macros, you may immediately recognize that a UDF has a structure very similar to regular VBA functions. If you aren’t familiar with macros, though, this type of macro code can be a bit intimidating. If you’ve got this feeling, it may be helpful to take a look at each part of the macro. Let’s start with the first line:
Function NumXs(rng As Range) As IntegerThis line simply tells VBA (Visual Basic for Applications, which is the language used by macros in Excel) that you are defining a function, that it’s name is “NumXs”, that the function will work with a range of cells you want to refer to as “rng”, and that the result of the function will be an Integer value (a whole number).
When working with your own UDFs, the two things you are most likely to change in this line are the function name (NumXs) and the function result type (Integer). The other elements of this line are very common and don’t get changed all that often.
The next four lines in the UDF are these:
Dim iCnt As Integer
Dim c As Range
Dim sTemp As String
Dim J As IntegerThese are declarations of variables that will be used in the main body of the UDF. Each line consists of the Dim keyword followed by the name of the variable, the As keyword, and the data type for the variable. Each variable has a specific purpose, determined when you actually envision what the UDF will accomplish. For instance, iCnt is a variable that will serve as a counter for the number of X characters found.
The next seven lines in the UDF do the actual work of looking at what is in each cell:
iCnt = 0
For Each c In rng.Cells
sTemp = c.Value
For J = 1 To Len(sTemp)
If Mid(sTemp, J, 1) = "X" Then iCnt = iCnt + 1
Next J
Next cThe code starts off by setting the counter (iCnt) to 0. This is followed by a special programming structure known as a For Next loop. In this instance, it means that for each cell in the range passed to the UDF, all the programming lines between “For Each” and “Next c” will be repeated. Those lines first grab the value of whatever is stored in each cell and store that value in the sTemp variable. Then, another For Next loop is used which examines each character in the sTemp variable. If that character is “X”, then the counter (iCnt) is incremented.
The next line of the UDF is very important:
NumXs = iCntThis makes the NumXs variable equal to the value in the counter (iCnt). Note that the NumXs variable has the same name as the function itself, as stated in the first line of the UDF. Assigning the value to NumXs in this way informs VBA of the value that should be returned from the UDF.
That leaves just one last line in the UDF:
End FunctionThis line tells VBA that the end of the UDF has been reached. (Everything between the first line—the one that starts with “Function”—and this line is considered part of the UDF.)
Using A User-Defined Function In Your Spreadsheet
So how do you use the UDF in your worksheet? Just the same as you would any other worksheet function. For instance, if you wanted to know the number of “X” characters in the range A2:A15, you could use the following in a cell:
=NumXs(A2:A15)The function returns the number of uppercase “X” characters in the range A2:A15. The result is stuffed into the cell just as soon as you press Enter after typing the formula.
Below is a look of how you might use this function in a spreadsheet environment:
Obviously, the example provided in this article is rather simplistic—most people never need to know how many uppercase “X” characters are in a range of cells. What it should demonstrate, however, is that through the use of UDFs you can expand how you process your data in a worksheet. They are very powerful and not that hard to create, so give it a try with your processing needs.
If you are baffled by “all things macro,” however, you would do well to check out an online course that could help improve your skills. What you’ve learned above is just one part of my Excel Macros for Beginners course, which can help you make sense of macros and even create your own. And, as the title promises, it is a course designed for beginners just like you.
Want to know more? Check out UsingOffice.com
Allen Wyatt is the author and publisher of the ExcelTips newsletter, published at no cost to recipients every week for the last 18 years. You can find out more about him at AllenWyatt.com. His course, Excel Macros for Beginners, is available at UsingOffice.com.
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
Allen Wyatt