11.6 Example: Balance Sheet for a Car-Sharing Club

11.6 Example: Balance Sheet for a Car-Sharing Club

The car-sharing example has the character of a complete (and not entirely atypical) Excel application. The application DB_Share.xls is derived from the smart form Share.xls of the car-sharing club presented in Chapter 9. The program makes it possible to use a smart form to maintain a simply constructed logbook. What is new in comparison to Share.xls is the situation that now all trips are logged in a monthly balance sheet, and the administration of the fleet of cars and the usage thereof by the members of the club are integrated into the application. The program is run via a custom menu.

Running the Program

After the file DB_Share.xls is opened, the smart form familiar from Chapter 9 appears on the monitor together with a custom menu (see Figure 11-13). Other Excel elements such as the formula and status bars and the toolbars have been deactivated. Little has changed in the operation of the form. New is that the name of the individual who has borrowed a car can now be easily selected in a listbox.

Figure 11-13: The smart form appearing in the application DB_Share.xls

After the form is filled out, it is printed with InvoicePrint And Save and saved in the table of monthly accounts. The input of a second invoice must be effected with InvoiceNew. This way the invoice receives a new (running) invoice number and all the input fields of the form are cleared. InvoiceCorrect Old Invoice Of This Month enables the selection of an invoice computed in the current month. The data are transferred into the form, and there they can be edited. Finally, the corrected invoice can be printed and saved with InvoicePrint And Save.

With the View menu you obtain access to other sheets of the application: ViewMembers Database takes you to the database of club members (Figure 11-14), where new members can be entered or the data on current members updated. The Sort button can be used to sort the database alphabetically after it has been added to or edited. The sequence of names is reflected in the listbox in the invoice form.

Figure 11-14: Data on club members

Note in particular the first entry in this database. It is the text "please choose a member" or " please choose a car" that includes a prefixed blank character. This blank character has the effect of placing this entry in the sorted listbox before all the others, enabling the program to achieve a definite starting condition for creating the list or establishing this condition in a query. (In using MS Forms listboxes in worksheets there is no possibility ”in contrast to Excel 5/7 listboxes ”to distinguish the selection of the first list entry from the condition that no list entry at all has been selected. Both conditions produce the value 0 in the cell specified by LinkedCell .)

ViewCar Database switches into a worksheet for managing the transport fleet (Figure 11-15). In the table is stored the list of all the cars together with their rate data.

Figure 11-15: Fleet management

ViewAccount Of This Month shows the balance sheet table for the current month (Figure 11-16). These tables are stored in separate files. The file name contains the year and month: for example, Car_2000_01.xls for Januar y 2000. When a new file is created automatically (at the beginning of the month) the template Car_template.xlt is accessed, which must be located in the same folder as the application file DB_Share.xls .

Figure 11-16: Monthly balance sheet

In the numerous columns of the monthly table are saved the invoice number, invoice date, date and time of the last change in the invoice, name, car, time when the car was used, the number of miles traveled, and so on. The balance sheet table contains all relevant data for the next step, which might be creating a monthly invoice for each member of the club or performing a statistical analysis of automobile usage.

The last menu to describe is the Car-Sharing menu. Save, not surprisingly, saves the file DB_Share.xls . This ensures, in particular, that the changes in the databases "fleet" and "members" are saved. The monthly balance sheet in the file Car_yyyy_mm.xls is completely independent of DB_Share.xls and is updated automatically after each change. This costs some time, of course (particularly if the file is large), but from the standpoint of data security it is the only safe way to proceed.

Car-SharingEnd closes the files DB_Share.xls and Car_yyyy_mm.xls and removes the Car-Sharing menu. Excel is not itself shut down; that is, END refers only to the application DB_Share .

Suggestions for Improvements

