Overuse of VBA?

[Oorang]

When I first started using Excel many many years ago, it was as a casual user. I made all the usual mistakes and pretty much used it like word with cells. Then came banking and I was forced to learn Excel "for real". Sure I knew some of the the math formulas, but even IFs were new to me. But I loved it right away and threw myself into learning formulas, and pretty soon people were coming to me with questions instead of the other way around.

Then one fateful day I discovered VBA. I never really saw a need for it until I watched a colleague run a screen-scrape that did about 3 days worth of his work in a "Mere" 45 minutes. (Being a little more experienced with databases, I cringe to think of this now.) But that was it, I was hooked and I learned it inside and out. But as a man with a hammer, pretty soon every problem started looking like a nail. In the last year or so I have come full circle, it seems that often UDFs and add-ins are built and used for functionality that someone more expert with the Product could do without VBA. (For instanced, I now know how to pull that data directly into Excel via query).

So how about it? Anyone else find that VBA can be a crutch? Did leaning on it slow you down in learning the native interface? Ever use it and then find you didn't really need to? (I once recreated Word's "Toggle Codes" function.) Should "Only use VBA after you've tried everything else" be our motto?

I'd like your feed back.

 

[DonkeyOte]

I think I was/am guilty of that too especially regards over use of UDFs

My general rule of thumb is that if my brain begins to hurt trying to come up with a native function and/or it's heading towards incredibly long and inefficient arrays I tend to revert to UDF approach.

I don't revert to UDF nec. for sake of efficiency but for sake of readability...
My general standpoint is that any solution based on native functions is in reality going to be just as incomprehensible to end-user as any VBA based approach, however, the latter will be significantly more succinct in cell syntax terms and should in theory (if constructed well enough) be able to account for various scenarios / outputs simply by altering a handful of arguments within the "formula" itself. In short I would say a well designed UDF is no bad thing per se.

Note: I'm not saying my UDFs meet the above criteria nor am I saying that there aren't efficient non-UDF approaches available (BarryH, Aladin, PGC01, Domenic etc have all proved that over time) it's just that too often I don't or rather can't think of them!

 

[Lewiy]

I don't think that VBA has ever stopped me learning native functions as I was fairly proficient in these before I entered into the world of VBA (a path which I would highly recommend), however, I have caught myself on a number of occasions over complicating problems by using VBA when in hindsight it would have been quicker and easier to use worksheet functions.

Possibly the cause for this is a desire to treat every project as an "application" that I need to build and make user proof, regardless of the actual purpose of the solution.....so I empathise to an extent!!!

 

[SydneyGeek]

It's easy to use VBA 'because you can'. There's a blurred line between VBA being the best way to go, versus other options via the interface.

I'll often try both. The one with the best combination of ease-of-use and efficiency wins, especially if I will hand the workbook to another user.

Denis

 

[RoryA]

For Each varitem In Array(84, 104, 101, 114, 101, 39, 115, 32, 110, 111, 32, 115, 117, 99, 104, 32, 116, 104, 105, 110, 103, 32, 97, 115, 32, 111, 118, 101, 114, 117, 115, 101, 32, 111, 102, 32, 86, 66, 65, 33)
 strText = strText & Chr(CLng(varitem))
 Next varitem
 MsgBox strText

 

[yytsunamiyy]

For Each varitem In Array(84, 104, 101, 114, 101, 39, 115, 32, 110, 111, 32, 115, 117, 99, 104, 32, 116, 104, 105, 110, 103, 32, 97, 115, 32, 111, 118, 101, 114, 117, 115, 101, 32, 111, 102, 32, 86, 66, 65, 33)
 strText = strText & Chr(CLng(varitem))
 Next varitem
 MsgBox strText

I totally agree rorya.

 

[SydneyGeek]

For Each varItem In Array(79, 102, 32, 99, 111, 117, 114, 115, 101, 32, 110, 111, 116, _
33, 32, 87, 114, 105, 116, 105, 110, 103, 32, 109, 101, 115, 115, 97, 103, 101, 115, 32, _
108, 105, 107, 101, 32, 116, 104, 105, 115, 32, 105, 115, 32, 99, 111, 109, 112, 108, 101, _
116, 101, 108, 121, 32, 105, 110, 116, 117, 105, 116, 105, 118, 101, 33)
 strText = strText & Chr(CLng(varItem))
 Next varItem
 MsgBox strText

Denis

 

[RoryA]

Yep!
(I have code to write the code, so it's easy)

 

[SydneyGeek]

You wouldn't want to do it by hand... that's the whole point of code isn't it?

Denis

 

[hatman]

But as a man with a hammer, pretty soon every problem started looking like a nail. In the last year or so I have come full circle, it seems that often UDFs and add-ins are built and used for functionality that someone more expert with the Product could do without VBA.

I too have come full circle. VBA definitely has it's place. There are certainly things that cannot be done without it. On the flip side, it's way too easy to use VBA because it's fast and easy. I still tend to do so when I'm doing personal troubleshooting. Often, I'll even use VBA to work out a non-VBA solution. But in general, I found that it's best to offer solutions to others that don;t use any VBA, or even any obscure Addins. Often, even using formulas from something as ubiquitous as the Analysis Toolpack can be confusing to novice users. Then again, using complex native formulas can often be just as confusing, however, people's eyes don;t tend to be quite as apt to glaze over... they can at least view the solution as something they can absorb, if they take the time.