Reference no: EM131844667

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.

No.	Guest	Department	Adults coming	Children coming
1	John Smith	IT	2	0
2	Dan Harris	Marketing	3	3
3	Jane Taylor	Design	1	2
4	Mary Davis	Finance	4	4
5	Jonathan Wilson	Logistics	2	3

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.

Label	Calculation	Cell Fill Color		Font Color & Type
(H2:H4)	(I2:I4)	(H2:H4)	(I2:I4)	(H2:H4)	(I2:I4)
Total Number of Guests	Total sum	None	Yellow	Black, Bold	Black, Underlined
Total Cost of Picnic	(Total sum of adults * Cost per adult * Number of rides for adults) + (Total sum of children * Cost per child * Number of rides for children)		Red		Yellow, Bold
Average Size of Party per Guest	Average		Blue		White, Italic

 

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

B	C	D	E	F
Rank	Title	Lifetime Gross (USA)	Year	Studio
1	Avatar	760,507,625	2009	Fox
2	Titanic	658,672,302	1997	Paramount
3	Marvel's The Avengers	623,357,910	2012	Buena Vista
4	The Dark Knight	534,858,444	2008	Warner Bros
5	Star Wars: Episode I - The Phantom Menace	474,544,677	1999	Fox
6	Star Wars	460,998,007	1977	Fox
7	The Dark Knight Rises	448,139,099	2012	Warner Bros
8	Shrek 2	441,226,247	2004	DreamWorks SKG
9	E.T.: The Extra-Terrestrial	435,110,554	1982	Universal
10	Pirates of The Caribbean: Dead Man's Chest	423,315,812	2006	Buena Vista

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.