Open a blank Excel workbook and save it as a MACRO-ENABLED file. Your workbook must contain only one worksheets named "Probl". 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 (10 pts): 1. Write a sub procedure named Calculate Stats that allows the user to calculate summary statistics for a maximum of six deliverables of a course. The summary statistics that will be calculated for these deliverables are average, median, highest, lowest, and sample standard deviation. Your sub procedure must meet the following requirements: a) Manually create a graphical user interface such as the one depicted in Figure 1 into worksheet"Prob1". You may use different colors when creating the data table and buttons, but the student data must be entered exactly as shown in Figure 1. This program allows a professor to calculate summary statistics about specific deliverables of a course. Press the button labeled "Calculate Statistics and provide the information requested. Name James Norton HW 815 85 Midterm Final Project 90 % Chea Figure 1. Sample graphical user interface for Problem 1. b) Prompt the user via an input box for the location of the upper left corner of the table that will display the summary statistics. You must configure the input box as depicted in Figure 2. Assume that the user always enters a valid cell number (Le you do not need to validate the user input for the cell). Location of summary statistics table Please enter the address of the cell where the upper left comer of the summary statistics table will be located Figure 2. Input box to request upper cell corner of summary statistics table c) Prompt the user via an input box for the total number of deliverables for which summary statistics will be calculated. You must configure the input box as depicted in Figure 3. You must verify that the user enters a value that can represent a valid integer number in the range [1,6]. If the user enters a value that does not represent a number, the user enters a valid value but the value is outside of the allowed range, or the user enters a non-integer value, your program must display a message box as depicted in Figure 4. Once the user acknowledges the error by p hethe "OK" button on the message box, your program must continue to display the input box depic Figure 3 until a valid value is provided Note: you must use a repetition programming sture with a single input box function statement to fulfill this requirement. Total of deliverables 12 Please enter the desired number of delivers for which m ay statistics will be calculated Figure 3. Input box to request the number of deliverables. Incorrect input Entramberbenland Figure 4. Message to be displayed if user enters incorrect number of deliverables. Finally, it when prompted for the desired number of deliverables, the user presses the "Cancel" button on the input box depicted in Figure 3. your program must display a message box as depicted in Figure 5. If the user presses "Yes", your program must terminate and not display any summary statistics on worksheet"Prob1". If the user presses "No", your program must continue to display the input box depicted in Figure 3 until a valid value is provided Please confirm Do you really want to quit? Figure 5. Message to be displayed to confirm that the user wants to quit program. d) The value for the total number of deliverables entered by the user in step (e) must be used by a repetition programming structure to control the calculation and the printing of the summary statistics for the deliverables. The user must first be prompted via an input box for the label that will identify the specific deliverables to be processed. You must configure the input box as depicted in Figure 6. You code must check that labels are entered as shown in range DS:18 in Figure 1. It is important to note that the user should not be required to enter labels for deliverables in any specific order. If the user enters as Styles incorrect value or presses the "Cancel" button on the input box depicted in Figure 6. your program must display a message box as depicted in Figure 7. Note: you must use a repetition programming structure with a single input box function statement to fulfill this requirement. Labels fordeler Please enter the label of column must be entered only as they appearing on the w heet Figure 6. Input box to request the labels of deliverables. Peserved column Figure 7. Message to be displayed if user enters incorrect labels for deliverables. e) As the user provides valid labels for the deliverables, your program must print a table with the summary statistics for each deliverable specified. Figure 8 depicts an example of how this table must look. You must comply with the following formatting requirements when printing the summary statistics table: Use a variable of type Range in combination with the offset property and the table location input by the user to insert labels and values into the summary statistics table. Change the color of every other column in the output table to improve readability of individual records. This functionality must be implemented with the use of the offset property within the repetition structure being used in step (d). The output table must have continuous black borders around its individual cells to improve readability Row labels must be BOLD and left aligned Column labels must be BOLD and centered Statistics values should be centered and displayed with exactly one digit of precision after the decimal point 1) The sub procedure Calculate Stats must end by selecting cell Al B) Assign the sub procedure Calculate Stats to a rectangular shape button. The caption of the rectangular shape button should read "Calculate Statistics C D E F G H I This program allows the user to calculate summary statistics about specific deliverables of a course. Press the button labeled "Calculate Statistics and provide the information requested. HW HWR HW 3 Midterm Final Project Final Project Dames Norton Michael Bradley 99 as 80 85 94 Average 357 96 19 Paul Keane Ruby Fernandez James Anderson Mary Williams Nicholas Neville Brandon Jordan Kathy Jackson 416 397 95 84. 5 86.0 Calculate Statistics Clear Statistics Figure 8. Example summary statistics table for two deliverables. 2. Write a sub procedure named Clear Stats that will: a) Prompt the user Vis an input box for the range of cells to be cleared. You must configure the input box as depicted in Figure 9. Assume that the user always enters a valid cell range (eg., K9 M12). Please enter the age of cells to be cleared Follow the sample shown in the textbox below Figure 9. Input box to request the range of cells to be cleared. b) The sub procedure Clear Stats must end by selecting cell Al c) Assign the sub procedure CleStats to a rectangular shape button. The caption of the rectangular shape button should read "Clear Statistics". Open a blank Excel workbook and save it as a MACRO-ENABLED file. Your workbook must contain only one worksheets named "Probl". 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 (10 pts): 1. Write a sub procedure named Calculate Stats that allows the user to calculate summary statistics for a maximum of six deliverables of a course. The summary statistics that will be calculated for these deliverables are average, median, highest, lowest, and sample standard deviation. Your sub procedure must meet the following requirements: a) Manually create a graphical user interface such as the one depicted in Figure 1 into worksheet"Prob1". You may use different colors when creating the data table and buttons, but the student data must be entered exactly as shown in Figure 1. This program allows a professor to calculate summary statistics about specific deliverables of a course. Press the button labeled "Calculate Statistics and provide the information requested. Name James Norton HW 815 85 Midterm Final Project 90 % Chea Figure 1. Sample graphical user interface for Problem 1. b) Prompt the user via an input box for the location of the upper left corner of the table that will display the summary statistics. You must configure the input box as depicted in Figure 2. Assume that the user always enters a valid cell number (Le you do not need to validate the user input for the cell). Location of summary statistics table Please enter the address of the cell where the upper left comer of the summary statistics table will be located Figure 2. Input box to request upper cell corner of summary statistics table c) Prompt the user via an input box for the total number of deliverables for which summary statistics will be calculated. You must configure the input box as depicted in Figure 3. You must verify that the user enters a value that can represent a valid integer number in the range [1,6]. If the user enters a value that does not represent a number, the user enters a valid value but the value is outside of the allowed range, or the user enters a non-integer value, your program must display a message box as depicted in Figure 4. Once the user acknowledges the error by p hethe "OK" button on the message box, your program must continue to display the input box depic Figure 3 until a valid value is provided Note: you must use a repetition programming sture with a single input box function statement to fulfill this requirement. Total of deliverables 12 Please enter the desired number of delivers for which m ay statistics will be calculated Figure 3. Input box to request the number of deliverables. Incorrect input Entramberbenland Figure 4. Message to be displayed if user enters incorrect number of deliverables. Finally, it when prompted for the desired number of deliverables, the user presses the "Cancel" button on the input box depicted in Figure 3. your program must display a message box as depicted in Figure 5. If the user presses "Yes", your program must terminate and not display any summary statistics on worksheet"Prob1". If the user presses "No", your program must continue to display the input box depicted in Figure 3 until a valid value is provided Please confirm Do you really want to quit? Figure 5. Message to be displayed to confirm that the user wants to quit program. d) The value for the total number of deliverables entered by the user in step (e) must be used by a repetition programming structure to control the calculation and the printing of the summary statistics for the deliverables. The user must first be prompted via an input box for the label that will identify the specific deliverables to be processed. You must configure the input box as depicted in Figure 6. You code must check that labels are entered as shown in range DS:18 in Figure 1. It is important to note that the user should not be required to enter labels for deliverables in any specific order. If the user enters as Styles incorrect value or presses the "Cancel" button on the input box depicted in Figure 6. your program must display a message box as depicted in Figure 7. Note: you must use a repetition programming structure with a single input box function statement to fulfill this requirement. Labels fordeler Please enter the label of column must be entered only as they appearing on the w heet Figure 6. Input box to request the labels of deliverables. Peserved column Figure 7. Message to be displayed if user enters incorrect labels for deliverables. e) As the user provides valid labels for the deliverables, your program must print a table with the summary statistics for each deliverable specified. Figure 8 depicts an example of how this table must look. You must comply with the following formatting requirements when printing the summary statistics table: Use a variable of type Range in combination with the offset property and the table location input by the user to insert labels and values into the summary statistics table. Change the color of every other column in the output table to improve readability of individual records. This functionality must be implemented with the use of the offset property within the repetition structure being used in step (d). The output table must have continuous black borders around its individual cells to improve readability Row labels must be BOLD and left aligned Column labels must be BOLD and centered Statistics values should be centered and displayed with exactly one digit of precision after the decimal point 1) The sub procedure Calculate Stats must end by selecting cell Al B) Assign the sub procedure Calculate Stats to a rectangular shape button. The caption of the rectangular shape button should read "Calculate Statistics C D E F G H I This program allows the user to calculate summary statistics about specific deliverables of a course. Press the button labeled "Calculate Statistics and provide the information requested. HW HWR HW 3 Midterm Final Project Final Project Dames Norton Michael Bradley 99 as 80 85 94 Average 357 96 19 Paul Keane Ruby Fernandez James Anderson Mary Williams Nicholas Neville Brandon Jordan Kathy Jackson 416 397 95 84. 5 86.0 Calculate Statistics Clear Statistics Figure 8. Example summary statistics table for two deliverables. 2. Write a sub procedure named Clear Stats that will: a) Prompt the user Vis an input box for the range of cells to be cleared. You must configure the input box as depicted in Figure 9. Assume that the user always enters a valid cell range (eg., K9 M12). Please enter the age of cells to be cleared Follow the sample shown in the textbox below Figure 9. Input box to request the range of cells to be cleared. b) The sub procedure Clear Stats must end by selecting cell Al c) Assign the sub procedure CleStats to a rectangular shape button. The caption of the rectangular shape button should read "Clear Statistics