User Defined Funcion in Conditional Formatting - Drain on Resources?

[TemiU]

Hello Excel Experts!

We make heavy use of a user-defined function in conditional formatting in our applications.

We are trying to determine if this slows down the program in the same way that UDFs in cells do. So far, we tested by putting a breakpoint by the function to see when it gets called, but the breakpoint is never hit. (If we comment out the function, then there is an error, though.)

Does anyone know if using a UDF in this way slows down the application and/ or if there is any way to test?

Thank you!

 

[tusharm]

Well designed UDFs provide functionality that is natively absent from Excel with minimal performance impact. [By default, Excel will call a UDF only when an argument value changes.]

However, if you believe UDFs are a drain on resources, then why would you believe they are any different when used in a c.f.?

If you want to time performance, enhance your functions to maintain global counters of the number of times a function is called and how long it takes to execute the function.

You can then make a rational and objective decision on the pros and cons of using UDFs in your environment.

Hello Excel Experts!

We make heavy use of a user-defined function in conditional formatting in our applications.

We are trying to determine if this slows down the program in the same way that UDFs in cells do. So far, we tested by putting a breakpoint by the function to see when it gets called, but the breakpoint is never hit. (If we comment out the function, then there is an error, though.)

Does anyone know if using a UDF in this way slows down the application and/ or if there is any way to test?

Thank you!

 

[TemiU]

We have found, based on research, that if a cell contains a UDF in its formula, even though it is only called when the value changes, it takes much more time to process than if the cell contained a built-in Excel function. This had, as I recollect, something to do with Excel refreshing the environment every time a UDF is called. Therefore, even though programmatically it was better for our software to used UDFs, since it resulted in much "cleaner" code, we replaced a lot of the UDFs with often convoluted formulas using built-in Excel functions, because the software was running too slowly.

I can see that the c.f. is calling my UDF, based on your suggestion of using a global variable. The question was whether CF calling the UDF has the same issue of slowing things down as using a UDF in a cell formula.

Well designed UDFs provide functionality that is natively absent from Excel with minimal performance impact. [By default, Excel will call a UDF only when an argument value changes.]

However, if you believe UDFs are a drain on resources, then why would you believe they are any different when used in a c.f.?

If you want to time performance, enhance your functions to maintain global counters of the number of times a function is called and how long it takes to execute the function.

You can then make a rational and objective decision on the pros and cons of using UDFs in your environment.