CSCI 112
Fall 2021 - Assignment 5
Submit deadline: 11:30am, 25 October 2021, Monday

Objective:

• Learn to develop a simple VBA program with conditions

References:

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

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, Student Services Fee and Students' Union (SU) Fee are calculated the same as for the domestic students.

But the international students' tuitions are calculated differently. Each term, for up to 15 credits (including 15 credits), there is a flat tuition of $8700. Any credit above the 15 credit limit adds $725 per credit to the tuition.

And the international students do not have to pay lab fees, but have a different fee called ancillary fee. Each term, for up to 15 credits (including 15 credits), the ancillary fee is a flat $389.40. Any credit above the 15 credit limit adds $32.45 per credit to the ancillary fee.

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

    16 * $156.80         (Tuition: $2508.8)
  +  3 * $22.52          (Lab Fee: $67.56)
  + 0.04 * 16 * $156.80  (Student Activity Fee: $100.352)
  + 16 * $6.79           (Student Services Fee: $108.64)
  + $83.60               (SU Fee: $83.60)
---------------------------------------
  = $2868.952 or $2868.95

If the student is an international student, then his/her total payment for this term would be:

    $8700 + 1 * $725     (Tuition: $9425)
  + $389.40 + 1 * $32.45 (Ancillary Fee: $421.85)
  + 0.04 * $9425         (Student Activity Fee: $377)
  + 16 * $6.79           (Student Services Fee: $108.64)
  + $83.60               (SU Fee: $83.60)
---------------------------------------
  = $10416.09

Your tasks

• Download the template file A5-input.xlsx.
• Open the template file you just downloaded and design and implement a VBA program to calculate the tuition and fees for a student enrolled in a credit-based undergraduate program in this hypothetical university.
  Your program should perform the following tasks:
  1. Ask the user to enter the following information (using InputBox)
     • the student's name;
     • the number of credits the student enrolled in this term; you can assume that the user will enter an integer number.
     • the status of the student; that is, whether the student is a domestic student or an international student; You can assume the default status is domestic;
       (Hint: you can ask the user to enter the words International or Domestic directly, or ask the user to enter one code (such as I or 1) for International or another code (such as D or 2) for Domestic.)
     • if the student is a domestic student, ask for the number of the student's enrolled courses that have associated labs; you can assume that the user will enter an integer number.
  2. Calculate the student's total payment for this term according to the information your program just collected from the student and the tuition and fee schedule stored in the template file;
  3. Use a message box to show the student's total payment; Optionally, also show the breakdown of 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.
• Save your work to a Macro-enabled Excel file named as your LastName_A5.xlsm.

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 156.8, 8700, 15, etc.) in your program. Instead you should either use named constants for the parameter values or load parameter values from worksheet to variables first.
• 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.
• Use consistent indentation in your program.
• You must give meaningful names to your variables, constants and subroutines.
• Use appropriate and easy to understand messages when you ask the user to enter data and when you show the calculation result to the users.
• 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 assignment, name your solution file your Lastname_A5.xlsm, then 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 solution file Lastname_A5.xlsm.

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.