Question

You must manually create a data table as shown in Table 2 into worksheet "Prob2". You should format the cells manually (i.e., not via code) in Excel. The upper left corner of Table 2 (i.e., the label "Container Type") should be entered in cell B2. Note that the worksheet “Prob2” does not display gridlines.

Table 2. Data for Problem 2.

Material Mass, g Container Type PET 400mL bottle PE 1 liter milk bottle Glass 750 mL bottle Al 440 mL can Steel 440 mL can PET High-density PE Soda glass 5000 series Al alloy Plain carbon steel 25 38 325 20 45 Embodied Energy, MJ/kg 84.0 81.0 15.5 208.0 32.0 Energy/Liter, MJ/L 5.3 3.8 6.7 9.5 3.3 4 6

SOLUTION REQUIREMENTS

Write a sub procedure named Sort_by_Embodied that will:

Use the End property of the Range object to copy and paste the data in Table 2 one row at a time. The upper left corner of the copied data (i.e., the label "Container Type") should be pasted in cell B10.

Sort the data in the copy of Table 2 numerically in ascending order based on the values shown in the column labeled “Embodied Energy” (use the Sort method of the Range object).

Use the Rows property of the Range object to give every other row in the sorted copy of Table 2 its own background color. You are at liberty to pick the color.

Ensure that no revolving border remains after executing any copy-pasting operations.

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

End the sub procedure by selecting cell A1.

Assign the sub procedure Sort_by_Embodied to a rectangular shape button. The caption of the rectangular shape button should read “Sort by Embodied Energy”. The rectangular shape button should be visually appealing and its upper left corner should be aligned with the upper left corner of cell H2.

Write a sub procedure named Clear_Table that will clear the range B10:F15.

Assign the sub procedure Clear_Table to a rectangular shape button. The caption of the rectangular shape button should read “Clear”. The rectangular shape button should be visually appealing and its upper left corner should be aligned with the upper left corner of cell K5.

Answer 1

1. Writing Good Programs The only way to learn programming is program, program and program. Learning programming is like learning cycling, swimming or any other sports. You can't learn by watching or reading books. Start to program immediately. (On the other hands, to improve your programming, you need to read many books and study how the masters program.) It is easy to write programs that work. It is much harder to write programs that not only work but also easy to maintain and understood by others I call these good programs. In the real world, writing program is not meaningful. You have to write good programs, so that others can understand and maintain your programs. Pay particular attention to:

