Beauty of formulas and comparison with VBA solutions

Hi, I've been thinking about this for a while. I'm curious what are your opinions on this:

1) Is incorporation of less formulas always a better solution? Or should we also reflect computing difficulty of the formulas used? If we agree with the latter, we can say that the beauty of an excel solution is based on how fast the computation is, how much of memory the operation takes.

2) When should I stop using formulas and give VBA a chance while both solutions are feasible? When you have to use 10 formulas to achieve something easily achievable with VBA, it is clearly wrong not to use VBA. Despite that, I sometimes feel tempted to solve problems only by formulas, because it feels like defeating Goliath with my littleness of David.

Yes, some formula solutions are faster than others, volatile are usually worse for the workbook than non volatile and array formulas generally perform worse than non array. VBA tends to be slower than foul as at achieving the same thing and generally a formula solution is preferred over a VBA solution.

That said it's not cut and dry there are plenty of exceptions, a single array formula can perform better than many Bon array to achieve the same thing and VBA can sometimes be a better choice.

In general the two things to consider are performance and clarity of solution, don't be tempted to over optimise at the cost of ease of maintenance, build for maintenance first then optimise.


Sent from my iPhone using Tapatalk