Homework: Programming Structures
PROBLEMS -
Open a blank Excel workbook and save it as a MACRO-ENABLED file. Your workbook must contain only two worksheets named "Prob1" and "Prob2", respectively. Open the Visual Basic Editor (VBE) and insert a new module into the Project Explorer. You must write (NOT record) all your sub procedures inside this module.
Problem 1 -
a) Add a module (i.e., Module1) in the Project Explorer and write (do NOT record) all the code for this problem inside this module. Show your results in a worksheet labeled "Prob1".
b) Write a sub procedure that allows a user to create a list of individuals displaying their last name, first name and age, as well as the length of each of these data strings. Your sub procedure must do the following:
- Prompt the user for the total number of people to be entered in the spreadsheet via an input box. Assume that the user always enters a valid integer number in this step. The sub procedure must terminate execution if the "Cancel" button of this input box is pressed.
- Given the total number of entries provided, prompt the user to enter the age, first name, and last name for each person via another input box. This input box must display a default value prompting the user to enter each data string in the right order and separated by a comma. For example, the user must enter "45, Jenny, Jones" for the first person, "28, Mike, Pedersen" for the second, and so on. Each entry constitutes a record. Your program must use a repetition structure and, based on the number of records to be entered, present the same input box to meet this requirement. The sub procedure must NOT allow the user to terminate execution if the "Cancel" button of this input box is pressed.
- Your program must ignore any blank spaces entered before and/or after each string and only report the length of the actual string. For example, if a user enters "¢¢¢23¢¢,Lane¢¢¢¢¢¢¢¢¢¢,¢¢¢¢¢¢¢¢Louise", your program should report string lengths of 6, 4 and 2 for the last name, first name and age, respectively. The special character "¢" indicates a blank space in the example.
- Each record entered by the user must be separated into its individual components and displayed on the spreadsheet "Prob2" in separate cells, as shown in Figure 2. The first record entered by the user should be printed on row 3. Column B should display last names, column C should display the length of last names, column D should display first names, column E should display the length of first names, column F should display ages, and column G should display the length of ages. The columns headings can be entered by hand in the spreadsheet.
- You must use a variable of type Range in combination with the Offset property to enter each record in a row and each entry on a column.
- The interior color of every other row should be changed to improve the readability of individual records.
- Columns must automatically adjust their width to the length of the strings entered.
- The total number of records displayed on the spreadsheet should match the number of entries specified by the user.
c) Assign the sub procedure to a shape button.
Problem 2 -
Write a sub procedure that plays the game Ro-Sham-Bo (a.k.a., Rock, Paper, Scissors) for a player and a computer opponent. The player selects one of the options listed in an input box, then the computer randomly selects an option as well. The winner of the game is determined as follows:
Rock beats Scissors - Scissors beats Paper - Paper beats Rock
The same selection results in a tie.
Your sub procedure must do the following:
- The sub procedure should be named Ro_Sham_Bo.
- Prompt the player to enter a selection with an input box. Provide instructions to use the integers one, two, and three to represent the selection. Assume the player enters a valid choice (i.e., you do not need to validate the input), but allow the sub procedure to end if the cancel button is pressed.
- Generate another random value between 1 and 3 for the computer's choice with the same rules as for the player's choice.
- Display the player and the computer choices with the appropriate image in the spreadsheet. Also, display a label explaining the outcome (e.g., Rock Smashes Scissors!). Finally, if the player wins, display "You win!", if the computer wins, display "You lose!", and if the result is a tie display "No winner."
- Place one second delays between displaying each of the three items listed above (image, explanation, and result).
Assign the sub procedure Ro_Sham_Bo to a rectangular shape button. The caption of the rectangular shape button should read "Ro - Sham - Bo!".
Attachment:- Assignment Files.rar