Why is Excel User Defined Function not working?

Why is Excel UDF not recalculating?

When you make any changes in your workbook, Excel won’t recalculate each and every formula you have there. It’ll update the results for those formulas that are linked to the changed cells only.

But this concerns the standard Excel functions. As for the custom ones, Excel cannot validate the VBA code and identify other cells that could also affect the result of the custom function. Therefore, your custom formula may not change when you make changes to the workbook.

To fix the issue, you’ll just need to use the Application.Volatile statement. Check out the next chapter to learn the step-by-step instructions on how to apply it.

Volatile vs non-volatile custom functions

By default, custom functions in Excel are not volatile. This means that the UDF is recalculated only if the value of any of the cells it refers to changes. But if the format of the cells, the name of the worksheet, the name of the file change, then no changes will occur in the UDF.

Let’s switch from words to examples. For instance, you need to write down the name of your workbook in a cell. To do this, you create a custom function:

Now imagine the following case. You wrote custom formula =WorkbookName() into the cell and got the file name there. In a while, you decided to rename the file and saved it with a different name. But you look at the value in the cell and see that it has not changed. There is still an old file name which is no longer right.

Since there are no arguments in this function, the function is not recalculated (even if you change the name of the workbook, close it, and then reopen it).

Is there an easier way? To make the formula recalculating every time the worksheet changes, you need an extra line of code. Paste the following piece of code at the beginning of your function:

Application.Volatile

So, your code will look like this:

Now your UDF is volatile, hence it will be recalculated automatically if any cell in the worksheet has been recalculated or any change has occurred in the workbook. As soon as you change the name of the file, you will see that update immediately.

Therefore, I recommend using volatility only where it is really needed.

Why custom functions are not available

When you enter the first letters of the name of a custom function, it appears in the drop-down list next to the input cell, just like standard Excel functions.

However, this does not always happen. What mistakes can cause this situation?

If you have Excel 2003-2007, then the UDF never appears in the dropdown list. There you can see only standard functions.

But even if you are using a newer version of Excel, there is another mistake you may accidentally make.

You see, the custom function must be in a standard VBA module called Modules. When you add a new module to write the function code, a Modules folder is automatically created in which all modules are written.

But sometimes it happens that a new module is not getting created. On the next screenshot you can see that the custom function code is in the “Microsoft Excel Objects” module along with ThisWorkbook.

The point is that you cannot place a custom function in the code area of ​​a worksheet or workbook. In this case, the function will not work. Moreover, it will not appear in the dropdown list of functions. Therefore, the code should always be in the folder Modules.

Excel custom function help text is not displayed

Another problem may occur is the hint you see when you paste a custom function. If you use a standard function, you will always see a tooltip for the function and for its arguments. But what about UDFs?

If you have a lot of custom functions, it will be extremely difficult for you to remember what calculations each of them does. It will be even more difficult to remember which arguments to use. I think it’ll be a good idea to have a description of your custom functions as a reminder.

For this, I’d suggest using the Application.MacroOptions method. It will help you show the description of not only the function but also of each of its arguments in the Function Wizard window. You see this window when you click the Fx button in the formula bar.

Let's see how to add such a hint to your UDFs. In the previous article we looked at the GetMaxBetween custom function. It finds the maximum number in the specified range and takes three arguments: a range of numeric values, and a maximum and minimum value to search for.

Now we’ll add a description for this custom function. To do this, create and run the Application.MacroOptions command. For the GetMaxBetween function, you can run the following command:

or

Variable str FuncName is the name of the function. strDescr - function description. The strArgs variables contain hints for each argument.

You may wonder what is the fourth argument to Application.MacroOptions. This optional argument is named Category and indicates the class of Excel functions that our custom GetMaxBetween () function will be placed in. You can name it after any of the existing categories: Math & Trig, Statistical, Logical, etc. You can specify a name for the new category in which you will place the functions you create. If you do not use the Category argument, then the custom function will automatically be placed in the “User Defined” category.

Paste the function code into the module window:

Then click on the “Run” button. The command will perform all the settings for using the Fx button with your GetMaxBetween() function.

If you try to insert a function into a cell using the Insert Function tool, you will see that there is your GetMaxBetween function is in the "My Custom Functions" category:

You can simply start typing the function name into the cell and you will see your custom function in the dropdown list of functions to select from.

Then call the Function Wizard with the Fx button.

In the Function Wizard window you will see a description of your function, as well as a hint for the first argument. If you place your cursor over the second or third argument, you will also see hints for them.

If you want to change the text of these hints, change the values ​​of the strDescr and strArgs variables in the RegisterUDF () code. Then run the RegisterUDF () command again.

If you want to undo all the settings made and clear the function description, run this code:

There is one more way to get a hint when you enter a custom function. Enter the name of the function and then press the Ctrl + Shift + A:

=GetMaxBetween( + Ctrl + Shift + A

You will see a list of all the function’s arguments:

Unfortunately, here you will not see the description of the function and its arguments. But if the names of the arguments are pretty informative, so they might be helpful too. Still, it's better than nothing :)

It will take a little more work to create intellisense for UDFs that work like standard Excel functions. Unfortunately, Microsoft does not provide any options. The only solution available is currently an Excel-DNA IntelliSense extension. You can find more information on the developer’s website.