• In its present form the application is highly susceptible to accidental or intentional abuse. The possibility of changing existing invoices with InvoiceCorrect Old Invoice or opening and changing the monthly report directly can, of course, be used for dishonest purposes. There are various measures that can be taken to improve the security of the data (for example, the monthly balance sheet could be password protected, or changes in invoices could be marked in the invoice). But complete protection that no Excel pro could crack is unlikely to be obtained.

• The monthly balance sheets could serve as a starting point to send to each member at the end of the month a list of all trips made together with an invoice (instead of billing for each trip individually, which in practice involves too much effort and paperwork). Further, payments could be kept track of, reminders sent, and so on.

• A booking and reservation system could be implemented.

Although it is possible in principle to include these new features using Excel tables, it would be more sensible , and certainly more secure, to store the data in an external database file.

These ideas demonstrate the typical path taken by many Excel applications. From a simple idea (the balance sheet of Chapter 9) a more and more complex database application is developed. The problem that arises is that Excel is really a spreadsheet program and not a database program. Although almost any extension is possible, the ratio of programming effort involved in relation to the benefit gained is an increasing spiral. Before your application starts putting on weight, consider transferring to a database program (and the sooner, the better!).

Overview of the Components of the Application

The application consists of at least two files: DB_Share.xls for the program code, the invoice form, and the fleet and member databases; and the template Car_template.xlt for the monthly balance sheet. Furthermore, every month in which the program is used produces a file Car_jjjj_mm.xls with the monthly statement.

The file DB_Share.xls consists of the following sheets and modules:

invoice	worksheet with invoice form
cars	worksheet with car database
members	worksheet with member database
ThisWorkbook	class module with event procedures
moduleMain	code for monthly account report
moduleMenu	code for car-sharing menu
modulefunctions	code with custom worksheet functions (see Chapter 9)

Within the invoice form (that is, in the worksheet invoice ) most of the input and output cells are named, so that they can be more easily accessed in program code. This makes it possible to avoid having to access cells in the form [H17], the accuracy of which is hard to verify. Here is a list of the names and their addresses:

car	C14
enddate	E26
endtime	E19
fuelcost	D35
hoursI	D21
hoursII	D22
hoursIII	D23
invoicedate	G9
invoicenr	B9
invoicetotal	G38
membername	C12
nrOfMiles	D32
startdate	D26
starttime	D19
weekendbonus	D28

Tip  

If you need a list of all named cells for documentation purposes, simply execute in the immediate window

 For Each n In ThisWorkbook.Names: ?n.Name, n.RefersTo: Next 

Both in the invoice form and both database worksheets the row and column headers, gridlines, and sheet tabs are hidden in order to obtain maximal use of the available space.

Program Code

The following pages describe the most interesting details of the program code. The procedures are described in order of appearance, where we have declined to present a repeat of the user -defined worksheet functions (see Chapter 9).

Global Variables

The division of the program code into several modules created the necessity of declaring certain variables as Public . These variables can be accessed from any module. With the exception of the variable accountMonth , which contains the current year and month as a character string (for example, "2000_05" for May 2000) all other variables relate to the monthly table: monthReportWb , month Report , and accountCell contain references to the workbook, the worksheet, and the first cell of the monthly table. All three variables are initialized in LoadMonthReport (see a few pages below).

The constant pagePreview specifies whether invoices should be printed or simply presented on the monitor in page view.

 ' DB_Share.xls, moduleMain Public monthReportWb As Workbook 'workbook with monthly account Public monthReport As Worksheet  'sheet in monthReportWB Public accountCell As Range      'first cell in monthReport Public accountMonth$             'year + date (2000_05 for May 2000) 

Configuring Excel in Workbook_Open

After the file DB_Cars is opened, the procedure Workbook_Open is automatically executed. This procedure makes the invoice form the active sheet. Then with LoadMonthReport the monthly table of the current month is opened. ClearMainSheet takes the current invoice number from this file and inserts it into the invoice form. At the same time, all input fields of the invoice form are cleared. Finally, the additional toolbar "DB_Car_Sharing" with its new menu items is activated.

