CSCI 112
Fall 2021 - Assignment 6
Submit deadline: 11:30am, 1 November 2021, Monday

Objective:

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

References:

• Lab 6
• VBA Decision (I)
• VBA Decision (II)
• VBA Userform
• VBA Userform Programming

Problem Description

A hypothetical Hydro Company (such as BC Hydro) calculates a customer's energy (electricity) bill based on the customer's service type and the customer's energy consumption using the rates shown in the following table:

The basic charge is calculated according to the billing days, regardless whether or how much energy (electricity) is consumed during the billing period.

The Hydro Company uses a Two-Step energy charge system (except for the farms). The Step 1 to Step 2 threshold is calculated pro rate based on the billing days.

Both the basic charge and the energy consumption charge are subject to 5 percent of GST (Goods and Service Tax).

For example I, if you are a residential customer, there are 50 days in your current billing period, and your energy (electricity) consumption is 1450 kW.h in this billing period (50 days), then your bill will breakdown to the following:

Step 1 to Step 2 threshold: 22 kW.h * 50 = 1100 kW.h
Basic Charge: 50 * $0.2077 = $10.39
Step 1 Energy Charge: $0.0939 * 1100 = $103.29
Step 2 Energy Charge: $0.1408 * (1450-1100) = $49.28
Sub-Total: $162.96
GST: $162.96 * 5% = $8.15
Total Charge: $162.96 + $8.15 = $171.11

For example II, if you are a farm typed customer, there are 60 days in your current billing period, and your energy (electricity) consumption is 1500 kW.h in this billing period (60 days), then your bill will breakdown to the following:

Step 1 to Step 2 threshold: 0 kW.h * 60 = 0 kW.h
Basic Charge: 60 * $0.2215 = $13.29
Step 1 Energy Charge: $0.1125 * 0 = $0
Step 2 Energy Charge: $0.1125 * 1500 = $168.75
Sub-Total: $182.04
GST: $182.04 * 5% = $9.10
Total Charge: $182.04 + $9.10 = $191.14

For example III, if you are a business customer, there are 55 days in your current billing period, and your energy (electricity) consumption is 1500 kW.h in this billing period (55 days), then your bill will breakdown to the following:

Step 1 to Step 2 threshold: 35 kW.h * 55 = 1925 kW.h
Basic Charge: 55 * $0.3622 = $19.92
Step 1 Energy Charge: $0.0962 * 1500 = $144.30
Step 2 Energy Charge: $0.2515 * 0 = $0
Sub-Total: $164.22
GST: $164.22 * 5% = $8.21
Total Charge: $164.22 + $8.21 = $172.43

Your Tasks:

1. Download the template file to your home directory on discovery (your U drive).
2. Design a userform that collects the following information from a user:
   • The customer's address
   • The customer's service type
   • The number of billing days in this billing period
   • The energy (electricity) consumption in kW.h in this billing period
3. Write a VBA program to handle the click event of the command button captioned "Calculate" by performing the following tasks:
   1. user input data validation:
      • The address provided by the user can't be an empty string
      • The user must select exactly one customer service type
      • The number of days in the billing period must be a positive number
      • The energy consumption in the billing period must be a positive number
   2. calculate the Basic Charge, Step 1 Energy Charge, Step 2 Energy Charge, Sub-Total, GST and the Total Charge for the user.
   3. Display the final total charge using a label on the userform and save all user input and calculated result to the Excel worksheet's appropriate cells. You can assume that the Active Cell is placed on the first column of the row to receive the data.
4. Write another VBA program that handle the click event of the command button captioned "Clear" to reset all the controls on the userform.
5. Once you've done the programming, save the file to a new (Macro- enabled) Excel file named as your LastName_A6.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 perform user input validation in the appropriate places?
• 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 avoid using literal numbers with ambiguous meanings in 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?

Submitting:

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