Monthly Archives: November 2011

TEXT vs VALUE vs VALUE2 – Slow TEXT and how to avoid it

I was intrigued by a recent post pointing out that using .Text to retrieve data from Excel ranges got slower and slower as you iterated through the rows. So I took some time to explore and compare the three main … Continue reading

Posted in UDF, VBA | 54 Comments

Writing efficient VBA UDFs (Part 7) – UDFs calculated multiple times

There are several circumstances where Excel will calculate a UDF multiple times when you would expect it to only be calculated once. This can be a significant problem if your UDF takes a long time to execute. The previous posts … Continue reading

Posted in Uncategorized | 9 Comments

Evaluate Functions and Formulas fun: How to make Excel’s Evaluate method twice as fast

Prompted by a comment from Sam on Match vs Find I thought I would take a look at Excel’s rather quirky Evaluate method with Excel 2010 to see how it performed. The Evaluate method internally uses Excel’s formula parser and … Continue reading

Posted in Calculation, UDF, VBA | 44 Comments