Applications Programming --- Lab 5

Objective:

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

References:

• Lab 4 referneces
• VBA Decision (I)
• VBA Decision (II)
• VBA Userform
• VBA Userform Programming
• Gas Station Example discussed in lectures

Problem Description

A new-age Taxi company let its riders have two choices of calculting ride fare:

1. the user can negotiate a fare with the driver before the ride (Uber style). Or
2. the fare is calculated based on the distance travelled and the idle minutes during the ride (traditional Taxi style). If a rider choose this style, the company charges $1.5 per kilometer and $0.55 for each minute the taxi is not moving during the ride.

Your Tasks:

1. Download the Lab 5's template workbook file: lab5-input.xlsx.
2. Open the template file you just downloaded and design a userform that collect the following information from the user:
   1. indication of which style of fare calculation the user wants to use;
   2. if the Uber style is selected, provide a control (maybe just a textbox) so that the user can enter the negotiated fare amount;
   3. if the traditional style is selected, provide controls (again maybe just textboxes) so that the user can enter the following information:
      • the distance travelled in kilo-meters in this ride, and
      • the number of minutes the taxi was not moving (idling) during this travel.
3. then, design and implement a VBA program to handle the click event of the command button captioned "Calculate Fare" by calculating and displaying the taxi fare for the user based on the user choice and trip information.
   Show the calculated fare using either a lable on the userform or a separate message box;
   Save the trip information, user choice and fare information to the appropriate cells in row 9 of the Excel file.
4. Save your work to a Macro-enabled Excel file (with the extension name .xlsm).

In order to make your program sufficiently robust, when your program collect the data of pre-negotiated fare, distance travelled and idling minutes, always perform user input validation by first read in these numbers as strings, then check whether these strings are numerical numbers (using IsNumeric function). If they are numbers, then convert them to floating point numbers (using CDbl function) and test whether these numbers are in the appropriate range, at least they must be positive numbers.

If the user enters a non-numerical data or negative numbers as pre-negotiated fare, distance and/or idling minutes, using message box to display an appropriate error message and then terminate your program right away.

When you develop your VBA program, you need to pay attention to the following things:

• Is your interface design (the userform) user-friendly?
• Did you use "Option Explicit" at the beginning of your program?
• Did you use named constants to represent any literal values?
• Did you give meaningful names to your variables and constants?
• Did you use appropriate messages when you show the calculation result to the users?
• Did you remember to save data to Excel file in your program?

There is a weekly Assignment 5 following this lab.