I have a file with huge formulas and nested if statements, they are excessive and hard to understand. I was thinking of using vba for these formulas, but notice that when i use a vba function, it gets called over and over again for each cell ( i think) is this the only alternative?

what are the downfalls of using vba functions? compared to formulas?

This is a really extensive topic with lots of 'ifs' and 'buts'.

Perhaps a general rule of thumb would be that if a native worksheet function was built to do what you want, then use it. If you start having to use more resource hungry solutions to get these functions to work - such as using array formulas - then a UDF may be a better bet in the end even though there are some default overheads involved. There are loads of other options too such as using pivot tables, helper columns, better layout design, etc...

I recommend you have a look through the articles on Charles Williams' site. There's lots of valuable information and it's very accurately written:
How the Excel Smart Recalculation Engine works - Decision Models

There are segments in various sections on his website which discuss why a formula may be called several times in a calculation sequence, be it through bad UDF design or due to Excel optimising the calculation.


For us to give a more specific answer, perhaps you could give us a simple, specific example?

Hope that helps....