Beauty of formulas and comparison with VBA solutions

regresss

Board Regular
Joined
May 20, 2015
Messages
68
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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,943
Office Version
  1. 365
Platform
  1. Windows
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

Legend
Joined
May 15, 2003
Messages
29,536
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

Board Regular
Joined
Mar 16, 2002
Messages
248
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,742
Messages
5,638,100
Members
417,007
Latest member
joellange20

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top