Applications Programming --- Optional Lab for Midterm

Objective:

• Learn to design and use a userform to collect user input
• Learn to develop an event-driven VBA program
• Learn to develop a simple VBA program with conditions

References:

• Lab 5 referneces
• VBA Decision (I)
• VBA Decision (II)
• VBA Userform
• VBA Userform Programming
• Gas Station Example discussed in lectures

Problem Description

For foot passengers travelling from Departure Bay to Horseshoe Bay via BC Ferries, BC Ferries charges $20.00 for each passenger who is 12 years or older, $10.00 for each passenger between 5 to 11 years old, and free for each child that is under 5 years old.

Further more, if there are 10 or more paying passengers (5 years or older passengers) in your group, then each one in your group can enjoy discounted group fares that is $17.10 for each passenger who is 12 years or older, and $8.50 for each passenger between 5 to 11 years old. And children under 5 years old are still free.

No matter whether your group qualifies to have group discount fare, there is an additional charge, called Nanaimo Port Authority Fee, on individuals brought into or out of the limits of Nanaimo Habour. Each passenger who is 12 years or older is charged $0.50 and each passenger who is between 5 to 11 (inclusive) years old is charged $0.30 for this Nanaimo Port Authority Fee. Children under 5 years old are exempted from paying this fee.

Your Tasks:

1. Download this lab's template workbook file: midLab-input.xlsx.
2. Open the template file you just downloaded and design a VBA userform to collect the following information:
   • the number of adults (12 years or older) in the travelling group;
   • the number of children (between 5 to 11 years old) in the group;
   • the number of children (under 5 years old) in the group;
3. Design and implement a VBA program responding to the command button click event of the command button titled "Calculate Charge", to calculate the total charge this group needs to pay to travel as foot passenger from Departure Bay to Horseshoe Bay via BC Ferries according to the information collected from the user and the fare information stored in the template file;
   (Note: Don't forget the group fare discount if there are 10 or more paying passengers in the group.) Use a message box or a pre-defined label on the userform to show the calculation result;
4. Enter the input information and the total charge to the appropriate cells in row 11 in the Excel file. In the cell for "Qualifying for Group Fare?", enter "yes" if the group is qualified to have the group fare discount, and enter "no" otherwise.
5. Save your work to a Macro-enabled Excel file with a meaningful name.

Make your program sufficiently robust using combined proper userform design and/or user input validation. Make sure that there is at least 1 adult in this group and other numbers must be at least 0 or above.

There is NO weekly assignment following this lab.