Over at FastExcel the answer was given why UDF arrays go faster to the question I had on why UDF array formulas are faster than regular formulas. I had forgotten about this post that he had written.
Here’s the answer that he gives. Check out his blog post to see more of the details of why this is.
You can break down the time taken by a VBA UDF into these components:
- Overhead time to call the UDF.
- Time to fetch the data thats going to be used by the UDF.
- Time to do the calculations.
- Overhead time to return the answer(s).