CSCI 112
Fall 2021 - Assignment 7
Submit deadline: 11:30am, 8 November 2021, Monday

Objective:

• Learn to design and use a userform to collect user input and display result
• Learn to develop an event-driven VBA program
• Learn to develop a VBA program with repetition

References:

• Lab 7
• VBA Userform
• VBA Userform Programming
• VBA Repetition (I)
• VBA Repetition (II)

Problem Description

The Department of Computer Science offers a list of computer science courses with the code of "CSCI xxx". Some computer science courses require other computer science and math courses as their pre-requisite.

For example, CSCI 330 requires CSCI 162, CSCI 260, CSCI 261 and CSCI 265 as its pre-requisite, while CSCI 112 requires no pre-requisite.

A sample pre-requisite list can be viewed in A7-input.xlsx.

Your Tasks:

1. Download the template file to your home directory on discovery (your U drive).
2. Design a userform with at least a textbox, a command button and a label. The textbox allows the user to enter a course code, the command button's caption should be "Check Pre-req", and the label is used to display the checking result.
3. Write a VBA program to handle the click event of the command button captioned "Check Pre-req" by performing the following tasks:
   1. If the course code entered by the user is valid, find all of the course's pre-requisites, and display them using the label. If the pre-requisite list is empty (that is, the valid course doesn't have any pre-requisite, display "This course doesn't require any pre-requisite.".
   2. If the course code doesn't match any valid course code in the Excel file, use the label to display an error message, such as "The course code entered is invalid".
4. Once you've done the programming, save the file to a new (Macro- enabled) Excel file named as your LastName_A7.xlsm.

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

• Is your interface user-friendly?
• Is your program logically correct and easy to understand?
• Did you handle invalid user input properly?
• 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 and control objects?
• Did you test your program thoroughly using different combinations of data, such as invalid course name, course with or without pre-requisites?

Submitting:

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