For many tasks, regular Excel functions cannot help. Well show you how to create new custom functions and how to use them to make your work easier. Learn how to create and use custom functions:
By the moment I started writing this article, Excel has already introduced you to over 450 different functions. With their help you can perform a vast variety of different operations. However, not any task can be solved in Excel as its developers couldnt foresee all the issues we face. I think that many of you have met at least one of these challenges:
While the first two options sound familiar, the third one may cause some confusion. So letâs have a closer look at custom functions in Excel and decide whether they are worth a shot.
Excel provides over 450 built-in functions that help users perform various calculations and data analysis tasks. However, sometimes the existing functions may not be able to meet your specific needs. This is where creating custom functions in Excel can be extremely useful.
Custom functions, also known as user-defined functions (UDFs), allow you to create your own formulas and extend Excel’s default capabilities In this comprehensive guide, you’ll learn
- What is a custom function in Excel
- When to use custom functions
- How to create a custom function step-by-step
- How to use a custom function in Excel
- Different types of custom functions
- Advantages and limitations of custom functions
What is a Custom Function in Excel?
A custom function in Excel is a formula written in VBA (Visual Basic for Applications) code that performs a specific calculation and returns a result.
For example, you can create a custom function to extract the domain name from a URL, count the number of words in a cell, add up even numbers in a range, capitalize text, and much more based on your requirements.
The key difference between built-in and custom functions is that the latter is created by you according to your needs, Excel doesn’t place any restrictions on what a custom function can do as long as the output value is something that Excel can handle,
When Should You Use a Custom Function in Excel?
Here are some common scenarios when creating a custom function can be useful:
- Existing Excel functions cannot perform the required calculation
- You need to automate a complex or repetitive task
- You work with customized calculations that use specific business logic
- You want to simplify formulas by encapsulating complex logic into a function
- You need to make certain calculations dependent on parameters not supported by built-in functions
- You want to create reusable code instead of copying formulas
- You need more flexibility than what standard functions provide
Step-by-Step Guide to Create a Custom Function in Excel
Follow these steps to create your first custom function in Excel:
1. Open the VBA Editor
Press Alt + F11 to open the Visual Basic Editor (VBE) window. This is where you’ll write and edit VBA code for custom functions.
2. Insert a New Module
In the Project Explorer pane on the left right-click on your workbook name and select Insert > Module. This inserts a blank code module.
3. Start writing your function
Begin with typing “Function” followed by the desired function name. Enclose any arguments within brackets. End the first line with the data type to be returned.
For example:
Function AddTwoNumbers(num1 As Integer, num2 As Integer) As IntegerThis starts defining a function named AddTwoNumbers that accepts two integer arguments num1 and num2 and returns an integer.
4. Add the function code
Write the VBA code that performs the desired calculation. Use the argument variables and assign the final result to a variable with the same name as the function.
For example:
Function AddTwoNumbers(num1 As Integer, num2 As Integer) As Integer AddTwoNumbers = num1 + num2End FunctionThis simply calculates the sum of the two arguments.
5. End the function definition
Close the function definition by adding End Function on a new line after the code.
6. Save and close VBE
Save your changes and close the VBE window to return to Excel. The custom function is now ready to use.
And that’s it! By following these basics steps you can create simple custom functions in Excel.
How to Use a Custom Function in Excel
To use a custom function in Excel:
-
Type the function name followed by opening and closing parentheses, just like built-in functions.
-
Enter any required arguments within the brackets.
-
Press Enter to see the result.
For example, if you enter the formula =AddTwoNumbers(10, 20) in a cell, it will return 30 as the result.
Custom functions appear under the “User Defined” category when inserting a function through the Formula tab. You can also get intellisense suggestions when typing the function name in a cell.
![Using a custom function in Excel][]
That’s all there is to using custom functions once created. Now let’s look at different types of custom functions you can build.
Types of Custom Functions in Excel
Custom functions in Excel can be categorized based on the number of arguments, optional vs required arguments, and data types.
1. Functions without arguments
You can create functions that perform a preset calculation without requiring any arguments.
For example:vb Function TodaysDate() As Date TodaysDate = Date End Function
This function simply returns the current date whenever used.
2. Functions with required arguments
These functions expect compulsory input values to work correctly.
For example: vb Function AddNumbers(num1 As Integer, num2 As Integer) As Integer AddNumbers = num1 + num2 End Function
This function requires two integer arguments to calculate the sum.
3. Functions with optional arguments
Optional arguments are not mandatory for the function to work. Default values can be set.
For example:vb Function SayHello(name As String, Optional greeting As String = "Hello") As String SayHello = greeting & " " & name End Function
The greeting argument is optional and defaults to “Hello” if not specified.
4. Functions that return an array
Custom functions can also return arrays or ranges instead of a single value.
For example:vb Function GetRange() As Range Set GetRange = Range("A1:A10") End Function
This returns the specified cell range.
There are many other possibilities when creating custom functions. You can have multiple arguments, input and output different data types, integrate with workbook data, add logic and conditions, and much more.
Advantages of Using Custom Functions
Here are some key benefits of using custom functions in Excel:
- Encapsulate complex formulas – Replace long, complex formulas with a simple function call to improve readability.
- Reuse code – Eliminate repetitive formula copy-pasting by creating reusable functions.
- Custom logic – Apply business rules, implement special algorithms, integrate with other systems.
- Simpler collaboration – Easily share functions across workbooks and teams.
- Parametrization – Pass inputs to generalize calculations for different scenarios.
- Cleaner spreadsheets – Abstract implementation details into functions keeping worksheets tidy.
Limitations of Custom Functions
However, there are some limitations to be aware of:
- Slower calculation – Custom functions may impact workbook recalculation performance depending on complexity.
- Limited portability – Functions don’t work when a workbook is used on systems without VBA installed.
- Programming learning curve – Creating advanced functions requires knowledge of VBA coding and objects.
- Risk of errors – Rigorous testing is needed to ensure accurate outputs.
- Limited access control – Anyone who can edit VBA code in a file can modify the functions.
Custom functions are a great way to extend Excel’s built-in capabilities for your specific needs. By encapsulating complex logic and reusable code into a simple function call, they can help clean up your spreadsheets and improve efficiency.
Follow the step-by-step guide provided above to start creating your own custom functions in Excel VBA. Just be mindful of the potential limitations and test thoroughly before using critical functions extensively across workbooks.
Happy custom function creating! Let us know in the comments if you have any other tips or examples to share.
How to use custom functions
When you create a custom function, it becomes available in the same way as other standard Excel functions. Now we are going to learn how to create custom formulas.
To apply a user defined function, you have two options.
- Click the fx button on the formula bar. Among the categories of functions, you will see a new group – User Defined. And in this category, you can see our new custom function CountWords.
- You can simply write this function into a cell in the same way as you do with standard functions. When you start typing a name, Excel will show you the name of the user defined function in a list of matching functions. In the example below, when I entered =cou, Excel showed me a list of matching functions, among which you see CountWords.
Letâs finally apply our custom formula. Write it down in any cell:
= CountWords(A1: A4)
Press Enter. See, we just specified the function and set up a range and here is the result of the count: 28 words. 
Using an array as an argument
Many Excel functions use arrays of values ââas arguments. Remember the SUM, SUMIF, SUMPRODUCT functions. We already covered this situation above when we learned how to create a custom function to count the number of words in a range of cells.
The code below creates a function that sums all even numbers in a specified range of cells.
The NumRange argument is specified as Range. This means that the function will use the original data array. It should be noted that the Variant variable type can also be used. It looks like Function SumEven(NumRange as Variant)
The Variant type provides a “non-dimensional” container for storing data. Such a variable can store any of the data types allowed in VBA, including numeric values, text, dates, and arrays. Moreover, the same such variable in the same program at different times can store data of different types. Excel will determine on its own what data is passed to the function.
The code has a For Each argument. It takes each cell and checks to see if it contains a number. If it is not, then nothing happens and it moves on to the next cell. If a number is found, it checks if it is even or not (using the MOD function).
All even numbers are summed up in the Result variable.
When the loop is finished, the Result value is assigned to the SumEven variable and passed to the function.
Most Excel functions have multiple arguments. Custom functions are no exception. This is why it is so important to be able to create UDFs with multiple arguments.
The code below creates a function that selects the maximum number in a given range:
It has 3 arguments: a range of values, a lower bound for a numeric range, and an upper bound for a range. The first one is rngCells As Range. This is the range of cells to search for the maximum value in. The second and third arguments (MinNum, MaxNum) are specified without a type declaration. This means that the Variant data type will be applied to them by default.
VBA uses 6 different numeric data types. Specifying only one of them means limiting the use of the function. Therefore, it would be better if Excel determines the type of numeric data itself.
The For Each loops through all the values ââin the selected range sequentially. The numbers that are in the range from the maximum to the minimum value are written to a special array – arrNums. Using the standard MAX function, find the largest number in this array.
Create Custom Functions in Excel (User Defined Functions)
How do I use a custom function in Excel?
To apply the custom function, the workbook where you saved it must be open in your Excel. If it is not, you will get the #NAME! error when trying to use it. This error indicates that Excel does not know the name of the function that you want to use in the formula. Let’s take a look at the ways in which you can use the custom functions you create.
How do I create a custom function?
If you have logic that you want to reuse many times or apply the same set of transformations to a different query or value, consider creating a custom function and then invoking the function where and when you need it. There are several ways to create a custom function: Use the Advanced Editor to add your own let statement and start from scratch.
What are the different types of custom functions in Excel?
Some types of custom functions in Excel include: It’s possible to create a custom function that doesn’t use any arguments. These primarily occur when the information you input as values doesn’t affect the results you return. For example, you may use the RAND function to add a random number.
How to create a custom function in VBA?
After opening VBE, you need to add a new module where you will write your functions. Right-click on the VBA project pane and select Insert -> Module. An empty module window will appear where you are to specify your custom function. Before we start, let’s go through the rules by which UDFs are created:
