Excel expert only..

HW7/~WRL0001.tmp

INFS 3250

In Class Project – Exercise 7

Name: ____________________________ Points: ______ / 20

Due: See Blackboard for Due Date! (2 pts deduction for each class period late)

Download and open the data file “Excel Exercise 7.xlsm”.

____ (5 points) Part 1. Import the data from the tab delimited (NOT comma delimited) text file “Exercise7-DonorForWEdate-09-05.txt” into the Worksheet titled “Donor09-05”. Write a macro to format the data to center the heading across the rows, make the column headings visible and wrapped, highlighted and shaded. Format the data columns as appropriate: dates as dates, dollars as currency, etc. Write a formula to calculate the “Matching Gift” column (simply equals the amount of the employee’s deduction. Calculate the “Total Gift” column as the sum of the Employee’s deduction + the Matching Gift. Create a total for each of the columns: – at the bottom of the last row and set the Print options for the page to print in landscape mode and print to fit to 1 page wide and 2 pages tall. (You may want to practice the steps once before attempting to record the macro). Title the macro FormatDonor and assign the Hotkey – “d” to the macro.

Test your macro by importing the tab delimited file “Exercise7-DonorForWEdate-09-12.txt” into the worksheet named “Donor09-12” and then running your FormatDonor macro (using the hotkey “Ctrl-d”. Repeat the test once again with “Exercise7-DonorForWEdate-09-19.txt” with Worksheet “Donor09-19”. Make sure your macro handles each version of the data. Notice where the totals appear. Extra credit if you can change the recorded macro, so that it can correctly identify the last row of data and write the sum function in the next row. I will test your macro with the data in the Worksheet named “Donor-tak”.

____ (5 points) Part 2. Write a macro to automatically grade a quiz (assign the Hotkey – “g” to the macro). Look at the data in the range of cells N1:Q11 on Worksheet Quiz-Smpl that displays the formulas and the way the user wants to see the results of the quiz grading. (Note the correct answers to the quiz in the column titled “Correct”. Your Macro should allow the user to select the quiz worksheet, press the hotkey “ Ctrl-g” – and grade the quiz automatically and format the grading table like the sample provided. I have given you two copies of the quiz to test your macro on (each with different student guesses). Leave the last worksheet titled “ Quiz-tak” for me to test your macro solution. (HINT: be sure that your macro does NOT record the selection of the sheet. Also, if you select back and forth between worksheets, that is recorded as well. You will either need to edit the recorded macro to remove those selections, or be able to record the macro without referencing the Quiz-Smpl worksheet).

____ (5 points) Part 3. Write an Excel macro that will create a user defined Excel function to calculate (gross) Profit. Open the module window in VBA (Visual Basic – from the Developer Tab). Type the following function code into the module.

It is a function called Profit that will accept 3 numeric arguments passed to it: qty, sales price and unit cost and return the calculated Profit.

Use the function on the GrProfit worksheet to calculate the Profit column by entering:

=profit(argument1, argument2, argument3)

Replace the arguments with the cell addresses containing qty, salesPrice and unitCost – in that order.

____ (5 points) Part 4. Write a macro to simulate a calculator, on the sheet thus named.

The Excel worksheet should look like the image on the left. Cells D5 and D6 will accept user input of any numbers. You will create 4 macros: one for each calculator function: Multiply, Divide, Add and Subtract the two numbers in D5 and D6 (which are 5 and 7 in the diagram above). Each macro will be assigned to a Button object – and the button will be placed over the white space reserved for it.

Step 1: Recording Excel macros

For this part you will create the following Excel macros using the Macro Recorder (on Developer Tab):

1. A macro named DoAddition that will

a. Enter a plus sign (“+”) into cell C6

b. Set value in cell D7 to =D5+D6

2. A macro named DoSubtraction should be recorded that will enter a minus sign (“-“) into cell C6 and set the value in cell D7 to =D5-D6.

3. A macro named DoMultiplication should be recorded that will enter a multiplication sign (“x“ or “*”) into cell C6 and set the value in cell D7 to =D5*D6.

4. A macro named DoDivision should be recorded and assigned to the “/“ button. It implements similar pseudo code, but with division sign (“/”) and D7 set to D5/D6.

Step 2: Add Buttons

Once you have recorded all 4 macros, create a button for each math operation and relate the button to the Macro.

1. From Developer Tab – Click on Insert, Form Control – and select the Button icon (found in upper left icon on the Insert drop down menu pictured below.)

2. Draw a rectangle (button) in the space provided in the calculator form. After drawing the button, you will be prompted to select a Macro to tie the button to. Select the appropriate macro for the button’s purpose. Right click on the Button – to Edit the text and replace it with the Math function symbol (or word – ie: + or Add)

3. Test your work – by entering two values into the input cells (D5 and D6) and click on your buttons to see if they perform the math operation and provide the correct answer in D7.

Save your finalized document (Excel Exercise 7.xlsm and post it to the Blackboard assignment post. Write your name on the top of this form and turn it in as a vehicle to communicate your graded project.

Function Profit(qty, salesPrice, unitCost) As Currency

Profit = (qty * salesPrice) - (qty * unitCost)

End Function