Beauty of formulas and comparison with VBA solutions

[regresss]

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.

 

[Joe4]

A wise man (OK, more like a "wise-guy") on this board once said "You could use a wrench to drive in nails, but why would you?" It seems pretty inefficient.
Or just look up the legend of "John Henry" for another good metaphor.

What I am getting at is there are different tools, and some work better for better other, depending on the tasks that they were designed for.
For example, I see a lot of questions on this board where people are having a heck of a time trying to create a database-type model in Excel (jumping through all kinds of hoops), and I think, that is so easy to do in Access (because that is what Access is designed for).

The trick is identifying what tool is the best for a certain task.
Of course, there are often other factors that come into play in the "real world", i.e. people not allowed to use VBA, or they don't have Access, etc.

I don't know if that clarifies anything, but that's my 2 cents anyway...

 

[Smitty]

Welcome to the Board!

I'll be a wise guy and simply put that the best solution is the one that's most appropriate for the situation. The hard part is knowing which.

I see a lot of people trying to use VBA when formulas will suffice, and the opposite, where people try formulas where VBA is the only solution. And Joe's absolutely right on the Access front; a lot of people waste a lot of time trying to get Excel to be a database, which it's not.

In cases where formulas and VBA are equally feasible I generally tend to stick with formulas, as a native approach is almost always faster. The deciding factors for me are efficiency (which means processing load, as well as end-user load), and what fits your particular competence in that situation.

Another thing I see quite often is people struggling with VBA/Formulas/both when a simple Pivot Table will suffice (and much faster). Again, the hard part is determining what's the best tool for the job.

 

[stemar]

Welcome to the Board!

I'll be a wise guy and simply put that the best solution is the one that's most appropriate for the situation. The hard part is knowing which.

I know all about the hard part - at least how hard it is!

Some years ago I had a rather complex spreadheet and one formula was 8 lines deep in the formula bar. It worked fine (eventually), but when I wanted to make a slight change 6 months later I had no idea how it worked! So I re-wrote it as a VB function, with nicely structured and commented code, so 5 years later, I was able to make another change to the formula.

As a rule of thumb, I'd suggest that when your formula gets long enough that it's hard to read, it's time to think, 'Am I going to understand that when I come back in 6 months time?' If the answer's not a definite yes, it's time to think about VB