If you activate lines that have been commented out in the program listing, then at the start all of Excel's toolbars, formula bar, and status bar are deactivated. They are not required for using the program and they just take up space.

 ' DB_Share.xls, "ThisWorkbook" Private Sub  Workbook_Open  ()   Dim cb As CommandBar   Application.ScreenUpdating = False   ThisWorkbook.Activate   Sheets("invoice").Select   ThisWorkbook.Windows(1).DisplayWorkbookTabs = False   LoadMonthReport                    'load account of this month   ClearMainSheet                     'clear invoice form   ' hide formular bar, status bar and toolbars   ' Application.DisplayFormulaBar = False   ' Application.DisplayStatusBar = False   ' For Each cb In Application.CommandBars   '   If cb.Type = msoBarTypeNormal Then cb.Visible = False   ' Next cb   With Application.CommandBars("DB_Car_Sharing")     'show toolbar of this application     .Visible = True   End With   ActiveWindow.WindowState = xlMaximized End Sub 

Managing Custom Menus

After the application-specific menu has been activated in Workbook_Open , the sheet-(de)activation procedures see to it that in the future this menu is deactivated when a worksheet of another Excel file is clicked on and is reactivated when a worksheet of DB_ Share.xls is clicked. (In Workbook_Deactivate the menu remains visible if the invoice table is clicked on.)

 ' DB_  Share.xls  , class module "ThisWorkbook" ' deactivate menu when another workbook is activated Private Sub  Workbook_Deactivate  ()   On Error Resume Next   If LCase(ActiveWorkbook.Name) <> "car_" + accountMonth + ".xls" Then     Application.CommandBars("DB_Car_Sharing").Visible = False     End If End Sub ' ensure that menu bar is always visible Private Sub  Workbook_Activate  ()   Application.CommandBars("DB_Car_Sharing").Visible = True End Sub Private Sub  Workbook_SheetActivate  (ByVal Sh As Object)   Application.CommandBars("DB_Car_Sharing").Visible = True End Sub 

Open Excel File for Monthly Report

LoadMonthReport is called at various points in the program to ensure that the file with the monthly report is loaded. (It can happen that the user has accidentally closed it.) The procedure first sets the variable accountMonth to a character string of the form Form "2000_05" (May 2000) and from it and the path to DB_Cars determines the file name of the invoice file (such as C:\Test\Car_2000_05.xls ). Then a loop is run over all loaded Workbook objects. If the monthly file is found, then various variables must be initialized.

On the other hand, if the file is not loaded, then a test is made as to whether the file exists on the hard drive. If it does not exist, which will be the case the first time in the month that the application is run, then a search is made for the template file Car_Template.xlt . If this file is found, then the program opens this template file. Otherwise, it opens an empty (unformatted) Excel file. In principle, the program does not complain in this case, but the template file provides the advantage that the monthly table is labeled, the column width is set more or less correctly, and the individual columns are correctly formatted (date and time format).

