9.3 Example: Balance Sheet for a Car-Sharing Club


9.3 Example: Balance Sheet for a Car-Sharing Club

Our second smart form (example file Share.xlt ) manages a balance sheet for a car-sharing club. Members of the club can borrow a car from the club's inventory. In addition to a security deposit and monthly dues, the charges for the use of an automobile might be placed in the following categories, at least for the purposes of this example. These costs will vary depending on the type of car:

hourly rate I (8 am “8 pm)

$ 1.20

hourly rate II (8 pm “2 am, 6 am “8 am)

$ 0.65

hourly rate III (2 am “6 am)

$ 0.00

full day rate (24 hours)

$ 18.00

weekend credit

$ 6.00

charge per mile

$ 0.18

The hourly rate applies to a full hour or a fraction thereof. The daily rate is valid for any 24- hour period, beginning at any time. If the car is borrowed over a weekend, then the charge is reduced by the weekend credit amount. The charge per mile includes gasoline. If the club member fills the tank, then those charges are refunded. These charges should cover all of the club's expenses, including purchase, maintenance, insurance, and taxes.

If the charges seem high, it should be borne in mind that they are conceived for persons who use a car only seldom and for whom it is therefore a more economical alternative to owning and maintaining a car. It should also be taken into account, for urban dwellers at least, that they will not have to pay for parking, which in New York, for example, can easily run to over $30 per day. They also will not have to worry about maintenance, insurance, and so on.

After that short commercial in support of the car-sharing model, let us return to the rigors of Excel. The example file Share.xlt contains the form template shown in Figure 9-7. In order to make the template available, it must first be copied into the template directory of the Office package. To open the file, FileNew must be used (not File Open ).

click to expand
Figure 9-7: Smart form for an invoice of a car-sharing club

For completing the information for a transaction, only relatively few items need to be input: name of the member, type of car (in a listbox), start and end times for the hourly rate or start and end dates for the daily rate, number of miles traveled, and finally, the amount spent on gasoline.

From these few data Excel determines how many hours should be charged to each of the three hourly rates and whether the start and end dates for the daily rate include a weekend. The resulting charges are summed.

The button labeled Print Invoice initiates printing of the form. The macro thus activated copies the current date and inserts it again as a number so that the invoice will be saved with a fixed date. Then the entire table is copied into a new worksheet. In this worksheet the yellow background (gray in Figure 9-7) is removed. Then the worksheet is printed and afterwards deleted. The resulting printout looks something like the one displayed in Figure 9-8.

click to expand
Figure 9-8: Sample printout of an invoice of a car-sharing club

In contrast to the invoice form presented in the previous section, where a macro was provided for saving the invoice, here the user must take responsibility for saving. The amount of automation in this example is thus less than that of the previous example.

Constructing the Table

The two input cells for the hourly rate are D19 and E19. Cells D21 through D23 contain the user-defined functions Rate_I_Hours through Rate_III_Hours (for a description of the code, see below). In G21 through G23 the numbers of hours at each rate are multiplied by the appropriate hourly rate (C45 through C50).

The two input cells for the daily rate are D26 and E26. In G26 the difference between the two dates is multiplied by the daily rate. If E26 is empty, then one day is assumed.

 G26:  =IF(D26=0,"", IF(E26=0, C48, (E26-D26)*C48)) 

In D28 the function TestIfWeekend is used to check whether a weekend is included in the loan period. If that is the case, then the text "OK" is printed in D28; otherwise , " ”-" is shown. The formulas in G28 evaluate D28 and display, if appropriate, the weekend credit value (in C49) as a negative number .

 G28:  =IF(D28="OK",-C49,"") 

The button Print Invoice was inserted into the table using the Control Toolbox, then labeled and linked with the macro btnPrint .

In the listbox provided for car type one of the three types of automobile in the club's transport fleet can be selected. These types together with the associated tariffs are stored in a second worksheet (the smaller and less expensive the car, the lower the charge). The result of the listbox (a value of 0, 1, or 2) is linked to cell G14. In cell D14, directly beneath the listbox, the type of car is displayed above the index function. This text is normally invisible and becomes visible only when printed (since the listbox itself is not printed).

 D14: =INDEX(sheet2!A2:A4, G14+1) 

The rate range of cells C45:C50 contains INDEX formulas, which access the charges in sheet 2 based on the type of auto selected (G14).

 C45:  =INDEX(sheet2!$B:$B,$G+1) 

Calculating the Charges

Cells D24 and E24, which contain the start and end dates of the car loan, are passed as arguments to the function TestIfWeekend . The code for this function begins with a function declaration together with its two parameters startDate and endDate . The function's result is given the value "no" at the outset. Three If tests check whether values are actually contained in the two parameters and whether the car was borrowed for at least two days (as a condition for receiving the weekend rebate). If one of these conditions is not fulfilled, then the function ends at once, and the result " ”" is displayed.

The keyword For introduces a loop in which the variable varDate runs through all the days from the start date to the day before the end date. To understand this loop it is necessary to know how Excel represents dates and times internally. The number 0 corresponds to the date 1/1/1900 00:00, while 34335.75, for example, represents 1/1/1994 18:00.

