Best short examples of the need for Excel VBA [closed]

Question

Here's one for Joel...

I am looking for ways to demonstrate to an Excel user (with no programming experience) how learning some Excel VBA can make their life working with Excel a little easier.

First thoughts are to use an example that replicates manual tweaking of a spreadsheet, such as one click conditional formatting of all the data. For example: highlighting all the numbers red, orange or green according to user input thresholds coupled with some other derived data such as current business week.

I am hoping that such a short VBA example should not be too difficult to grasp for someone who has never written a line of code before, and hopefully make a case for trying to learn a bit of Excel VBA.

However, with this example the time taken to code it is not significantly quicker than applying the conditional formatting manually in Excel. So I would be interested to know if anyone in the community has any more elegant examples that demonstrate the advantages of using Excel VBA.

Ideal examples would have the following characteristics:

1. Significant time savings (large T, where T = time for manual procedure / time to code).
2. Non-abstract, everyday spreadsheet examples.
3. End results that can not be easily achieved manually.
4. Achievable with short, basic VBA code.

Bear in mind that the target audience is taking their first steps into programming.

Answer 1

If you can, watch them use Excel for a 1/2 hour and you'll find the perfect opportunity. When they open that one spreadsheet, autofit all the columns, format col A as a date, right justify col J, delete rows 2 through 5, and change the print orientation to landscape then you've found a winner. Have them do it again, but with the macro recorder on. Then replay the macro recorder.

By working with something they use in real life, it will have more impact.

You don't have to save them 1/2 hour a day with the first shot. Save them 30 seconds of drudgery on something they'll use and they'll start thinking of all the things they want automated. In my experience, they'll go overboard rather quickly. In no time, they'll want Excel to go fill out a web form, import the information, and get them a coffee.

Answer 2

Create your own "function" with VBA that you can use like another function from within the sheet.

You can do things that are not possible in plain Excel, or very hard to implement or reuse.

An example:

In VBA create a new module, add code like this:

Public Function SizeOfFile(a As String)
   SizeOfFile = VBA.FileLen(a)
End Function

And you can now use SizeOfFile in a formula in a cell.

If cell A1 contains the name of a file, B1 fill with =SizeOfFile(A1) to get the size.

Also

You can show recording (and editing) a macro, to repeat steps that you do often.