MS Excel: Should I Use VBA or Not?

I've been using Excel for years but only recently started learning VBA. Now when I want to do something in Excel I have to decide whether to write a VBA module to do what I want or just hammer out a quick and dirty solution using formulas. I am by no means a programming expert (if you can't tell already), but I have gleaned a few pointers that may be useful.

Using VBA

Pros:

• You can make turnkey products - everything is under the hood, reducing the likelihood that your user will break something.
• You can customize everything - write new functions, change formatting, etc.
• Many things are just not possible without VBA: loops, for example.

Cons:

• Programming is time intensive.
• If it does break, your user is in much more trouble than if your spreadsheet were non-VBA.
• The end product is less flexible. If you want to change something once you're done, you might need to spend hours rewriting all your modules. (How many times have you created something and they didn't request changes?)
• If you hard-code formulas, users can't tell what is going on unless they pop the hood and understand your coding.

Let's look at two examples. I just built two versions of Boggle -- the board game where you use letters on dice to spell words. The first version has almost no VBA, and the second is mostly VBA.

VBA version

You'll quickly notice that the VBA version is very clean - only one tab; just press a button and go. It also has functions that are very difficult without some coding, like the button that rotates the board.

non-VBA version

Now look at the non-VBA version (these were made at different times -- sorry the formatting is slightly different). You can peek at the "Data" tab and see exactly what is going on. I'll admit I patted myself on the back for figuring out how to do all of the calculations. You'll notice that the spreadsheet doesn't work right unless you go to "Formulas -> Calculation Options -> Automatic Except for Data Tables". This enables the scoring formulas to work but forces you to manually recalculate the spreadsheet when you want a new board. Normally, changing the formula calculation settings on a product is a big no-no. There's a high likelihood that the client will forget and probably won't be able to figure out what is wrong.

In the end both version work, but I think we can find a useful way to categorize features:

1. Features that can be easily done without VBA (e.g., formatting)
2. Features the client will want to tinker around with (e.g., decision-making formulas)
3. Features that could easily break and cause headaches
4. Things that can only be done with VBA

One common pitfall is when a developer (who probably learned programming first and spreadsheets second) does some basic requirements gathering and jumps right into the coding. They often don't know that there are Excel formulas for many of the things they normally do in VBA. If you are an end user requesting a product, you may want to mock up a prototype of the user interface, with all of the formatting and formulas you will use, and then draft your requirements document outlining the modules you will need and where they tie in. If you do this you will probably get something closer to what you want and the developer will thank you.