Homework: Writing sub procedures

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 -

Table 1 shows the names of guests invited to a company's picnic at an amusement park. Table 1 also shows the department each guest belongs to and how many people are coming with them (broken down by adults and children). The cost of each ride is $4 per adult and $2 per child. There are 9 rides for adults and 7 rides for kids. Manually enter the data exactly as shown in Table 1 into worksheet "Prob1." You can format the cells manually (i.e., not via code) in Excel.

Table 1. Data for Problem 1.

Write a single sub procedure named Picnic that will format the data and calculate the values as described in Table 2.

Table 2. Data Formatting Instructions for Problem 1.

SOLUTION REQUIREMENTS:

a. The Offset property MUST be used to position all labels and calculations in their respective cells. You may use any cell in the worksheet "Prob1" as the reference cell to use the Offset property.

b. Assign the sub procedure to a shape button. The button's caption should read Calculate Statistics. The button should be visually appealing and its upper left corner should be aligned with the upper left corner of cell K2.

c. After the button is clicked, the display of the first calculation (i.e., Total Sum), its label and its corresponding cell's formats should be delayed 3 seconds. The display of every subsequent calculation (and corresponding label and formats) should then be delayed 2 seconds.

d. The text "DONE!" should be displayed in cell H7 one (1) second after the last calculation is displayed. Use capital letters and BOLD font style for this legend.

e. Range H2:I7 should be cleared automatically right after the button is pressed by your procedure every time it is run.

f. You must use the WITH construct to make your code more readable.

g. Your sub procedure should end by selecting cell A1.

Problem 2 -

Manually enter the data exactly as shown in Table 3 in columns B, C, and D, E, and F, respectively, of worksheet "Prob2." The upper left corner of Table 3 (i.e., the label "Rank") should be entered in cell B2.

Table 3. Data for Problem 2

SOLUTION REQUIREMENTS:

1. Write a sub procedure named Movies that will:

a. Use the Columns property of the Range object to give each column of the table its own background color. You are at liberty to pick the color for each column.

b. Copy the data in columns Title, Lifetime Gross (USA), and Year using the End property and paste it in range H2:J12.

c. Assign the sub procedure to a shape button. The button's caption should read GROSS EARNINGS BY MOVIE. The button should be visually appealing and its upper left corner should be aligned with the upper left corner of cell B15.

d. Ensure that no flashing border remains after the copy-pasting operation is complete.

e. Ensure that no flickering occurs when you run your sub procedure.

f. Your sub procedure should end by selecting cell A1.

2. Write a sub procedure named Clear_Movies that will:

a. Clear range H2:J12.

b. Assign the sub procedure to a button. The button's caption should read CLEAR. The button should be visually appealing and its upper left corner should be aligned with the upper left corner of cell E15.