In any case, the new file's name is entered into cell A1, and the current invoice number in A2. Then the file is saved under a new name. In the last lines, which are always executed regardless of how the file was found or opened, the window of the file is reduced to an icon.

 ' DB_Share.xls, moduleMain ' load account file for current month Public Sub  LoadMonthReport  ()   Dim wb As Workbook   Dim reportFile$, templateFile$   Dim loaded As Boolean   loaded = False   accountMonth = CStr(Year(Now)) + "_" + Format(Month(Now), "00")   reportFile = ThisWorkbook.Path + "\car_" + accountMonth + ".xls"   'test, if file has already been loaded   For Each wb In Workbooks     If UCase(wb.Name) = UCase("car_" + accountMonth + ".xls") Then       Set monthReportWb = wb: Set monthReport = wb.Worksheets(1)       loaded = True       Exit For     End If   Next wb   If Not loaded Then     If Dir(reportFile) <> "" Then       'file does already exist ---> load       Set monthReportWb = Workbooks.Open(reportFile)       Set monthReport = monthReportWb.Worksheets(1)     Else       'file does not exist: open template (if it does exist)       templateFile = ThisWorkbook.Path + "\car_template.xlt"       If Dir(templateFile) <> "" Then         Set monthReportWb = Workbooks.Open(templateFile)       Else         'template is missing also; simply use an empty file instead         Set monthReportWb = Workbooks.Add       End If       Set monthReport = monthReportWb.Worksheets(1)       monthReport.[A1] = reportFile 'save filename in A1       monthReport.[A2] = 0          'save invoice nr in A2       monthReportWb.SaveAs reportFile     End If   End If   Set accountCell = monthReport.Range("A5")   monthReportWb.Windows(1).WindowState = xlMinimized End Sub 

Initialize Invoice Form

At various places in the program ”such as at the start or when the Next Invoice button is clicked ”the invoice form must be translated into a defined basic form. That is, all input cells are cleared. These input cells were named in the worksheet "invoice" so that a more readable access is possible in the form [name] . Furthermore, the procedure takes the current invoice number from the monthly report (which is located there in cell A2).

In InitializeListboxes the source data range (property ListFillRange ) is set afresh for the listboxes cmbMembers and cmbCars . Then the listboxes are set to entry 0, so that the first list entry is displayed (that is, "please choose a member" or "please choose a car").

 Public Sub  ClearMainSheet  ()   Application.ScreenUpdating = False   ThisWorkbook.Activate   Sheets("invoice").Select   LoadMonthReport   With ThisWorkbook.Sheets("invoice")     .[invoicedate].Formula = "=Now()"     .[startTime] = ""     .[endTime] = ""     .[startDate] = ""     .[endDate] = ""     .[nrOfMiles] = ""     .[fuelcost] = ""     .[invoicenr] = "Invoice " & accountMonth & "-" & _         monthReport.[A2] + 1     InitializeListboxes   End With End Sub ' reset listboxes in invoice form to 0 Public Sub  InitializeListboxes  ()   Dim z1 As Object, z2 As Object   With ThisWorkbook.Sheets("invoice")     ' List box members     Set z1 = [members!A4]     Set z2 = z1.End(xlDown)     .cmbMembers.ListFillRange = "members!" + z1.Address + ":" + _       z2.Address     .cmbMembers = 0     ' List box cars     Set z1 = [cars!A4]     Set z2 = z1.End(xlDown)     .cmbCars.ListFillRange = "cars!" + z1.Address + ":" + z2.Address     .cmbCars = 0   End With End Sub 

Removing Keyboard Focus from Listboxes

There is one further detail in connection with the two listboxes in the invoice worksheet that is worthy of mention: After the selection of a car or a member via the listbox, the input focus is located in the associated listbox. This can block the further execution of VBA code. For this reason, the event procedure cmbCars_Change or cmbMembers_Change is invoked to switch the keyboard focus immediately back to a nearby cell.

The test ActiveSheet.Name = Me.Name ensures that this happens only when the invoice worksheet is the active sheet. (The procedures are also called when the listboxes are changed in program code, and at that time another sheet can be active; the attempt to activate a cell would then lead to an error.)

 ' DB_Share.xls, invoice Private Sub  cmbCars_Change  ()   If ActiveSheet.Name = Me.Name Then     [b14].Activate   End If End Sub Private Sub  cmbMembers_Change  ()   ' as above End Sub 

Event Procedures for the Database Buttons (Database Form, Sorting)

In each of the two worksheets "members" and "cars" there are two buttons, one for displaying the database form and the other for sorting the entries. (Sorting also takes place automatically after the database form has been displayed.)

