Preparing a Worksheet for Data

One of the advantages of using Excel for user applications is the worksheet. Worksheets can be used to store data that is easily accessed by VBA. The data is visible and easy to modify (this can be both a blessing and a curse).

In this project, we will use an Excel worksheet as our database. It will be a flat database with column headers and records in worksheet rows.

Named ranges in Excel maintain their position even if rows or columns are added to or deleted from a worksheet. We will use this feature of Excel named ranges to make our data worksheet more dynamic. The named ranges will mark column header locations. Another advantage to this methodology is that you can change the names of your column headers without affecting your code.

Select the second sheet in your worksheet.
Name the sheet "Lists" (without the quotes) using the method detailed previously.
Type in the column headers as shown in figure below.

Make the headers bold and apply a fill color as shown in the figure.
Apply the column widths shown in the figure.
Type in three locations in cells A2, A3, and A4, as shown in the figure. You can use whatever places you want since the data is all pretend. These are going to be the three destinations for our trip.
Select cell A1, then click once in the Name Box.
Type "_places" (without the quotes), the press Enter to name the range; "_places" should show in the Name Box. Naming ranges is also detailed in the main course. (Range)

In a similar fashion, name B1 "_legs", name C1 "_air", D1 is "_rent", and E1 is "_bus". Check each range by clicking on it and checking the Name Box, or by doing a menu command Insert > Name > Define and checking the ranges of each of the names. (Tip: This Define Name dialog is where you delete named ranges.) We will use these names to get column numbers using VBA. This avoids "hard coding" the column numbers and allows the columns to be switched around or added to.

Save your work.

Heads-up About "Legs"

On the Lists worksheet we made a column labeled "Legs". This column will hold all possible combinations of trip legs. With the three locations we have included there are six possible trip legs, i.e., for each location you can travel to two other locations, and 3 X 2 = 6.

We could just type those trip legs into column B. But, what if we had 20 locations? The number of possible trip legs would be 380, and typing them in would be tedious. Instead, we will write some code in a few more lessons that creates the legs (see Create Trip Legs).

VBATrain Home | Samples TOC | Previous | Next