# Overuse of VBA?



## Oorang (Nov 28, 2009)

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 (Nov 28, 2009)

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 (Nov 28, 2009)

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 (Nov 29, 2009)

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 (Nov 30, 2009)

```
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 (Nov 30, 2009)

rorya said:


> ```
> 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
> ...


 


I totally agree rorya.


----------



## SydneyGeek (Nov 30, 2009)

```
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 (Nov 30, 2009)

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


----------



## SydneyGeek (Nov 30, 2009)

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

Denis


----------



## hatman (Nov 30, 2009)

Oorang said:


> 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.


----------



## Cbrine (Dec 1, 2009)

I'm in the same boat as everyone else.  I do my best to create workbooks using only built in Excel functionality and formulas.  You can build some pretty amazing workbooks only using Sumproduct, Array formula's, offset, indirect, Custom Validation, Custom Formatting, etc..  I will only use vba as a last option on any workbooks that I'm planning on distributing.   Where nothing else I'm aware of will do what I want then I use vba.
     I still use vba extensively but only for workbooks that I know I will not be distributing to anyone else.


----------



## Norie (Dec 1, 2009)

How about overuse of Excel itself?

I just found myself posting a formula on the board.

I realised one of the cell references should probably be absolute.

So obviously I selected it and hit F4.

To my surprise nothing happened, then it dawned on me that I was writing a message on the board not a formula in Excel.


----------



## SydneyGeek (Dec 2, 2009)

Hmmm...

It'd be nice if you could put your mouse on a bank statement, press F4 and make $ symbols appear!

Denis


----------



## hatman (Dec 2, 2009)

Cbrine said:


> I'm in the same boat as everyone else. I do my best to create workbooks using only built in Excel functionality and formulas. You can build some pretty amazing workbooks only using Sumproduct, Array formula's, offset, indirect, Custom Validation, Custom Formatting, etc..


 
Sometimes I find it amazing how quickly some users jumpt to VBA, even though I was once one of those users myself (and still can be at times).  Under many circumstances, VBA can become an impediment to learning the native power of the worksheet environment.  I mean, why bother learning how to apply Match() and Offset() creatively when you can write a LeftLook() UDF?


----------



## Oorang (Dec 2, 2009)

Surely you meant match/index?


----------



## hatman (Dec 2, 2009)

Oorang said:


> Surely you meant match/index?


 
Of course!!!!


----------



## Cbrine (Dec 3, 2009)

hatman said:


> Sometimes I find it amazing how quickly some users jumpt to VBA, even though I was once one of those users myself (and still can be at times).  Under many circumstances, VBA can become an impediment to learning the native power of the worksheet environment.  I mean, why bother learning how to apply Match() and Offset() creatively when you can write a LeftLook() UDF?



I think I most likely blame Aladin for weaning me off the VBA addiction.  I saw some of the things that he was able to do with formula's that I had suggested code for.  While his were much harder to understand, they worked so much faster and didn't involve having to worry about user macro settings.  That being said, I still have a library of custom functions that do everything from formatting my workbooks(Structure,  print settings, Headers/Footers,etc..) to adding and removing protection with custom passwords.


----------



## xld (Dec 3, 2009)

hatman said:


> Sometimes I find it amazing how quickly some users jumpt to VBA, even though I was once one of those users myself (and still can be at times).  Under many circumstances, VBA can become an impediment to learning the native power of the worksheet environment.  I mean, why bother learning how to apply Match() and Offset() creatively when you can write a LeftLook() UDF?



What is WinXP 2002?


----------

