Excel Custom Function UDF Causing Problems

Excel macros ran really slowly in a workbook that someone sent to me. They said it was an Excel custom function (UDF) causing problems. The UDF ran for no apparent reason, and slowed other macros down. Here’s what I did to make things a bit faster.

User Defined Function (UDF)

The custom function converts numbers to words. For example, 22.50 would be converted to the written words, “Twenty-Two Dollars and Fifty Cents”.

You can see the code for the custom function, named SpellNumber, on the Convert Numbers Into Words page of the Microsoft site.

Finding the Bottleneck

When I stepped through one of the workbook’s macros, it cleared a range of several hundred cells.

At that line of code, it jumped to the SpellNumber function, and ran through that function.

And then it did the same thing, again and again. After a few loops, I stopped the macro.

My guess is that it was recalculating some or all of the cells that use the SpellNumber function. No wonder the macros ran slowly!

Why was the SpellNumbers function running so frequently? Was there any way to stop it from running unnecessarily?

It’s Complicated

I’m not a calculation expert, but Excel performance expert, Charles Williams, shows how dependencies and recalculations work with formulas and UDFs. Even with his very simple example, things get complicated!

Imagine trying to track all the dependencies in someone else’s large, complicated workbook!

TIP: Charles sells a FastExcel add-in, that makes it quick and easy to find performance problems in your workbooks. I have a copy, but it wasn’t installed on the laptop I was using. Read more about in this FastExcel review on my site. (These are affiliate links)

Where Are the Formulas?

I did a quick Find in the workbook, and the SpellNumber function is only used on 3 sheets. There were 350-400 rows on each sheet, with the function used in 2 columns, and twice in each formula.

So, if we do some arithmetic, there are 400x2x2x3 = 4800 calculations!

I did a quick test with a global variable (myCount), to count how many times it looped through the UDF during the main macro.

It was a big number! myCount= 3438

Turn Things Off

Unfortunately, I couldn’t find any internet advice on how to stop a UDF from running unnecessarily.

The first thing that I tried was putting a couple of lines at the start of the macro, to turn off events, and screen updating.

That might have made the macro run a bit faster, but it still looped through the UDF countless times.

Put Up a Stop Sign

For my next test, I added a “stop sign” for the SpellNumber UDF.

- First, I created a global variable named bNumWdOff.
- Next, at the top of the SpellNumber UDF, I added a line to check the bNumWdOff variable

If bNumWdOff=True then Exit Function

Finally, in the other macro, I added code to change the variable to True at the start, and change it to False at the end.

bNumWdOff = True

That sped things up a bit. The code still went to the SpellNumber function countless times, but didn’t loop through it.

Turn Off Calculation

My final test was to turn off calculation. The main macro was adding formulas to a sheet, so I couldn’t turn off calculation entirely.

Instead, I turned off calculation for the 3 sheets that use the UDF.

Then, at the end of the main macro, I added these steps

set bNumWdOff to True, so it won’t go through the entire SpellNumber UDF
turn on calculation for the 3 sheets
set bNumWdOff to False

UDF Causing Problems

As you can see, a custom function (UDF) can slow down macros in your workbook.

The UDF also ran when I filtered the columns that used the custom function.

I’m sure there are lots of other things that trigger the UDF too. Other calculations? Changing cell values? What else?

Custom Function Alternative

In the workbook that was sent to me, the numbers that were being converted to words weren’t very big, and they were all whole numbers.

In this case, instead of using a custom function, you could create a table of numbers, from zero to 100. Then, in the second column, put the written word(s) for each number.

Then, in the cells where you want to show written words, use an INDEX/MATCH formula to pull the words from the lookup table.

Use a Macro to Convert Numbers

Another option is to convert the numbers to words with a macro, instead of a custom function. It could put the words in the cells as static values, instead of a formula.

Run the macro when needed, and you won’t have formulas with custom functions slowing down your workbook.

FastExcel Add-in

Here’s the link to Charles Williams’ FastExcel add-in again, in case you’d like to see what it can do to help speed up your workbooks.

And here’s the page with my FastExcel review. (These are affiliate links)

_______________________