Microsoft provides many built-in functions to speed up your work in Excel. However, you can technically create functions using VBA coding called “user-defined functions” (UDFs). Also known as a “user-defined function” in Excel VBA.

A formula that can be accessed from a worksheet using a piece of code is called a UDF. Simply put, formulas that are not built-in but available in Excel are called “user-defined functions”.

Using VBA, you can create user-defined functions (also called user-defined functions) that can be used in worksheets like regular functions. These are useful when existing Excel functionality is not sufficient. In such cases, you can create your own custom user-defined functions (UDFs) to meet your specific needs. This tutorial covers all about creating and using user-defined functions in VBA.

How to create User defined functions?

UDF is part of a module, but not part of a regular subroutine in VBA. In VBA they are called function procedures. Just like you start your macro coding with the word SUB, you should start it with the word “Function”. A sub-procedure has a beginning and an end. Similarly, a Function procedure has a Start and an End.

• Create SUM function in Excel By adding two numbers. To start coding, start with the word “function” in one of your modules. Similar to how macros are named, functions must be named.
•  We used this name as the expression name. Unlike subroutines, you cannot simply press “Enter” to create a procedure. But I have to mention the arguments here. 
• For example, consider the following worksheet function SUM syntax: Unlike subroutines, you cannot simply press “Enter” to create a procedure. But I have to mention the arguments here.
  For example, consider the following worksheet function SUM syntax:

 

Here we declare the arguments “x as integer” and “y as integer”. Since we are adding numbers, we only need to assign the data type as a numeric data type.

After declaring the arguments, we also assigned the return type to integer because the result returned by the OurSum function is also numeric.

Now, inside the function, we need to mention the formulas that we use. Here you have to use the function first.

 

Another Example of a User-defined Function

Create a simple custom function in VBA and see how it works.

The following code creates a function that extracts the numeric part from an alphanumeric string.

 

If you have the above code in your module, you can use this function in your workbook.
Below is how this function GetNumeric is used in Excel.

 

Before I show you how to create this function in VBA and how it works, there are a few things you should know.

Once a function is created in VBA, it can be used throughout the workbook like any other normal function. Typing an equal sign after a function name displays the function name in the list of matching functions. After typing =Get in the example above, Excel displayed a list of custom functions. I think this is a great example of how VBA can be used to create useful functions in Excel. You could do the same thing using formulas (as shown in this tutorial), but that would be complicated and hard to understand. With this UDF, you only need to pass one argument and get the result.

 

Arguments in a User-defined Function in VBA

In the example above where we created a custom function (GetNumeric) that retrieves the numeric part from an alphanumeric string, the function is designed to accept a single argument. This section describes how to create functions that take no arguments or multiple arguments (both required and optional).

Create a function in VBA without arguments

There are several functions in Excel worksheets that do not take arguments (RAND, TODAY, NOW, etc.).
These functions do not depend on any input arguments. For example, the TODAY function returns the current date and the RAND function returns a random number between 0 and 1. A similar function can also be written in VBA.
Below is the code that gives the name of the file. No arguments are required because the result that should be returned does not depend on the arguments.

 

The code above returns the result of the function as a string data type (I expect the filename to be a string as a result).
This function assigns the value of ThisWorkbook.Name to the function that is returned when the function is used on a worksheet.
Returns the name with the file extension if the file is saved. Otherwise, just return the name.
However, there is a problem with the above.
It doesn’t update automatically when the file name is changed. Functions are typically updated whenever their input arguments change. However, since this function has no arguments, the function is not recalculated (even if I rename the workbook, close it, and reopen it).

If necessary, you can force a recalculation using the keyboard shortcut (Ctrl+Alt+F9).

You need to add a line of code to recalculate the formula every time the worksheet changes.

In the code below, the function is recalculated every time there is a change in the worksheet (just like other similar worksheet functions like the TODAY and RAND functions).

 

