Storage Patterns for User Input
In the last chapter, I discussed developing the components of a spreadsheet-based wizard. The main example from last chapter had you review the infrastructure required to create a spreadsheet-based wizard. Whereas the last chapter concerned layout mechanics of creating an input interface, this chapter will deal with how to store the information once the user has finished their input. What follows builds from the previous chapter. You’ll still use the spreadsheet-based wizard implementation described in the previous chapter. However, going forward, you’ll make a few changes, which you’ll see here soon.
In this chapter, I’ll begin by describing a system of metrics that will become the inputs for your wizard. From there, I’ll describe the database scheme used to store information once it’s been completed. Finally, I’ll discuss handling typical database functions, like inserting a new record or deleting an existing one.
The World Health Organization : An Applied Example
In 2000, the World Health Organization ranked the healthcare systems of several different industrialized nations in a study called the World Health Report 2000 – Health systems: Improving performance. The study used five key metrics defined here:
- Health Level: Measures life expectancy for a given country.
- Responsiveness: Measures factors such as speed to health service, access to doctors, et al.
- Financial Fairness: Measures the fairness of who shoulders the burden of financial costs in a country.
- Health Distribution: Measures the level of equitable distribution of healthcare in a country.
- Responsiveness Distribution: Measures the level of equitable distribution of responsiveness defined above.
I’ll make some slight modifications to the original model used by the World Health Organization. For one, each country can score from 1 to 10 for a given metric. Second, I’ve generated a list of made-up countries. So, to be sure, all the data presented herein is notional. Except for the metrics used above (and the weights used in later chapters), the results have basically nothing to do with the actual results of the real model. That’s right, all data herein is fictitious. Any resemblance to real life data is purely coincidental. No spreadsheets were harmed in the writing of this book.
In this chapter, you’ll allow the user to create a new country, score each country based on metrics, and then store each result into a database. All of this will be self-contained in one spreadsheet file. In addition, you’ll be following many of the themes presented in previous chapters. You’ll rely heavily on named ranges and attempt to minimize unnecessary use of code.
Design of Your Spreadsheet File
You’ll be using the example file Chapter18Wizard.xlsm for this chapter. The file is made up of five of tabs, shown in Figure 18-1.
Figure 18-1. The five tabs you’ll be using for your workbook
Let’s go through each of these tabs.
- Welcome: Welcome is essentially your menu. When the user first opens the spreadsheet, it’s what they should see (think: “Welcome screen”). Figure 18-2 in the following section shows what the menu looks like.
Figure 18-2. All the different views of your wizard
- Wizard: Wizard contains your spreadsheet-based wizard.
- Database: Database contains the backend database you’ll be using to store country record data.
- Analysis: Analysis contains the spreadsheet analysis system you’ll be developing in the next chapter.
- Helper: Helper contains information about the spreadsheet application. For example, it keeps track of how many total views there are in the wizard. It also keeps track of the current wizard page. In the next few chapters, it will keep track of even more.
The wizard used here has changed substantially from the previous chapter. In this section, I’ll talk about some of those changes in design plus additional design enhancements. Figure 18-2 shows what your wizard looks like with all columns unhidden and zoomed out.
Tip If you zoom out to 39%, the name of your named ranges will appear on top of the area to which they refer.
As in the previous chapter, the inputs of the wizard have each been given a name. Figure 18-3 shows the named ranges given for the inputs in the first view. Figure 18-4 shows the names for the inputs in the second view.
Figure 18-3. Inputs on the first view
Figure 18-4. Inputs on the second view
If you ever need to change the location of these named—or want to see where they are located immediately—you can use the Name Manager. Figure 18-5 shows the named ranges used to create spreadsheet-level variables. This keeps you from having to store everything in the code, which is error prone and not ideal.
Figure 18-5. The Name Manager showing all your spreadsheet variables
Setting Focus to the First Input Cell
As the user clicks Next and Back in the wizard, one clear problem is that the selector doesn’t move with it. For instance, if you are on the first screen, and the Comment box is selected (having just typed in some value), when you click Next, the selector will still be on the Comment box. What you want is for the selector to automatically focus on the top of each screen.
To do this, you’ll set the first input box of each screen to follow the .FirstFocus pattern. For the first screen, you’ll create a new named range called Wizard.View1.FirstFocus (Figure 18-6).
Figure 18-6. Setting the .FirstFocus input cell of View 1
You’ll do the same for the second view (Figure 18-7).
Figure 18-7. Setting the FirstFocus for the second view
You then need to adjust your GoNext and GoPrevious procedures, which are displayed in Listings 18-1 and 18-2.
The new procedure that helps you focus on the first input cell in each view is SetFocusForView, which is highlighted in bold in the code. The code for the SetFocusForView procedure is shown in Listing 18-3.
Notice what SetFocusForView does. It takes in the current page number of the wizard. If you’re looking at the first view, it looks for the string Wizard.View1.FirstFocus. If you’re on the second page, it looks for Wizard.View2.FirstFocus. Obviously, since you have only two pages with input (the third page gives the user a few buttons to make a choice), you need ensure you’re not looking for a .FirstFocus cell where none exists on the page. Hence, you test to ensure you’re not in the last view before doing anything.
Now let’s take a moment to think about what you’ve built. In a broad sense, the code doesn’t care too much about what page you’re looking at so long as there is a FirstFocus on it. Moreover, if you make changes later, and want the FirstFocus to automatically start somewhere else, it’s as simple as changing where the name points in the name manager. Third, because you’re following a naming convention, it’s fairly clear that Wizard.View1.FirstFocus refers to the first input cell in the first View on the Wizard tab. (Compare this to other naming conventions commonly in practice, which might have used something like vw1_Focus1). Finally, you see that named ranges are super flexible. A cell can have more than one named range pointing to it at any given time.
In this section, I’ll talk about the interworkings of the database that serves to store user input. Figure 18-8 provides a snapshot of the database setup you’ll be working with.
Figure 18-8. The backend database storing country information filled in by the user
Figure 18-8 shows that the database is made up of three components.
- Input Entry table: Serves as the “living” record of current inputs from the wizard.
- Database Information table: Keeps track of the different pieces of information required to add, edit, and delete records.
- Database table: Keeps a record of all information stored currently in the database. I’ve aptly named this table “Database,” which you can see by clicking into the table and going to the Design context menu.
Let’s go through each section in detail.
Input Entry Table
The Input Entry table is what I like to call the “living record” of the current inputs from within the wizard. Figure 18-9 shows the actual formulas for the five metrics you’re capturing beneath their values. Notice that they connect directly to the named ranges found in your wizard. Unfortunately, because of the size of named ranges and page size, I wasn’t able to show full names, but you can readily understand what’s going on here.
Figure 18-9. The values in the Input Entry table link directly to the cells on the wizard
The only cell that doesn’t link directly is Country Id (Figure 18-10). I’ll go into more detail on that in the next few sections.
Figure 18-10. Country Id uses the current index and the table
Once you have all the inputs from the wizard in one spot, adding it into the table can be done in fell swoop. You simply need to copy the values from the Input Entry table into your Database table. Figure 18-11 shows how you’re going to do this conceptually.
Figure 18-11. A conceptual visualization of how you add a new record to the database
Because you don’t want to do a lot of read/write action on the spreadsheet (since those are volatile), the best way to do this is to simply copy the information from the living record down to the bottom of the table. Figure 18-12 shows the Input Entry with a named range of Database.InputEntry. When you save a new record, the SaveNewRecord procedure is called. Listing 18-4 shows the code for this procedure.
Figure 18-12. The result of adding a new record
What allows this code to work effectively is the use of Excel tables. A feature of these tables is their dynamic growth. When you add a new row of data right below its last record, it will subsume the new record. There’s no extra VBA code required for this action to take place. It happens automatically. And here you’ll use it to your advantage.
Your code finds the row count for all the data in the table. It then assigns the last row in the table to LastRowOfData. Next, you create a new range called NewRowOfData, which you tell Excel to place one row below the last. Next, you simply assign the NewRowOfData to be the same values as that of Database.InputEntry (one fell swoop, right?). Finally, you assign that new row of data a unique ID, which you’ll go into the next section. Figure 18-12 shows the result of running the code.
The Database Information table keeps track of all the information required to make changes to the Excel table. Figure 18-13 shows that the table is made up of three elements.
Figure 18-13. The Database Information Table
In this section, you’ll go through them.
- Record Count keeps track of the total records in the database. It uses the formula =COUNT(Database[Country Id]).
- Record Max keeps track of the maximum Country Id of all countries listed. You need to keep track of the maximum id for when you add records. The newest record will always be one plus the maximum record. This ensures each new record is always unique. The formula used is =MAX(Database[Country Id]).
- Current Index works keeps track of whether you’re editing a preexisting record or a new record. When Current Index equals negative one, you’re editing a new record. Otherwise, when you’re editing a preexisting record, Current Index will become the row index of the record being edited.
The most important feature of Current Index is that it never refers to a Country Id. You may find this confusing at first, but it’s a very important distinction. Figure 18-14 demonstrates this concept. In the Input Entry in Figure 18-13, you see you’re editing the country Ithha. Notice that while Country Id is five, Current Index is four. That’s because Ithha is located in the fourth row down in your database table.
Figure 18-14. Ithha has a Country Id of 5 but the record index is 4
You must separate location and Id. The reason is because later on in the chapter, you’ll be sorting on country name (in fact, you can see it’s already being sorted alphabetically in Figure 18-14). The location of the record could change with any update. In addition, you’ve also included the capability to delete records. Clearly, whatever country used to have a Country Id of 4 has been deleted from this table.
Here you use one of Excel’s most powerful capabilities—the table. There are several wonderful features of Excel tables that I’ll talk about in this section. For one, they allow for easy dynamic range references (there’s one exception to that, which I’ll get into in the next section). If I want to include the Country Name column in an Index function, I need only supply Database[Country Name]. That reference to the Country Name column is also dynamic: this means I can add or remove records—and Excel will automatically reflect these changes in the Database[Country Name] reference.
Another great feature is the table’s ability to expand to consume new entries. If I manually type in a new value in an unused cell directly adjacent to the table headings, Excel will expand to incorporate the new column heading. Likewise, if you add any data directly below the last record, the table will expand to consume the new record. The addition of new records is a boon to your development: you’re able to add records to the database by simply writing to the spreadsheet. There’s no extra overhead of grabbing the table object and inserting it. It’s always best to let Excel handle the heavy lifting for you. It’s not worth reinventing the wheel (perhaps I should say, “don’t reinvent the pie chart,” which is shaped like a wheel).
One other feature, which you will use in subsequent chapters, is the table’s calculated columns feature. Figure 18-15 provides an example. In the first row, I’ve selected the Health Level response for reach country and added an arbitrary amount to it (for demonstration). Notice, the syntax used is the @ symbol. You can think of that @ symbol as telling Excel that you want to do something with the values in Health Level at the same row as the current formula. Pressing Enter on the formula will automatically fill the formula down to the end of the row. You can see by the result in Figure 18-15, that each value in Test Column has added two to the respective values of Health Level in the same row.
Figure 18-15. A demonstration of calculated columns
Now let’s focus on what’s presented to the user when they first open the spreadsheet. Figure 18-16 shows the opening menu screen. In this section, I’ll go through the different elements.
Figure 18-16. The opening screen of your spreadsheet tool
As you can see, the opening screen is made up of several different elements. The most prominent of those elements are Excel shapes and a list box form control. As stated earlier, I am not a fan of using form control buttons (that look like old Windows 95 buttons) on the spreadsheet. Rather, I much prefer using clean-looking Excel shapes and assigning macros to them.
In this section, I’ll talk about creating a new record to be inserted upon its completion. Here, I’ve created a button called Insert New Record. But this may be a misnomer since it doesn’t insert a new record into the database; rather, it clears the wizard of its values and places the user on the wizard’s first input screen. From the user’s perspective, it prepares the wizard for the process of inserting a new record. See Listing 18-5.
As with most of my code, I’ve attempted to the keep the logic fairly straightforward. You set the CurrentIndex to -1 to tell Excel when you’re working with a new record. Next, you clear out any values in the table that may have been previously entered. Next, you tell Excel you want to start the user on the first page of entry. Finally, you activate the wizard to bring it into view.
In this section, I’ll talk about how to edit an existing record. This is where the Current Index from the Database Information table comes in. Figure 18-17 shows the cell link for the ListBox actually pointing to Database.CurrentIndex. Recall the cell link tracks the row index for a selected item. Figure 18-18 shows that since you’ve selected the fourth row, your Current Index (stored as Database.CurrentIndex) is 4.
Figure 18-17. Cell link refers to Database.CurrentIndex
Figure 18-18. Current Index is 4 because the list box on the front screen has the fourth row selected
You now work in reverse of when you add a record to the table. Since you know the row location of the record you want to edit, you simply need to fill this information in your Input Entry table. Figure 18-19 shows what this looks like conceptually. Listing 18-6 provides the code for the procedure.
Figure 18-19. What happens when you edit a given record based on the user’s selection in the list box from on the opening tab
This code is similar to the code in Listing 18-5. However, here you need to ensure that the values of the Input Entry table become that of the selected record. Notice in Listing 18-6 that you’re not assigning the cells of the Input Entry table directly. This is because that would overwrite their linkages to the wizard. Rather, you assign the values to the input cells of the wizard. This is akin to the user simply typing the information in themselves.
You might also notice that you use the constant numbers for the assignment. Generally, I don’t prefer this practice for other applications, but it works here in a pinch. So long as you’ve performed the requisite planning to ensure you won’t move the column assignments around. And, in fact, even if you did end up adding input boxes into the wizard and you had to update the input table, you could simply add another column adjacent to the Input Entry table. The order of inputs the user fills in within the wizard is not the same order you must follow when storing the information. So you can add even more variables to the store without changing the order of columns above. If, in another application, you must change these numbers in your code to accommodate the insertion of another variable, it’s best not to use this method (instead, go for named ranges for each cell).
In this section, I’ll talk about how to delete a selected record. On the opening screen, I allow the user to select a record from the list box to be deleted. Listing 18-7 shows the code to delete a selected record.
The code is fairly straightforward. You use the CurrentIndex to find the row location of the record you want to move. All you need to do is simply delete that row to remove it. The conditional in Listing 18-7 tests whether the selector is pointing to the last record in the table. If it is, you need to point it to the record that comes right before it since you’ll be deleting that record. If you did not do this, CurrentIndex would continue to point to a record that no longer exists. You can see the problem this would cause by placing the selector on the last item in the list box. If you press Delete, the record is removed. If you pressed Delete again, an error would occur since the selector would point to a row location that is now greater than the total count of rows in the list.
Linking the Column of Country Names to the Form Control ListBox
In this section, I’ll talk about how to automatically fill the list box with the list of country names from your backend database. Unfortunately, this is less straightforward than one might think. The problem stems from the ListBox’s inability to accept a direct reference to the backend database. You might think you could just type Database[Country Name] into the Input Range of the form control’s properties (refer to Figure 18-18). But doing this will generate a list box of blank data. Therefore, you need to create a dynamically sized named range using good ol’ fashioned functions.
Look back at Figure 18-18, and you can see you’ve specified the named range Database.CountryNameList. Let’s take a look at its formula.
=INDEX(Database[Country Name],1):INDEX(Database[Country Name],Database.RecordCount)
In previous chapters, I talked about creating dynamically sized functions such as these. The range operator (the colon) is what makes this formula work so seamlessly. Let’s look at Figure 18-20 while attempting to go through this function. The left side of the function INDEX(Database[Country Name],1) will always return the first record in the Country Name column of your table—cell B9 in Figure 18-19. The right side, INDEX(Database[Country Name],Database.RecordCount), will always return the last record in the table—cell B14 in Figure 18-20. Remember that Excel treats what INDEX returns as a cell reference, so behind the scenes Excel constructs the range B9:B14 on the fly based on this formula. If you added a record, Excel would construct the effective range B9:B15 on the fly.
Figure 18-20. Dynamic formulas help you construct this dynamic range on the fly
Looking back to Figure 18-16, it’s a matter of simply linking the ListBox’s input Range to this dynamic range.
The final button on the opening menu takes users to the analysis page. I’ll go over that in more detail in the next two chapters. In the meantime, look at the excerpted code in Listing 18-8. (Note this code is located in the Welcome sheet object.)
Now let’s focus on the buttons that appear in the third, summary view of your wizard (see Figure 18-21). In this section, you’ll go through each of these buttons. Here’s a quick summary of what they do:
- Save and Start New: Saves the current input and begins a new record from page 1 of the wizard.
- Save, and Go Back To Menu: Saves the current record and returns the user to the menu screen.
- Cancel: Does nothing with the current record and simply returns the user to the menu screen.
Figure 18-21. The summary view of your wizard
In this section, you’ll go over the Save, and Start New and the Save, and Go Back to Menu buttons. Listings 18-9 and 18-10 show their code, respectively.
Notice that both of these procedures perform the same functions. First, they test if the Current Index is -1. Again, you know if it’s -1 you’re dealing with a new record. Therefore, you call SaveNewRecord (Listing 18-4, from earlier in the chapter). Otherwise, you’re dealing with a record that already exists. In that case, you call SaveSelectedRecord (Listing 18-11).
The SaveSelectedRecord procedure works similarly to that of SaveNewRecord. However, because the record already exists on the table, you need not doing anything additional except set the values in the row location to those of the Input Entry table.
Returning to Listing 18-9 and 18-10, both procedures call the Database.SortCountryNames (Listing 18-12). As you make updates to the table, you want to keep the integrity of an alphabetical sort. Here, you use a simple command to the table to resort the data using the CountryName column. Note this procedure is actually in the Database sheet object (which is why you use Database.SortCountryNames).
Finally, returning once again to Listings 18-9 and 18-10, you see two both procedures differ with respect to their last line of code (which I’ve bolded). In Listing 18-9, you want to start the wizard over and insert another record. So you call InsertNewRecord (Listing 18-5). On the other hand, Listing 18-10 takes you back to the menu, so you call GoToMenu (Listing 18-13). Likewise, the Cancel button shown in Figure 18-21 calls GoToMenu directly.
In this chapter, you built upon the wizard from the previous chapter. You developed a backend database system that works seamlessly when complete. Whenever available, you let Excel do the work for you—by using formulas and features inherent to Excel’s tables. You also used quite a bit of code, but you were careful to make your code simple and readable. Specifically, you avoided using code for everything. By creating a proper balance between code, formulas, and features, you’ve built the beginnings of a robust Excel application. And that’s thinking outside the cell.