1.5 A Simple Literature Database


1.5 A Simple Literature Database

The following example is already a fairly concrete application (if perhaps still a bit elementary). It is located in the sample files under the filename books.xls.

This workbook (or "program") makes possible the management of a collection of books, perhaps, for example, a small library. The list of books can be arbitrarily enlarged, sorted by title or by author, selected according to one or another of several criteria (for example, only books on the courting behavior of sea urchins), searched by subject, and so on. The use of the application is simplified by several buttons , so that to search for a book or to enlarge the database requires no Excel specific knowledge.

Step 1: Preparing the Database, Arranging Windows

The construction of this application begins with the input of data on several books and constructing a table along the lines of Figure 1-7. This has nothing much to do with macro programming. It is merely a matter of constructing a normal Excel table. For now you should ignore the buttons and filter arrows.

click to expand
Figure 1-7: A simple database application

(If you prefer, you could just as well create an address database, an index of students, or something else entirely. You do not need to adhere precisely to our presentation. The more creatively and independently you work, the more you will learn.)

Some suggestions for formatting the table: All cells of the table have been formatted with the text aligned vertically at the top of the cell (FormatCellsAlignment). The cells in the title column have been given the attribute "wrap text" so that longer titles will automatically be displayed over several lines. The top two rows have had their height significantly enlarged. The entire second line has been formatted with a light gray background (FormatCellsPatterns).

A comment is stored in cell C2 (content: Category). (Comments are input by executing InsertComment. Beginning with Excel 7 comments are automatically displayed when the mouse is passed over a cell containing a comment.) The comment in cell C2 will be used to explain the category code: N= novels , C=computer books, etc. Cells in which a comment is stored are marked with a red triangle in the top right-hand corner. If this triangle is not shown, execute ToolsOptionsView, and activate the option "Comment indicator only."

Some tips for arranging windows: The window was divided horizontally and arranged so that in the top region of the window the name of the database (two fairly high rows) is visible (WindowSplit; arrange the dividing cross with the mouse, and fix it with WindowFreeze Panes). With ToolsOptionsView you can turn off the labeling of the row and column headers as well as the display of gridlines, the horizontal scroll bar, and the sheet tabs.

You do not need to inform Excel that it is dealing with a database. For Excel to recognize the database you have merely to move the cell pointer anywhere in the database. In Excel any connected range of cells is considered a database.

Therefore, you can immediately try out the database commands, such as sorting the table by a particular criterion (author, title, year of publication). Simply execute Data Sort . Another important database command is Data: This opens a form for entry and editing of data.

Step 2: Equipping the Database with Filters

With the command DataFilterAutofilter small filter arrows in the table's title cells are shown. When you click on these arrows with the mouse you can select filter criteria, such as a particular publisher or a publication year. The database will then display only those data that meet this criterion. To indicate that not all data are visible Excel turns the filter arrow blue. Several filtering criteria can be combined (such as all books of publisher x published in year y). You can even establish criteria such as showing all books that were published between 1980 and 1990 (a user -defined criterion).

Step 3: Buttons and Macros

As an experienced user of Excel you have presumably not had any problems using the database in its current configuration. Using the menu bar you can sort data according to chosen criteria, you can input and edit data, and so on. However, if you would like someone who is a total Excel novice to be able to use the database, then you need to make the interface a bit more user friendly. In the current example buttons have been added to the table that make it possible to execute the most important functions without a long search through the menu bar. (Of course, there are other possibilities for setting up the controls, such as a menu from which all but the truly important items have been deleted.)

To add buttons you activate the "control toolbox." (The control toolbox is part of the toolbars available for Excel. It can be shown using menu command ViewToolbar.) Then click on the "Command Button" tool, and with the mouse click in the worksheet to insert the button. (This will automatically activate design mode, which enables further work on the button.)

Note  

You can save time by carrying out these formatting steps for the first button only. Then copy this button several times, which is accomplished by dragging the button with the mouse with the Ctrl key held down. If you hold down the Shift key as well, then the copied button has the same horizontal position as the original one, so that the buttons appear to belong together. Finally, you must define labels and names for each of the buttons.

Now comes the formatting of the button. In the pop-up menu obtained by clicking on the new button with the right mouse button choose Commandbutton ObjectEdit. Ctrl+Return begins a new line, Esc terminates input.

