CSCI 112
Fall 2026 - Assignment 5
Submit deadline: 13:00, 22 October 2026, Thursday

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
• Assignment 4
• VBA Decision (I)
• VBA Decision (II)
• VBA Userform
• VBA Userform Programming

Problem Description

This assignment shares some of the application background knowledge of the previous assignment.

In the same hypothetical university of the previous assignment, such as VIU, there are not only domestic students, but international students, enrolled in credit-based undergraduate programs. And the university has different tuition and fee schedules for these two groups of students.

In the previous assignment, we know how to calculate the tuition and fees for domestic students.

For the international students, the Student Activity Fee, and Students' Union (SU) Fee are calculated the same as for the domestic students.

But the international student's tuition is calculated as $832.09 per credit.

The international students do not have to pay lab fees whether their courses have compulsory lab or not.

Probably because all international student must be full time students, their Student Services Fees are assessed at a rate of $22.94 per month.

For example, suppose a student is enrolled in 5 courses this term, 3 of the 5 courses are courses with compulsory labs, and these 5 courses have 16 credits in total. If the student is a domestic student, then this student's total payment for this term would be:

    16 * $173.12         (Tuition: $2769.92)
  +  3 * $24.87          (Lab Fee: $74.61)
  +  4 * $20.78          (Student activity fee: $83.12)
  +  0.03 * 16 *$173.12  (Student services fee: $83.10 or 83.0976)
  +  $150.32             (SU fee: $150.32)
---------------------------------------
  = $3161.07 or $3161.0676

If the student is an international student, then this student's total payment for this term would be:

    16 * $832.09     (Tuition: $13313.44)
  +  4 * $20.78      (Student Activity Fee: $83.12)
  +  4 * $22.94      (Student Services Fee: $91.76)
  + $150.32          (SU Fee: $150.32)
---------------------------------------
  = $13638.64

We still assume that a term always lasts 4 months.

Your tasks

• Download the template file A5-input.xlsx.
• Open the template file you just downloaded and design a userform that collect the following information from a user:
  • the student's name;
  • whether the student is a domestic or international student;
  • the number of credits the student enrolled in this term;
  • the number of the courses with compulsory labs the student enrolled in;
• Then design and implement a VBA program to handle the click event of the command button captioned "Calculate Payment" by performing the following tasks:
  1. user input data validation:
     • the number of credits must be a positive number;
     • the number of courses with compulsory lab must be between 0 and the number of credits entered inclusive;
     • the user's status must be either domestic or international;
     If any of these validation failed, show a proper error message to the user using msgbox and terminate this subroutine; otherwise, continue to the next step.
  2. Calculate the student's total payment for this term according to the information entered to the userform by the student and the tuition and fee schedule stored in the template file;
  3. display the student's total payment using either a lable on the userform or a message box; Optionally, also show the tuition and fees of each item included in the total payment.
  4. Enter the input information and the calculated results to the appropriate cells in row 12 in the Excel file. You can either assume the active cell is placed on A12, or use absolute values to enter data to row 12.
     To save a student's status, write either "Domestic" or "International" into the appropriate cell no matter how your program reads in the student's status. For the fee that's not applicable to the student, you can leave the corresponding cell empty or write "N/A" into the cell.
• Implement another VBA program that handles the click event of the command button captioned "Clear" to reset all the controls on the userform.
• 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:

• The interface design (the userform) must be user-friendly. The title of every control, including the userform itself, should be meaningful and appropriate.
• Your program should be logically correct and easy to understand.
• User input validation should be performed in the appropriate places.
• The invalid user input should be handled properly.
• There should be proper and consistent indent in your source code?
• Reasonable comment should be included in your source code?
• "Option Explicit" must be placed at the beginning of your whole module?
• Do NOT use literal numbers with ambiguous meanings (such as 173.12, 832.09) for your program's parameters. Instead you should either use named constants for these parameters or load parameter values from the parameter area on the worksheet to your program's valriables.
• Consistency is highly valued. That is, if you decide to use named constants for parameter values, then you should use named constants for ALL parameter values. Same with loading parameter values from the worksheet.
• You must give meaningful names to your variables and constants.
• Test your program thoroughly using different combinations of data.
• Finally don't forget to write the raw and processed data to the appropriate cells of the Excel worksheet before ending your program.

How to submit:

To submit your solution to this assignment, 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 "A5". Then you can click on the "Add a File" button to browse and upload your Macro-enabled Excel solution file.

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