UDF vs. Mega-Array-Formula

[Harry]

I am looking for feedback regarding whether I should pursue a solution as a
worksheet megaformula, or write a UDF using VBA instead.

I would prefer to use the megaformula instead of the VBA based solution for
speed purposes.

Question:
What criteria do you use to determine whether to pursue a solution via a
worksheet formula, or VBA?


for those who indicate case-by-case basis, can you elaborate what would make
you opt for the VBA instead of the mega formula?

thanks,
Harry

 

[Jim Thomlinson]

Here is my general guideline. UDF's are a final resort only when conventional
formulas will not work or in the very rare instance where a UDF will be
faster than a standard formula.

To that end I have used UDF's only for things like counting colour formulas
and returning a numeric value as the spelled out text.