CSCI 112
Fall 2025 - Assignment 4
Submit deadline: 11:30am, 13 October 2025, Monday

Objective:

• Learn to develop a VBA program with selection

References:

• Lab 5
• VBA Decision (I)
• VBA Decision (II)

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

• Download the template file A4-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 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;
  2. 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.)
  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. 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.
• Save your work to a Macro-enabled Excel file with a meaningful name.

Required user input validation:

In order to make your program sufficiently robust, when your program collect information about the travelling group, user validation must be performed. For each piece of input data, your program should first read it in as a string, then check whether this string represent a numerical value (using function isNumeric). If it is a number, then your program needs to make sure that there is at least 1 adult in this group, and other numbers must be at least 0 or above. Function CInt can be used to convert a string representing a numerical value to its equivalent integer number.

If the user enters a non-numerical data, 0 or negative for the number of adults, or negative numbers for the number of children, your program should display an appropriate error message to the user and then terminate right away without saving the data.

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 20, 17.1, etc) in your program. Either named constants or variables reading parameters from the Excel sheet should be used instead.
• You must give meaningful names to your variables and constants.
• User input validation must be done in the right place for each piece of input data.
• Use appropriate and easy to understand messages when you ask the user to enter data and when you show the calculation result or error messages to the user.
• Finally, don't forget to write data to the Excel file in your program if and only all the input data are valid.

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 "A4". 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 if necessary.