Best short examples of the need for Excel VBA

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

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.

Answer 2

My first tentative steps into VBA were taken after I joined a company and saw one of my new team spending 30 minutes each morning compiling a report from a list of about 1,000 items that needed auto-filtered in different ways to produce the required counts. A few hours mucking about with VBA had the task down to a button click and about a second.

Anything like this that involves a loop is going to satisfy your first criteria of a significant time saving. Perhaps a task that involves extracting the area codes or house numbers from a list of 200 phone numbers or addresses?

Answer 3

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 4

Are the people in the target audience power users?

If so, how about combining data from multiple workbooks using external references? I'm not sure if external references are the best way to do this, and I'm not sure how difficult this would be for someone new to VBA, but that's what I ended up doing in the past.

Example 1

There are many excel files following a naming convention:

  c:\data1.xls
  c:\data2.xls
  c:\data3.xls

I wanted to be able to enter the ID numbers in one column and have a VBA to get the data for me for all the other columns. I chose to do this with external references because then I didn't need to worry about opening and closing files and worrying about whether or not those files existed.

I wanted the result to look like this:

  id     data      hyperlink
  1      extRefA1  c:\data1.xls
  3      extRefA1  c:\data3.xls
  500    extRefA1  c:\data500.xls

I didn't need VBA to make the hyperlink, but I couldn't find an easy way to make external references without VBA. I tried using INDIRECT, but the referenced workbook had to be opened for INDIRECT to work. So, I used VBA to create the external references.

Example 2

This one is similar to Example 1, but I had to combine different chart data.

The data in each excel file were in columns:

  X    Y
  1    5
  2    10
  3    5
  4    60

I wanted the combined chart data in rows:

           1    2     3    4
  data1    5    10    5    60
  data3    30   60    4    2
  data500  25   45    20   5

So I made a VBA that put a formula array containing an external reference in a TRANSPOSE.

The data1 formula array looked something like this:

  =TRANSPOSE('c:\[data1.xls]Sheet1'!$B$2:$B$5)

I don't know how others use Excel and VBA, but these proved to be extremely useful to me.

Francis

Answer 5

Here is an awesome msdn link for this question. http://msdn.microsoft.com/en-us/library/aa203714%28office.11%29.aspx

has everything you need for a short preso.