1. (6 points) Create a user-defined function called Hexagon that takes one argument called side and returns the area of a regular hexagon given the length of the side. Show that the function works in a worksheet by inserting the formula somewhere.

2. (12 points) Start with a clean version of the file Training.xlsx. Do all of the following with VBA. (Place the code for all of the parts in a single sub.) (a) Boldface the font of the label in cell A1, change the font color to blue and change its font size to 36. (b) Boldface and italicize the headings in row 3, and change their horizontal alignment to the right. (c) Change the color of the font for the employee names in column A and B to pink (any shade you like). (d) Change the background (the Interior property) of the range with scores (D4:H23) to orange (any shade you like). (e) Enter the label Median in cell C24 and boldface it. (f) Enter a formula in cell D24 that finds the median of the scores above it. Copy this formula to the range E24:H24. 3. (7 points) Start with a clean version of the file Training.xlsx. Begin by using VBA to name the following ranges with the range names specified: cell A1 as Title, the headings in row 3 as Headings, the employee numbers in column A as EmpNumbers, and the range of scores as Scores. (These are to be Excel range names that will appear in the Excel sheet itself.) Refer to these range names as you do the following: (a) Fill the cells containing all of the scores yellow. (b) Boldface the title of the spreadsheet in cell A1. (c) Change the font of the column headings to 22 point. (d) Change the formatting of the employee numbers to have a comma in front of the last three digits.

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

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 B2:B16. (b) The cell at the intersection of the 5th row and 8th column of a range that has the range name Profit. (c) The cell at the intersection of 35th row and 11th 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 F6. (f) The set of entire columns from column D through column K. (g) A range of course 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 the 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 10 rows down from and 4 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.)