Programming Structures   Requirements for problems:   Make sure your program does not experience any flickering when processing the input data or producing results

Subject:MS ExcelPrice: Bought3

Share With

Programming Structures

 

Requirements for problems:

 

• • Make sure your program does not experience any flickering when processing the input data or producing results.
  • Make sure to follow good programming practices. In particular, pay close attention to the following:
    • Explicitly declaring all variables;
    • Properly indenting code so that it is more readable; and
    • Adding comments to your code.

 

Problems

 

Open a blank Excel workbook and save it as a MACRO-ENABLED file. Your workbook must contain only one worksheets named "Prob1". 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.

 

 

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:

 

1. 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.

 

 

Figure 1. Sample graphical user interface for Problem 1.

 

2. 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 (i.e. you do not need to validate the user input for the cell).

 

 

Figure 2. Input box to request upper cell corner of summary statistics table.

Esta parte es la posición de mis celdas de max, av, min

 

3. 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 pressing the “OK” button on the message box, your program must continue to display the input box depicted in Figure 3 until a valid value is provided. Note: you must use a repetition programming structure with a single input box function statement to fulfill this requirement.

 

 

Figure 3. Input box to request the number of deliverables.

 

 

Figure 4. Message to be displayed if user enters incorrect number of deliverables.

 

Finally, if 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.

 

 

Figure 5. Message to be displayed to confirm that the user wants to quit program.

 

4. The value for the total number of deliverables entered by the user in step (c) 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 D8:I8 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 an 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.

 

 

Figure 6. Input box to request the labels of deliverables.

 

 

Figure 7. Message to be displayed if user enters incorrect labels for deliverables.

 

5. 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.

6. The sub procedure Calculate_Stats must end by selecting cell A1.
7. Assign the sub procedure Calculate_Stats to a rectangular shape button. The caption of the rectangular shape button should read “Calculate Statistics”.

 

Figure 8. Example summary statistics table for two deliverables.

 

2. Write a sub procedure named Clear_Stats that will:
   1. Prompt the user via 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 (e.g., K9:M12).

 

 

Figure 9. Input box to request the range of cells to be cleared.

 

1. 2. The sub procedure Clear_Stats must end by selecting cell A1
   3. Assign the sub procedure Clear_Stats to a rectangular shape button. The caption of the rectangular shape button should read “Clear Statistics”.