When the worksheet is exited, the procedure InitializeListboxes displayed directly above is executed so that all new entries in the listboxes are taken into account. The code for the two worksheets is identical:

 ' DB_Share.xls, cars ' show database mask ' afterwards sort and reinitialize listbox Private Sub  btnEdit_Click  ()   Range("A3").Select   Range("A3").CurrentRegion.Name = "database"   ActiveSheet.ShowDataForm   btnSort_Click   InitializeListboxes End Sub Private Sub  btnSort_Click  ()   Range("A3").Select   Selection.Sort Key1:=Range("A4"), Order1:=xlAscending, _     Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _     Orientation:=xlTopToBottom End Sub Private Sub  Worksheet_Deactivate  ()   InitializeListboxes End Sub 

Event Procedures for Menu Commands

moduleMenu contains short procedures that are called from the commands of the car-sharing menu. Most of these procedures are extremely short; the code should be understandable at a glance, for which reason it is not reproduced here.

Printing the Invoice Form

The procedure PrintAndSave is responsible for printing the invoice form. This procedure copies the entire invoice form into a new worksheet, changes the yellow background color to white, and then prints the new worksheet. The analogous procedure btnPrint_Click is presented in Chapter 9, where it is described more fully.

What is new in PrintAndSave is the call to the function TestForValidInput : There a short test is carried out to determine whether the input in the invoice form is valid. If that is not the case, then the function returns an error text that is displayed in an alert box in PrintAndSave .

 Function  TestForValidInput  () As String   Dim errmsg$   With ThisWorkbook.Sheets("invoice")     If IsError(.[invoicetotal]) Then       errmsg = "Invalid total."     ElseIf .[invoicetotal] = 0 Then       errmsg = "Total is 0."     ElseIf .cmbMembers <= 0 Or IsNull(.cmbMembers) Then       errmsg = "Member name missing."     ElseIf .cmbCars <= 0 Or IsNull(.cmbCars) Then       errmsg = "car name missing."     ElseIf .[startTime] < 0 Or .[startTime] > 1 Or _            .[endTime] < 0 Or .[endTime] > 1 Then       errmsg = "Wrong time."     ElseIf .[startDate] <> "" Xor .[endDate] <> "" Then       errmsg = "Incomplete date."     End If   End With   TestForValidInput = errmsg End Function 

Saving the Data from the Invoice Form in the Monthly Report

The procedure PrintAndSave ends with a call to SaveAccountData . This procedure transfers all the basic information of the invoice into the monthly table. The row in which the data are entered is determined from the invoice number of the invoice form, which must be extracted from a character string of the form "Invoice 1999_05\3": InStr returns the location of the backslash character, Mid reads all characters after this character, and Val transforms the resulting character string into a numerical value. After the actual data transfer the invoice number is updated in the monthly table, and then the file is saved.

 ' copy invoice data to month account table Sub  SaveAccountData  ()   Dim x As String   Dim accountNr%              ' current invoice number   Dim accountWs As Worksheet  ' reference to account sheet   On Error Resume Next   Set accountWs = ThisWorkbook.Sheets("invoice")   LoadMonthReport   x = accountWs.[invoicenr]   accountNr = Val(Mid(x, InStr(x, "-") + 1))   accountCell.Cells(accountNr, 1) = accountNr   accountCell.Cells(accountNr, 2) = accountWs.[invoicedate]   accountCell.Cells(accountNr, 3) = Now   accountCell.Cells(accountNr, 4) = accountWs.[membername]   accountCell.Cells(accountNr, 5) = accountWs.[car]   accountCell.Cells(accountNr, 6) = accountWs.[startTime]   accountCell.Cells(accountNr, 7) = accountWs.[endTime]   accountCell.Cells(accountNr, 8) = accountWs.[hoursI]   accountCell.Cells(accountNr, 9) = accountWs.[hoursII]   accountCell.Cells(accountNr, 10) = accountWs.[hoursIII]   accountCell.Cells(accountNr, 11) = accountWs.[startDate]   accountCell.Cells(accountNr, 12) = accountWs.[endDate]   accountCell.Cells(accountNr, 13) = accountWs.[weekendbonus]   accountCell.Cells(accountNr, 14) = accountWs.[nrOfMiles]   accountCell.Cells(accountNr, 15) = accountWs.[fuelcost]   accountCell.Cells(accountNr, 16) = accountWs.[invoicetotal]   If monthReport.[A2] < accountNr Then monthReport.[A2] = accountNr   monthReportWb.Save      ' save changed file End Sub 

