CSCI 112
Fall 2025 - Assignment 7
Submit deadline: 11:30am, 24 November 2025, Monday

Objective:

• Learn to develop a VBA program with multiple procedures
• Get proficient with applications with repetitions
• Practice userform design and programming

References:

• Lab 9
• VBA Procedures

Problem Description

An institution organized an employee donation campaign for multiple causes for a period of time.

Whenever an employee donated an amount to a cause, a record was generated and appended to the list of recordes stored in an Excel worksheet named "Donation Records". Each row in the worksheet represents a donation, where each column records the donating employee's name (presumably unique for all employees), the donation cause (also unique for all causes), the donation date and the donation amount respectively. An employee can donate to the same cause multiple times.

You can find a list of the sample donation records in the input file. Note that the purpose of the sample input file is to demonstrate the format of the data. The actual data collection may be significantly different in its size and values.

After the donation campaign ended, the institution would like you to develop VBA programs to generate summary report, and to support enquiries about individual employees and causes. You are NOT allowed to change the original donation records stored in the worksheet "Donation Records" in any way.

Extra requirement:

In order to practice the VBA procedures, you must implement and use at least the following procedures:

' Given data as the name of an employee, find and return
' the row number of its match in designated column (col)
' between firstRow and lastRow,
' If no matching data is found, return lastRow+1
Function FindName(ByVal firstRow As Integer, _
                  ByVal lastRow As Integer, _
                  ByVal col As Integer, _
                  ByVal data As Date) As Integer

' Given data as the name of a cause, find and return
' the column number of its match in designated row (row)
' between firstCol and lastCol,
' If no matching data is found, return lastCol+1
Function FindName(ByVal firstCol As Integer, _
                  ByVal lastCol As Integer, _
                  ByVal row As Integer, _
                  ByVal data As String) As Integer

Your tasks

• Download the template file A7-input.xlsx.
• Design a userform that has at least the following controls:
  • A textbox for the user to enter a name;
  • Two option buttons for the user to indicate whether the name entered into the textbox is the name of an employee or a donation cause;
  • A command button captioned as "Individual Summary".
• Program the userform initialize event that generates a summary report about the whole donation campaign. In this program, a worksheet named "Summary" should be added to the same workbook, and the summary report should be generated and write to this newly added worksheet in the following format based on the sample donation records data in the input file:

  	Loaves & Fishes	Food Bank	Earthquake Relief	Giving Tuesday
  Irene	130	220
  Humphray		230	20	160
  Faith	290	110	340
  Kevin	370	300
  Ben		510	20
  Grant	60	210		180
  Jeremy	190	350		350
  Amy	160		540
  Ethan	290	40
  Cathy		110		390
  David		280		160

  You can safely assume that there are no data entry errors (such as negative donation amounts, misspelled names, invalid date values, etc.) in the Donation Records collection.
• Program the command button click event:
  • When the user clicks the "Individual Summary" button, your program should calculate and display the following information if the input is the name of an employee:
    • Total amount donated by this employee;
    • Number of causes donated by this employee;
    • Average donations made by this employee to these causes.
  • When the user clicks the "Individual Summary" button, your program should calculate and display the following information if the input is the name of a donation cause:
    • Total amount donated to this cause;
    • Number of employees donated to this cause;
    • Average donations made by these employees who donated to this cause.
  You can use the data in the summary report directly, making sure that the format of the data generated by initialize and used here is consistent.
  Your program must properly handle the exceptions of invalid employee name (invalid donation cause) and/or name of an employee who made no donation (a donation cause nobody donated to). The exceptions of invalid name input and no donation can't be detected and handled separately, so compose your error messages appropriately to acknowledge these two cases together.
• Program the userform terminate event that delete the newly added worksheet "Summary", so that when this userform is launched again, the initialize event can be performed without worksheet name conflict.
• Save your work to a Macro-enabled Excel file with a meaningful name.

How to submit:

To submit your macro enabled solution Excel file, first 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.

If you have trouble to make the VIU Learn submission work, please email me to let me know and attach your Excel solution file with your email if necessary.