Excel is already exciting, and then you add macros or VBA (Visual Basic for Applications) into the mix and WOAH! When I started using Excel VBA with regularity, it seemed like the right VBA code would allow me to see into the future.

I’ve used VBA for

  • Web-scraping
  • Parsing data in ways that wouldn’t have been possible through worksheet functions
  • Automating many types of reports and processes for clients
  • Building interfaces to ease data entry
  • Creating forms

The power that’s available is almost frightening. I’ve used VBA to break the passwords on other spreadsheets. It’s seductive to start thinking in VBA and head straight to the VBA editor with every task. But wait a second!

There are good reasons to avoid (or at least be cautious about) using VBA. Here are 6

6 Sometimes writing, testing and debugging the script will take longer than using worksheet features.      
5 VBA does not adjust in the way that formulae do when you move data from one worksheet to another, insert a column, delete rows, etc.
Example: you have a sheet called February. Now you want to rename it Feb. A formula will adjust. VBA will not. The VBA code has to be manually modified, or you just live with the sheet being called February.
4 Some users freak out when they open the workbook and see the security warning about enabling macros.
3 There is no UN-DO! This bears repeating: There is no UN-DO!
If the macro is designed to delete information and you run the macro at the wrong time or, the code is wrong and deletes work you spent hours creating … [insert expletive]! You’ll look up and see the UN-DO arrow grayed out.
2  If you don’t use good comments in your code, you’ll struggle to understand your own work if you have to revisit it weeks or months later.
1 Truly, the Numero Uno reason for avoiding (or limiting) use of VBA:
Many MANY users aren’t aware of VBA or they don’t understand it. So, if the VBA-happy developer quits to go chase other dreams in a foreign land, it’s going to be an added challenge for the users to get the document modified or repaired. Most folks do know an Excel wiz in their life. But their Excel wiz may not be familiar with VBA AND have the wherewithal to do the forensics on someone else’s code.
Thus, some users can feel like they’ve been abandoned to poke around in a dark alley called The Twilight Zone VBA Editor.

Excel VBA is splendid. I set it to run a task that took 48 hours, while I went on with my life and occasionally checked to ensure that the job was still running. My only warning is that VBA is like sriracha: you can over-do it. (Well … it’s harder to over-do sriracha, but that’s another discussion.)

Does anyone else have comments, questions, warnings or stories to share about VBA?

UPDATE (3MAR14) Check out 3 Reasons To Use VBA. I am actually a VBA fan