If you change the workbook name, this function will update every time the worksheet changes or when you reopen this workbook.

Create a function in VBA with one argument

In one of the sections above, we already saw how to create a function that takes only one argument (the GetNumeric function above).

Let’s create another simple function that takes only one argument.

The function created with the following code converts the referenced text to uppercase. Now Excel already has a function for this. This function is just to show how it works. If you need to do this, I recommend using the built-in UPPER function.

 

This function uses the VBA UCase function to change the value of the CellRef variable. Then assign that value to the ConvertToUpperCase function.

Since this function takes one argument, we don’t need to use the Application.Volatile part here. The function will automatically update as soon as the arguments change. 

Create a function in VBA with multiple arguments

Similar to worksheet functions, you can create functions in VBA that take multiple arguments. The code below creates a function that extracts the text before the specified delimiter. It takes two arguments, a cell reference containing a text string and a delimiter.

 

If your user-defined function requires multiple arguments, each argument can be enclosed in parentheses and separated by commas.

Note that you can specify the data type for each argument. In the above example, CellRef was declared as Range data type and Delim was declared as String data type. If you don’t specify a data type, VBA assumes it’s a Variant data type.

When using the above function on a worksheet, the first argument must be the cell reference containing the text and the second argument must be the delimiter enclosed in double quotes.

The INSTR function of VBA is then used to check the delimiter’s position. The characters before the delimiter are then extracted using this position (using the LEFT function).

The function is then given the result as a final step.

This equation is far from ideal. For instance, it would give you an error if you entered a delimiter that wasn’t present in the text. Now you can either use the worksheet’s IFERROR function to remove the errors or the code below, which returns the complete text if it can’t locate the delimiter.

 

We can improve this function even further.

It would give you an error if you directly entered the text (from which you wish to extract the portion before the delimiter) into the function. Please give it a try!

Because we designated the “CellRef” as a range data type, this occurs.

The method above also prevents you from using the cell reference rather than hard coding the delimiter’s location in the formula if you want the delimiter to be in a certain cell. The Delim has been specified as a string datatype, which is why.

Remove the data type declaration if you want the function to be flexible enough to take user-supplied cell references or direct text input.

As a result, the argument would become a variant data type that can handle any kind of argument.

 

Create a function in VBA with an optional argument

In numerous Excel functions, some of the arguments are optional. The venerable VLOOKUP function, for instance, has 3 required arguments and 1 optional one.

As the name implies, it is not required to specify an optional parameter. If you omit one of the required arguments, your function will give you an error; but, if you omit the optional argument, your function will still function.

Optional arguments, however, are not pointless. They give you a variety of options to pick from.

For instance, the VLOOKUP function does an approximate lookup if the fourth argument is omitted, and an exact match if the last argument is specified as FALSE (or 0).

Keep in mind that all optional arguments must come after any necessary arguments. Optional parameters cannot be used at the beginning. Now, let’s look at how to add optional parameters to a VBA function.

Function with only an optional argument

I may be mistaken, but to my knowledge, there isn’t an intrinsic function that only accepts optional inputs. However, we can make one using VBA.

The function’s code is shown below. It returns the current date in two formats: dd-mm-yyyy if no argument is provided (i.e., leave it empty) and dd mmmm, yyyy if an argument is provided (i.e., anything so that the argument is not blank).

 

Note that the aforementioned function checks to see if the argument is missing or not using the ‘IsMissing’ function. Your optional argument must be of the variant data type in order to use the IsMissing method.

No matter what you enter as the argument, the aforementioned function operates. Only the optional argument’s presence or absence is checked in the code.

By limiting accepting certain values as parameters and displaying an error in the remaining circumstances, you can increase this’s robustness (as shown in the below code).

 

The code above generates a function that displays the date in two formats: “dd-mm-yyyy” if an argument is not provided and “dd mmmm,yyyy” if the input is. In every other situation, it returns an error.