The last two weeks my year 10 ICT students have designed a booking sheet for cinemas. The client (me) had these requirements:
Client’s requirements:
1. Simple to use and learn.
2. Should show all seats for a given performance (rows=23, seats in a row=20, you decide where the passageways are).
3. Use colour code to show seats taken (vacant or free, reserved, taken, price, backup seats)
4. Calculate:
a. # seats taken (total number and in %)
b. total income
New requirements:
1. The price of a seat depends on the location in the cinema and if the customer is a child or an adult.
2. Please protect the cells where one is not supposed to enter information so they will not be overwritten by mistake.
3. Add pie charts to show the various incomes.
4. The prices may change often so make it easy to change them.
5. The program should calculate the cost for each customer and the change they should receive.
1. Simple to use and learn.
2. Should show all seats for a given performance (rows=23, seats in a row=20, you decide where the passageways are).
3. Use colour code to show seats taken (vacant or free, reserved, taken, price, backup seats)
4. Calculate:
a. # seats taken (total number and in %)
b. total income
New requirements:
1. The price of a seat depends on the location in the cinema and if the customer is a child or an adult.
2. Please protect the cells where one is not supposed to enter information so they will not be overwritten by mistake.
3. Add pie charts to show the various incomes.
4. The prices may change often so make it easy to change them.
5. The program should calculate the cost for each customer and the change they should receive.
Today each student or student pair demonstrated (defended) their solution for their peers using the projector on the front computer. Many solutions were excellent, but I could see how macros could make them even more useful.
Macros and programming are not part of the syllabus, but even so on Monday I will tempt their intellectual taste buds with the following.
Sub costandchange
Sheet = thisComponent.Sheets(“Sheet1″)
income = Sheet.getCellRangeByName(“D4″)
oldincome = Sheet.getCellRangeByName(“D5″)
cost = income.value – oldincome.value
received = InputBox (chr(13)+ “The cost is ” + cost + “.” + chr(13) + chr(13)+ “Received:”,”Cinema”)
moneychange = val(received) – val(cost)
msgbox “Change = ” + received + ” – ” + cost + ” = ” + moneychange,,”Cinema”
oldincome.value = income.value
End Sub
The user enters a and c for adults and children and with countif() functions the total income is calculated in D4.
What happens when a new customer buys some tickets? Without a macro you have to type a and c in the cells/seats where they will sit and then type the number of as and cs to find the total cost and later the change they should receive.
With the macro above it is enough to mark the sheet with as and cs. The trick consists in having the total income in both D4 and D5. When as and cs are entered D4 changes, but D5 does not. D4-D5 gives therefore the total cost for the customer.
I found the code tips I needed here.