Editing a Preexisting Invoice

The procedure ChangeOldEntry represents, in principle, the inverse function to SaveAccountData : This time data should be transferred out of the monthly report and into the invoice form, so that the invoice can be edited (for example, to correct an error).

The procedure begins with MenuViewMonthReport_OnClick displaying the monthly report and inviting the user to use an InputBox form to click on the line of the monthly report that contains data to be corrected. For this to happen InputBox is given the input type 0 (a formula). The resulting formula looks something like "=R5C7". With Mid the equal sign is eliminated. Then the character string is converted with ConvertFormula into the A1 format, then with Range is transformed into a Range object whose row number finally can be read with Row . Since the first four lines of the table are used for headers, the invoice number is determined after subtracting 4.

As soon as the invoice number is known, the actual data transfer can begin. For most of the input cells, data transfer presents no problems. In the case of both listboxes a loop must be used to determine the correct list entry.

Perhaps the many account variables are a bit confusing: accountWs refers to the invoice form and accountCell to the first data cell in the month table, while accountNr contains the invoice number.

 ' copy data of an already existing invoice from the account back ' into invoice form Sub  ChangeOldEntry  ()   Dim result As Variant, accountNr%, n%, i%   Dim accountWs As Object 'invoice form   ' accountWs cannot be defined as Worksheet; if you try,   ' Excel complains that the objects cmbMembers and   ' cmbCars are not known; obviously, the compiler   ' tries early binding, which is impossible in this case   Set accountWs = ThisWorkbook.Sheets("invoice")   On Error Resume Next   MenuViewMonthReport_OnClick   result = Application.InputBox("Please choose an invoice nr. " & _     "You can simply click on the correct line in the account list", _     Type:=0)   MenuViewMain_OnClick   If result = False Then Exit Sub   result = Mid(result, 2) ' = remove '=' in result   If Not IsNumeric(result) Then     ' extract row number out of formula "R123C123"     ' and save in result     result = Range(Application.ConvertFormula(result, xlR1C1, _       xlA1)).Row - 4   End If   If result < 1 Or result > Val(monthReport.[A2]) Then     MsgBox "Invalid invoice nr": Exit Sub   End If   accountNr = result   Application.ScreenUpdating = False   ' change date   accountWs.[invoicedate] = accountCell.Cells(accountNr, 2)   ' set member listbox   For i = 0 To accountWs.cmbMembers.ListCount - 1     If accountCell.Cells(accountNr, 4) = _        accountWs.cmbMembers.List(i) Then       accountWs.cmbMembers = i     End If   Next i   ' set car listbox   For i = 0 To accountWs.cmbCars.ListCount - 1     If accountCell.Cells(accountNr, 5) = accountWs.cmbCars.List(i) Then       accountWs.cmbCars = i     End If   Next i   ' various input fields   accountWs.[startTime] = accountCell.Cells(accountNr, 6)   accountWs.[endTime] = accountCell.Cells(accountNr, 7)   accountWs.[startDate] = accountCell.Cells(accountNr, 11)   accountWs.[endDate] = accountCell.Cells(accountNr, 12)   accountWs.[nrOfMiles] = accountCell.Cells(accountNr, 14)   accountWs.[fuelcost] = accountCell.Cells(accountNr, 15)   accountWs.[invoicenr] = "Invoice " & accountMonth & "-" & accountNr End Sub