uppercase. Use nouns for variables (e.g., radius) and class names (e.g., Circle). Use verbs for methods (eg.' getArea(), ¡s Empty()). Use Meaningful Names: Do not use names like a, b, c, d, x, x1, x2, and x1688. Avoid single-alphabet names like i. j, k. They are easy to type, but usually meaningless. Use single-alphabet names only when their meaning is dear, e.g. x. y. z for co-ordinates and i for array index. Use meaningful names like row and col (instead of x and y, i and j. x1 and x2), numStudents, maxGrade. size. and upperbound. Differentiate between singular and plural nouns (e.g, use books for an array of books, and book for each item) Use consistent indentation and coding style. Many IDEs (such as Eclipse/NetBeans) can re-format your source codes with a click. 2. Program Documentation: Comment! Comment! and more Comment! 2. Exercises on Flow Controls 2.1 Exercises on Conditional (Decision) Exercise CheckPassFail (if-else): Write a program called CheckPassFail which prints "PASS" if the int variable "mark" is more than or equal to 5e: or prints "FAIL otherwise. The program shall always print "DONE before exiting. Hints: *Trying if-else statement public class CheckPassFail // Save as "CheckPassFail.java public static void main(Stringl] args) // Program entry point int mark- 49; // Set the value of mark" here! System.out-println( The mark is"mark); System.out.println else t System.out.printin( System.out.println(

System.out.println System.out.println( Take note of the source-code indentation!! Whenever you open a block withndent all the statements inside the block by 3 or 4 spaces. When the block ends. un-indent the closingto align with the opening statement. Exercise CheckoddEven (if-else): Write a program called CheckoddEven which prints "odd Number if the int variable number" is odd, or "Even Number" otherwise. The program shall always print BYE! before exiting. Hints: n is an even number if (n % 2) is e: otherwise, it is an odd number. * Trying if-else statement and modulus (%) operator public class CheckOddEven Save as "CheckOddEven.java public static void main(Stringt] args) t// Program entry point int number 49; // Set the value of "number" here! System.out.println("The number is number); if( System.out.println( ) else System.out.println( System, out, println( Exercise PrintNumberInWord (nested-if, switch-case): Write a program called PrintNumber Inuord which prints "ONE, TWO"NIN other, respectively. Use (a) a nested-if" statement: (b) a "switch-case" statement. OTHER" if the int variable "number" is 1. 2..., 9, or

Hints: * Trying nested-if and switch-case statenents public class PrintNumberInword Save as "PrintlumberIndord.java" public static void main(String[] args) int number 5; 1 Set the value of "number" here! // Using nested-if if (number 1) System.out.println( ) else if (. else f // Using switch-case switch(number) f case 1: System.out.println case 2: System.out.println( . )i break; break; Don't forget "break" default: System.out.println Exercise PrintDay1nWord (nested-if, switch-case): write a program called PrintDayIn№rd which prints Sunday.. .Monday" .."Saturday. if the int variable day"is 0.1, . .6, respectively

2.2 Exercises on Loop (Iteration) Exercise SumAndAverage (Loop): Write a program called SumAndAverage to produce the sum of 1. 2. 3 to 100. Also compute and display the average. The output shall look like: The sum is 5050 The average is 50.5 Hints: Compute the sum and average of running numbers from a lowerbound to an upperbound using loop public class SumAndAverage t Save as "SumAndAverage.java" public static void main (String args) t // Store the accumulated sum, init toe // average in double int sum ; double average; int lowerbound 1; / The lowerbound to sum int upperbound = 100; // The upperbound to sum // Use a for-loop to sum from lowerbound to upperbound for (int number- lowerbound; number<-upperbound; ++number) sum += number; // Compute average in double. Beware that int/int produces int // Print sum and average // same as "sum sum + number" Try 1. Modify the program to use a 'while-do" loop instead of for" loop. int number lowerbound int sum 0; while (numberupperbound) t

2. Modify the program to use a "do-while" loop. int number lowerbound int sum 8; do sum + nurber; ) while (number upperbound); 3. What is the difference between "for" and "while-do" loops? What is the difference between "while-do and "do-while" loops? 4. Modify the program to sum from 111 to 8899, and compute the average. Introduce an int variable called count to count the numbers in the specified range. int count e; 11 count the number within the range, init to e for ++count; 5. Modify the program to sum only the odd numbers from 1 to 100, and compute the average. (HINTS n is an odd number ifn % 2 is not a) 6. Modify the program to sum those numbers from 1 to 100 that is divisible by 7, and compute the average 7. Modify the program to find the "sum of the squares" of all the numbers from 1 to 100, i.e. 1*1 + 2*2 3*3 100 100. Exercise ProductiToN (Loop): Write a program called Product1ToN to compute the product of integers 1 to 10 (ie. 12x3. x 10). Try computing the product from 1 to 11. 1 to 12. 1 to 13 and 1 to 14. Write down the product obtained and explain the results. Hints: Dedare an int variable called product (to accumulate the product) and iniialize to 1 Try: Compute the product from 1 to 11. 1 to 12, 1 to 13 and 1 to 14. Write down the product obtained and decide if the results are correct. Try: Repeat the above, but use long to store the product. Compare the products obtained. Hints: Product of 1 to 13 (-6227020800) is outside the range of int I-2147483648. 2147483647) but within the range of long. Take note that computer programs may not produce the correct answer evern though everything seems correct