VBA vs Built-In Functions

I know this has been beaten like drug user owing a lot of money to a thug but the whole built in function vs using VBA often beckons me to question it. From a thread, FDibbins provided

Formula:

Please Login or Register  to view this content.

Now I know that a lot of you guys and gals here have no problem seeing something like that and think, "there's nothing tough about that" but when I see stuff like that my eyes invert. I know it's probably due to my lack of using built in functions because I've been on the VBA wagon right out of the gates (as far as Excel goes). Maybe I should spend some time with the functions but at this point in my career with Excel I almost feel I can write a code block quicker than coming up with what you may find a simple solution sheet side.

There's no real point to this other than what I've just stated so....look a bunny!

I'm just the opposite, I learned formulas first and VBA later, so I almost always try for a non-VBA solution (formulas, built-in functionality like text-to-columns or filters) first.

I guess I should expand myself more with this and perhaps look into the general forums a little more than I do (which is almost never). They seem like such a learning curve though and that's why I've kept away from them. I can Sum() like the best of you gurus though

I think my knowledge of Excel and of VBA developed in parallel, partly ... Ok, in a large part, due to asking, answering and observing questions in these forums.

I would generally opt for a built in function solution over a VBA solution if there is one. Admittedly, some of the functions knocked out by the experts here don't always spring to mind.

Probably the only exception that I might make is complex and widespread array functions in which case a VBA solution may be preferable for performance reasons.

Regards, TMS

As you know my VBA skills are limited...but even that i believe that i can give formulas solutions to more than 80%(from rest 20% some of these needs VBA(can not do using formulas) and some just i don't know how to do..) of Excel cases, some times these are inapplicable for some reasons..Very slow...too many repeated calulations(Array formulas)...complicated for understanding..etc...

So in my own projects i usually prefer to ask for help from the VBA experts here to built these.

I like VBA and i'll try to learn it!

Here's an example:

Find value in range, loop for each row

Over 10,000 rows the formula and the code both complete within 2 seconds. The formula is marginally faster.

Over 100,000 rows the formula takes 3.6 seconds and the code takes 2min18 seconds
Over 200,000 rows the formula takes 9.9 seconds and the code takes 4min47 seconds

Just sayin' ...

Hi TMS, I guess my issue is that I can't readily look at a function, even the one from the thread you linked to, and easily know what it is trying to accomplish. Because of this, I can't readily create a function either.

I think I'm going to have to make it my goal to break on through to that evil side of Excel

And, to be fair, I still see a lot of formulas presented here and it's as though a black mist comes down in front of my eyes ...

I believe the original formula,

=IF(ISERROR(VLOOKUP(A2,$E$1:$F$10,2,1)),B2,VLOOKUP(A2,$E$1:$F$10,2,1))

can now simply be:

=IFERROR(VLOOKUP(A2,$E$1:$F$10,2,1),B2)

Even less typing! It's a good time to get into formulas! (Ok, since 2007, when IFERROR came along.)

I think a spreadsheet full of formulas is very fragile.

Concerning the example posted by TMShucks, did snb's code get a trial?

I suspect that that ran quite handily.

@ Paul, If I remember correctly, the OP was using 2003, hence no iferror()

In support of my use of formulas over VBA...my VBA sucks lol

How do you time a worksheet function?