The Custom Software Approach: Excel VBA To The Rescue!

The methods we described for consolidating standard workbooks and non-standard workbooks can be used for a variety of tasks. Any project which requires the manipulation of data can be streamlined using these methods. That's the wonderful thing about training - there's no end to how you can apply your knowledge.

In our case study though, for such a large number of files, the real answer is custom software written in Excel Visual Basic for Applications (VBA). Excel VBA has been included in every installation of Office since Excel 95.

Even before VBA, Excel Macro Language (XLM) allowed for the automating of routine tasks. The point is that you don’t have to go out and buy new software, just use the software you already have.

If you’ve ever recorded a macro, you know a little bit about VBA. In its simplest form, VBA simply reproduces a sequence of keystrokes and mouse clicks in Excel. With a little bit of extra work however, VBA can completely automate tasks like the conversion of our tables from the field personnel into lists for analysis and database upload.

Loops Make Your Code Go 'Round

Let’s take a look at the simplest form of the Un-PivotTable. We want our program to start on Row3, read the title and remember 'Service1'. We want it to go to Column B, read the title, and remember 'None'. Then we want it to look at the intersection and get '4'. Finally, in a master list somewhere, we want our program to write:

Service1 None 4

The program should then go to the next column, 'Basic' and repeat. When it’s done all of the columns, it should start again on the next row until all the rows and columns are read and all of the relevant data recorded.

The central line in italics is not real VBA code. We have to clean that up. Programmers often write out plans in pseudocode when starting. Pseudocode describes the desired effect but doesn’t actually do anything. We use pseudocode above because we haven’t figured out yet HOW to get our list information into some central place. Let’s look at that part now.

Your Computer's Short-Term Memory

Variables are used in programming to store a value. In the code above we have two variables called RowNumber and ColNumber. As you can guess, the first time through the loop, RowNumber is set equal to 2. When our program sees the Next RowNumber statement, it adds 1 to RowNumber and checks to see if RowNumber is now greater than 13. No? Then keep going. Next time RowNumber is set equal to 4, to 5, to 6, etc. until it reaches 14 and knows that the loop is finished.

Variables are not always such simple values though. A variable can also be text like ServiceName in our example which might equal “Service1” or “Service2”. A variable can also be an entire Excel file. We’re going to use a variable MasterWorkbook to represent the Excel file where we want to store the consolidated data from all of the workbooks. We’ll use another workbook variable SourceWorkbook to represent, in turn, each of the Excel files sent to us by the field reps.

If you’re having trouble with the thought of variables, just imagine yourself doing the work manually. You would place your finger on the number you’re currently working on in order to memorize it, then type in the title of that row, the title of the column, and the number from memory. Immediately upon typing the number in, you forget it. This type of human memory, called short-term memory, is analogous to the use of variables. Use it, forget it, and memorize the next number for a few moments.

Where Does The Data Go?

We’ll also need to know where in the MasterWorkbook to put each line of data. For that, we’ll use a built-in Excel VBA property called UsedRange. As you might guess, the UsedRange is a big rectangle that usually starts at A1 and goes to the last column of any row and the last row of any column. Our source workbooks always end at F13: even though there may not be anything written in cell F13, there is always a title in column F and there is always a title in row 13, so the UsedRange goes to F13. In our MasterWorkbook, we will always write data on the first line after the UsedRange – that will be the first blank line in the workbook.

Now we can replace our pseudocode with:

This reads the hours of service from the source sheet depending on which row and column we’re currently reading and then looks in the MasterWorkbook to find out how many rows we’ve already written. Then it places the latest data on the first blank row.

We’re almost ready to look at the complete table-reading engine. You may be wondering where the variables ServiceName and PackageName get set. We haven’t set that up yet, so let’s do it now. Each time through a loop, we should set the names. It’s only necessary to change the names when we move to a new column or a new row. All of the service names are listed in the first column (A), but all of the package names are listed in the second row (the first row is taken up by a big title).

We also need to set our SourceWorkBook to the workbook we currently have open and create a brand new workbook for the master. You can see these two lines below.

We want to use the first row of the MasterWorkBook for titles. The three lines following the creation of the MasterWorkbook place the words Service, Package, and Hours on the first row.

One more addition, we need to give our program a name: we’ll call it the UnPivotTable.

If you would like to test this code, you can copy and paste it into the VB Editor in a workbook that has data arranged in it like ours. You can also download sample files from our website containing sample data.

To watch the code run, open Excel and then press <ALT-F11> to bring up the VB Editor. Then click on the Windows taskbar at the bottom of the screen and Tile Windows Vertically. If there are other Windows applications open, minimize them and do the steps above one more time.

Click inside of the VB Editor and click anywhere within the code you pasted. Hit the <F8> key repeatedly and watch as each line of code is executed. You may also hold down the <F8> key and watch the program run.

As the yellow highlighter moves up and down through the code, you will see how loops work and how the data is pulled from the source workbook and put into the master.

Watch the video showing how to set this up.

Are you ready for one more loop? How do we get a master workbook to loop through files as well as rows and columns? Finish this case study by reading about looping though all of the Excel files in a folder.

A Word From Our Sponsor