4. (5 points) Start with a clean version of the file Training.xlsx. Write a VBA sub that sorts the scores in descending order on exam 1. Break ties by sorting in ascending order on exam 2.

OVER 5. (5 points) On a new sheet called Hello, insert numbers into the range A1:E10. The following code is used to format some data. This code works perfectly well, but it is quite repetitive. Rewrite it by using as many with constructions as make sense. Using appropriate indentation and then run your modified code to make sure it still works.

Sub FormatARange() ActiveWorkbook.Worksheets("Hello").Range("B1").Font.Bold = True ActiveWorkbook.Worksheets("Hello").Range("B1").Font.Size = 14 ActiveWorkbook.Worksheets("Hello").Range("B1").Interior.Color = vbYellow ActiveWorkbook.Worksheets("Hello").Range("B1").HorizontalAlignment = xlLeft ActiveWorkbook.Worksheets("Hello").Range("A3:A8").Font.Bold = True ActiveWorkbook.Worksheets("Hello").Range("A3:A8").Font.Italic = True ActiveWorkbook.Worksheets("Hello").Range("A3:A8").Interior.Color = vbGreen ActiveWorkbook.Worksheets("Hello").Range("A3:A8").InsertIndent 1 ActiveWorkbook.Worksheets("Hello").Range("B2:E2").Font.Bold = True ActiveWorkbook.Worksheets("Hello").Range("B2:E2").Font.Italic = True ActiveWorkbook.Worksheets("Hello").Range("B2:E2").Font.Color = vbYellow ActiveWorkbook.Worksheets("Hello").Range("B2:E2").Interior.Color = vbBlue ActiveWorkbook.Worksheets("Hello").Range("B2:E2").HorizontalAlignment = xlRight ActiveWorkbook.Worksheets("Hello").Range("B3:E8").Interior.Color = vbRed ActiveWorkbook.Worksheets("Hello").Range("B3:E8").NumberFormat = "$#,##0" End Sub

6. (15 points) Write a reference (in VBA code) to each of the following ranges. You can assume that each of these ranges is in the active worksheet of the active workbook, so that you dont have to qualify the references by worksheet or workbook. (Hint: You can reference the ranges by using the Select method. e.g., Range(A2:C4).Select.) Before you begin, create a range name in Excel called Profit that covers range C2:P45. You can test your program one line at a time by pressing the key in the Visual Basic Editor.

(a) The third cell of the range D2:D18. (b) The cell at the intersection of the 4th row and 7th column of a range that has the range name Profit. (c) The cell at the intersection of 33rd row and 10th column of a range that has been set to the Range object variable totalProfit. Have totalProfit cover A3:N50. (d) The cell at the intersection of the 10th row and 22nd column of A1:AA1000. (e) The entire row corresponding to cell G6. (f) The set of entire columns from column D through column L. (g) A range of U.S. state capital names, assuming the first is in Cell B2 and they extend down column B (although you dont know how many there are). [You will need to enter a range of course names to test this.] (h) A range of inventory figures in a rectangular block, assuming that location labels are above them in row 1 (starting in Cell E1) and week labels are to their left in column D (starting in Cell D2). You dont know how many locations or weeks there are and you want the range to include only the sales figures, not the labels. [You will need to enter data to test this.] (i) The cell that is 12 rows down from and 6 columns to the right of the active cell. (The active cell is the cell currently selected. If a rectangular range is selected, the active cell is the first cell that was selected when the range was selected. It can always be referred to in VBA as ActiveCell.)

no additional information given,