All other properties of the button are entered via a properties window (Figure 1-8), which can be summoned either by using the button's pop-up menu or by clicking on the "properties" tool. There you should enter the following properties:

  • Name: Give the control a meaningful name, such as btnSort for "sorting button."

  • Font: Enlarge the font size to 10 points and choose the attribute "bold." You do this by clicking in the "font" field, which enables a button that if pressed opens a window in which font attributes can be edited.

  • ForeColor: If you like buttons with a bit of pizzazz, here you can select a different text color .

  • TakeFocusOnClick: Select the property False , so that the VBA code will be correct. (More on this in Chapter 7.)

click to expand
Figure 1-8: The control toolbox (left); the table window (middle); the properties window with settings for the selected button (right)

Now the buttons have to be linked to the program code. A double click on the button takes you to a template in the module "Sheet1" for an event procedure that will be automatically executed when the button is pressed. The name of the procedure is a combination of the control name (such as btnSortAuthor ) and the event (usually Click ).

 ' books.xls, Module "Table1" Private Sub btnSortAuthor_Click() End Sub 

The program code is recorded as in the previous examples. You then move the resulting instructions with Copy and Paste from the recorded module into the code template. If you have just been working on the example in the previous section, then "relative recording" mode is still active. You should be sure to deactivate it before recording.

Now onward to the content of the macro! For the two Sort macros first click cell A2, then execute DataSort, and enter the desired sort criterion in the dialog (author or title). For data entry place the cell pointer once again in A2 and select DataForm. Before you can record the macro Display All Data, you must choose some filtering criterion (for example, show all books published before 1993). That is, the command DataFilterShow All is available only when at least one filtering criterion is active.

For the Save macro simply execute the command FileSave. The macro Datasearch must be input directly into the module via the keyboard (see below for the code). The macros should look something like this:

 ' books.xls, "Table1" ' Sort by Author Private Sub btnSortAuthor_Click()   Range("A2").Select   Selection.Sort Key1:=Range("A3"), Order1:= _     xlAscending, Header:=xlGuess, OrderCustom:=1, _     MatchCase:=False, Orientation:=xlTopToBottom End Sub ' Sort by Title Private Sub btnSortTitle_Click()   Range("A2").Select   Selection.Sort Key1:=Range("B3"), Order1:= _     xlAscending, Header:=xlGuess, OrderCustom:=1, _     MatchCase:=False, Orientation:=xlTopToBottom End Sub   'Show Database Form, Click on "New"-Button Private Sub btnInput_Click()   Range("A2").Select   SendKeys "%w"   ActiveSheet.ShowDataForm End Sub ' Show Search Form Private Sub btnFind_Click()   SendKeys "^f" End Sub ' Show All Data Records Private Sub btnShowAll_Click()   On Error Resume Next   ActiveSheet.ShowAllData End Sub ' Save Private Sub btnSave_Click()   ActiveWorkbook.Save End Sub 
Note  

In order to try out the buttons you must exit design mode (the first tool in the "Control Toolbox" toolbar).

Remarks for Advanced Users

Clicking cell A2 during the recording of a macro (it could be any cell in the range of the database) is necessary, because the database commands function only when the cell pointer is located within the range of the database. Since later the buttons will also be able to be pressed when the cell pointer is located elsewhere, at the beginning of the macro it must be placed explicitly in the range of the database.

In the macro btnInput_Click the SendKeys command must be inserted. It simulates the keyboard input Alt+W , by which in the dialog for the database form the button New is selected. This prevents the user from accidentally overwriting an existing entry. (Because of the not quite plausible operation of the database form this happens almost inevitably the first time.)

The order of the commands SendKeys and ShowDataForm seems illogical. It would appear as though first the form should be opened, and then the keyboard entry simulated. However, the command SendKeys merely has the function of placing the key combination Alt+W in a keyboard buffer, where (at some later time) it is made use of by Windows. If SendKeys appeared below ShowDataForm in the macro, then Excel would wait to execute SendKeys until the input in the database form was complete ”and that, of course, would be too late.

The macro btnFind_Click also uses SendKeys , for invoking the Search dialog. It would certainly be possible to show the dialog with Dialogs(xlDialogFormulaFind).Show , but it is not possible actually to find data. (The search is limited for unexplained reasons to the current cell.) This problem has been around since version 5!

Finally, an explanation of the macro btnShowAll_Click is necessary, in which the instruction On Error Resume Next may have caught your attention. This instruction has the effect of continuing the execution of the macro in the next row without an error message when an error occurs. In this macro an error can easily occur, namely, whenever the user clicks on the button Display All Data when no filtering criterion is active.




Definitive Guide to Excel VBA
Linux Application Development For The Enterprise (Charles River Media Programming)
ISBN: 1584502533
EAN: 2147483647
Year: 2002
Pages: 134

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net