CSCI 112
Fall 2021 - Assignment 9
Submit deadline: 11:30am, 29 November 2021, Monday

Objective:

• Learn to develop a VBA program that uses data from multiple worksheets
• Learn to develop a VBA program to solve problems

References:

• Lab 9
• Assignment 7
• VBA Repetition (III)
• VBA Repetition (IV)
• VBA Classes and Objects

Problem Description

This assignment is related to the Assignment 7.

The Department of Computer Science checks whether each student meets the pre-requisite for each class at the beginning of the term.

In order to perform the pre-requisite check, the department keeps a lot of data in a workbook. There are following guarantees about the format of the data in this workbook:

• The first worksheet is named "CSCI Pre-requisite" and stores a pre-quisite list for each course offered by the department (the same as specified in Assignment 7);
• The second worksheet is named "Course Enrolment History" and stores a list of the students' past course enrolment records. Each record includes the student's number (Student #), name, the enrolled course code, the term, and the final grade;
• Starting from the third worksheet to the last worksheet, each worksheet stores a class list of courses to be pre-requisite checked. Each worksheet is named with the corresponding course's course code, and includes the student number and name of each student who wants to take the corresponding course;
• There shouldn't be any data errors in this workbook;
• There are the following grade code that can possibly be assigned to a enrolment record: A+, A, A-, B+, B, B-, C+, C, C-, D, F, AUD, WDR, INC, TRF and TRM;
• Each course has a unique and fixed course code in the format of "XXXX NNN", where XXXX represent 4 upper-case letters, and NNN represents 3 digits;
• Each student has a unique and fixed student number in this system.

A student is considered to pass the pre-requisite check if the student has taken all the pre-requisite courses and got a grade other than "D", "F", "AUD", "WDR", "INC" or "TRM". If a course doesn't have any pre-requisites, then all the students in this course's class list pass the pre-requisite check.

A sample input file can be viewed in A9-input.xlsx.

One of the algorithms to perform the pre-requisite check is shown below:

For each class list, i.e., for each worksheet from 3 to Worksheets.Count
    Get the course's name
    Find out where to read the pre-requisite list of the course
    For each student in the class list
        For each pre-requisite course
            check to see whether the student passed this course
        End of For each pre-req course
        If the student passed ALL pre-requisite course
            output "YES"
        Else
            output "NO"
        End If
    End of For each student
End of For each class list

Your Tasks:

1. Download the template file to your home directory on discovery (your U drive).
2. Write a VBA program to check whether each student in each classlist meets the pre-requisite of the corresponding course.
   If a student in a class list pass the pre-requisite of the course, write "YES" to the column titled "Pre-requisite Passed" of the student's row; otherwise, write "NO" to the that cell.
3. Once you've done the programming, save the file to a new (Macro- enabled) Excel file named as your LastName_A9.xlsm.

This assignment doesn't require a user interface. Note that the collection of data in the sample input file is only an example to show the format of the data.

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

• Is your program logically correct and easy to understand?
• Did you properly indent your source code?
• Did you put in reasonable comment in your source code?
• Did you put "Option Explicit" at the beginning of your program?
• Did you give meaningful names to ALL your constants, variables?
• Did you design your own test cases to test your program thoroughly using different combinations of data?

Submitting:

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