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

Objective:

• Learn to develop a simple VBA program

References:

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

Problem Description

A hypothetical university, such as VIU, charges each domestic student enrolled in credit-based undergraduate programs tuition and fees.

The tuition is determined as $156.80 per credit. And students are also charged the following four types of fees:

• Lab Fees: For each course with labs, the university charges $22.52 per course;
• Student Activity Fee: This fee is calculated as four percent (4%) of a student's tuition;
• Student Services Fee: The university charges $6.79 per credit for this fee; and
• Students' Union (SU) Fee: This fee is $83.60 per term.

For example, if 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, the this student's 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

Your tasks

• Download the template file A4-input.xlsx.
• Open the template file you just downloaded and design and implement a VBA program to calculate domestic student's tuition and fees. 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;
     • the number of the student's enrolled courses that have labs.
  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 11 in the Excel file. You can either assume the active cell is placed on A11, or use absolute values to enter data to row 11.
• Save your work to a Macro-enabled Excel file named as your LastName_A4.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, 22.52, 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.
• 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 show the calculation result to the users.
• Finally don't forget to write the raw and processed data to the Excel file in your program.

How to submit:

To submit your assignment, name your solution file your Lastname_A4.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 "A4". Then you can click on the "Add a File" button to browse and upload your solution file Lastname_A4.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.