CSCI 112
Fall 2020 - Assignment 4
Submit deadline: 11:30, 6 October 2025, Monday

Objective:

• Learn to develop a simple VBA program

References:

• Lab 4
• VBA Basics (I)
• VBA Basics (II)

Problem Description

BC Ferries wants to provide an app to help SD 68 (Nanaimo/Ladysmith Area) Elementary and Secondary school teachers to organize group ferry travels from Nanaimo's Duke Point to Vancouver's Tsawwassen in school events.

For group travelling in school events, BC Ferries charges $10.00 for each student that is 12 to 18 years old, $5.00 for each student that is 5 to 11 years old, and $17.10 for each accompanying adult. And there is an additional charge called Nanaimo Port Authority Fee. This additional fee charges $0.50 for each passenger that is 12 years or older, and $0.30 for each passenger that is 5 to 11 years old.

Assuming that the students will be travelling using a bus, BC Ferries charges $5.40 per foot of the bus.

Just to give you an idea, a standard school bus is approximately 35 feet long. Suppose the event is a class event using a standard bus, there are 20 students aged 12 and 10 students aged 11 in the class, and there are 3 accompanying adults (including teachers, bus drivers and other supervising adults) travelling with the class, then the total charge the school needs to pay for the one way ferry trip would be:

    20 * $10 + 20 * $0.5 (for older students)
  + 10 * $5 + 10 * $0.3 (for younger students)
  +  3 * $17.1 + 3 * $0.5 (for the 3 accompanying adults)
  + 35 * $5.4 (for the 35 feet long bus)
---------------------------------------
  = $504.8

Your tasks

• Download the template file A3-input.xlsx.
• Open the template file you just downloaded and design and implement a VBA program that does the following tasks:
  1. Ask the user to enter the following information (using InputBox)
     • the number of students that are 12 years or older;
     • the number of students that are 5 to 11 years old;
     • the number of accompanying adults (including teachers, supervising adults and bus drivers if there are any) travelling with the students;
     • the length (in feet) of the bus used by this group. (Note that the user can enter 0 if they will not use a bus.)
  2. Calculate the total charge the school needs to pay to transport this group from Duke Point to Tsawwassen via BC Ferries according to the information collected from the user and the fare information stored in the template file;
  3. Use a message box 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.
• Save your work to a Macro-enabled Excel file with a meaningful name.

Before submitting your assignment, you need to check at least the following things:

• "Option Explicit" must be used at the beginning of your program.
• Do NOT use any magic (literal) numbers (such as 17.1, 0.5, etc) in your program. Instead you should use named constants, cell references or variables.
• You must give meaningful names to your variables and constants.
• Use appropriate and easy to understand messages when you ask the user to enter data and when you present the calculation result to the users.
• Finally don't forget to write data to the Excel file in your program.

How to submit:

To submit your macro enabled solution Excel file, First login to your VIU Learn account, find the CSCI 112 course page, click on the "Assessment" drop-down menu, click on the "Assignments" item, then click on the folder named "A3". Then you can click on the "Add a File" button to browse and upload your solution file.

If you have difficulties to make the VIU Learn submission work, please email me to let me know and attach your Excel solution file with your email.