The loop tests whether the date in var date is a Saturday ( WeekDay(varDate)=7 ). If that is the case, then it must be that the loan period spans a weekend, since the loop runs only to the second-to-last day of the loan period. The result of the function is changed to "OK", and the function is exited.

If the loop runs without encountering a Saturday, then at the end of the loop TestIf Weekend retains its original value of " ”".

 ' file Share.xlt, Module1 Function  TestIfWeekend  (startDate As Variant, endDate As Variant) As String   Dim varDate As Date   TestIfWeekend = "---"                          'default result   If startDate = 0 Then Exit Function            'no start date   If endDate = 0 Then Exit Function              'no end date   If endDate - startDate < 2 Then Exit Function  'only one day   ' weekend between start and end date   For varDate = startDate To endDate - 1     If WeekDay(varDate) = 7 Then       TestIfWeekend = "OK": Exit Function     End If   Next varDate End Function 

The three functions Rate_I_Hours through Rate_III_Hours are similarly constructed , and so here a description of only one of these functions will suffice. The function begins with a test as to whether the two parameters are valid values. Note also the use of IsEmpty , with which an empty cell can be distinguished from a cell containing the value 0. Thus the value 0:00 as a time is valid.

Next, the two variables time1 and time2 are assigned the values of the parameters startTime and endTime . Note that the case that the car was held beyond midnight (say, from 8:00 pm to 1:30 am) is taken care of.

 Function  Rate_I_Hours  (startTime As Variant, endTime As Variant) As Integer   Dim varTime As Date, time1 As Date, time2 As Date   Dim nrOfHours As Integer   If IsEmpty(startTime) Or IsEmpty(endTime) Then Exit Function   time1 = startTime   If endTime < startTime Then 'over midnight     time2 = 1 + endTime   Else     time2 = endTime   End If   For varTime = time1 To time2 - 1 / 1441 Step 1 / 24     nrOfHours = Int(varTime * 24) Mod 24 'convert to full hours     If nrOfHours >= 8 And nrOfHours < 20 Then       Rate_I_Hours = Rate_I_Hours + 1     End If   Next varTime End Function 

The loop that now follows runs through the hours (one hour corresponds to 1/24 in Excel's time format) from the start time to exactly one minute before the end time (1 minute corresponds to 1/1440; here 1/1441 is used to exclude possible round-off error). The number of the hour is calculated in the variable varTime (for example, 6 for the time 6:30). Then the time is multiplied by 24 (since in Excel 24 hours corresponds to the value 1), rounded, and restricted via the modulo operator to the range 0 to 23. This is necessary for time periods extending over midnight, since in such a case a value greater than 24 could arise.

Finally, a test is made as to whether the current hour is in the range of rate 1. If that is the case, then the resultant value of Rate_I_Hours is increased by 1. What is relevant is always the beginning of an hour. The functions Rate_II_Hours and Rate_III_Hours differ from the function just considered only in the test conditions for the time periods.

Ideas for Improvements in the Rate Calculations

In the model we have chosen , the distinction between hourly and daily rates is irksome. If the car is taken for a day and a half, say, a combination of both rates should be used. The required input of the dates is subject to error. It would be more elegant if the date and time for the start and end of the loan were input. The associated functions would then on their own figure out the minimal cost combination of daily and hourly rates. Clearly, this would require some programming effort.

A further shortcoming in our model is the lack of consideration of holidays (perhaps the club wishes to offer a discount, or perhaps a surcharge, for holiday loans).

Printing the Form

The last macro we wish to describe is btnPrint_Click . This macro prints out the car-share form, replaces the current date with a fixed value, and removes the yellow background from the input cells. The whole table is then copied into a new worksheet, which is deleted at the end of the macro.

The main part of the code was generated by the macro recorder with ToolsMacroRecord New Macro. Then various changes were made, and the code was commented. The actual printing is accomplished with PrintOut , where Preview:= True indicates that only a print preview is executed.

The instructions at the start and end of the procedure ( ScreenUpdating= ) speed up the execution of the macro considerably. They prevent the continual update of the screen while the macro is running. On Error Resume Next has the effect that the macro runs to the end even if an error occurs (for example, if the user stops printing with Break).

In the penultimate line of the macro the current worksheet (which was created just a few lines above) is deleted. To bypass the alert that asks whether the sheet is truly to be deleted, the property DisplayAlerts is set to False .

 ' print invoice Sub  btnPrint_Click  ()   Application.ScreenUpdating = False   ' copy varDate, insert as value   Range("G9").Select   Selection.Copy   Selection.PasteSpecial Paste:=xlValues   Application.CutCopyMode = False   ' copy entire sheet   Sheets("sheet1").Select   Sheets("sheet1").Copy ActiveWorkbook.Sheets(1)   With Sheets(1)     ' remove yellow background     .Cells.Interior.ColorIndex = xlNone     ' print     .PrintOut Preview:=True   End With   ' delete new sheet   Application.DisplayAlerts = False   ActiveWindow.ActiveSheet.Delete   Application.DisplayAlerts = True   ' recalculate sheet 1   With Sheets(1)     .Rows(1).Insert Shift:=xlDown     .Rows(1).Delete Shift:=xlUp   End With   Application.ScreenUpdating = True End Sub 



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