Excel Skills for Business: Intermediate II - Week 6 Final Assignment

Look at the lines of code directly beneath the Sub with the apostrophes (') in front, these lines are called... Reserved Words Comments Executable Code Statements

Comments

Click in a blank worksheet (if necessary add one). Click into cell B2 and run the macro. Which statement is true of the heading Expenses? (Note: If the macro generates an error, try recording it again and don't make any edits to it.) It has been inserted in A1 but if relative referencing had been on it would be in A2 It has been inserted in A1 because all macros start in A1 It has been inserted in A2 because it is a relative reference macro It has been inserted in A1 because the macro uses relative referencing

It has been inserted in A1 but if relative referencing had been on it would be in A2

Record a Macro following these steps (you may wish to run through them first). Ensure Relative Referencing is off. Click in B1. Open the record macro dialog, change the macro name to Expenses, give it a shortcut key Ctrl+Shift+E, click OK to start recording Click in A1 and type Expenses, click Enter twice. In A3 type Date, in B3 type Description, in C3 type Distance and in D3 type Cost, press Enter. In A4 type =TODAY()-WEEKDAY(TODAY(),3) to get the first date of the current week. Press Enter. Copy A4 and Paste Values into A4 to replace the calculation with the result. Use the Fill Handle to copy A4 down to A8. Press Enter In A9 type TOTAL, press Tab three times. In D9 type =SUM(D4:D8) and press Enter. Change the font size of A1 to 20 and widen column B. Click Stop Record and save your workbook. If you made a mistake while recording, you could always stop recording, delete the macro and start again...

True

Save your workbook but leave open. Create another blank workbook. Click in cell A1 and type 192 then press Enter (to go to cell A2). Run the macro Calc. What result is returned and why? 140.16 0 #REF! None of the above, you can't run the macro as it is not in this workbook

0

Find the line of code that does the SUM. It looks a bit unusual as it is a relative reference formula. Which of the following formulas is correct (as shown in your code): ActiveCell.FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)" ActiveCell.FormulaR1C1 = "=SUM(R[-5]C[1]:R[-1]C[1])" ActiveCell.FormulaR1C1 = "=SUM(R[5]C[1]:R[-1]C[1])" ActiveCell.FormulaR1C1 = "=SUM(R[5]C:R[1]C)"

ActiveCell.FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)"

In C4 enter the distance 52.5 and copy down to C8. We want to record a macro that will multiply the distance travelled in the cell to the left by 0.73. Click in D4 and record a relative reference macro called Calc that multiplies the value in the cell to the left by 0.73, press Enter, and then stop recording. Open the macro in the VBA editor. What is the second line of executable code? ActiveCell.Offset(1, 0).Range("C5").Select Range("C4").Select ActiveCell.Offset(1, 0).Range("A1").Select Range("C5").Select

ActiveCell.Offset(1, 0).Range("A1").Select

Return to the Excel spreadsheet. Which keyboard shortcut can we use for this (on the PC)? Ctrl+F11 Shift+F11 Ctrl+Shift+F11 Alt+F11

Alt+F11

Return to the spreadsheet and click in D5. Run the Calc macro. Which cell is now selected?

D6

We would like to be able to run the Calc macro from a button in the worksheet. Insert a shape in the worksheet and change the label to Calc. To run the macro we right click on the shape and select Run. True False

False

Which option would we click on the Developer tab to add a trusted location? COM Add-Ins Macro Security Macros Visual Basic

Macro Security

You have decided to write a macro to quickly and easily generate an expense sheet. Follow the steps carefully and answer the questions as you go. Create a new blank workbook. Save it with the name Expenses and change the file type to one that will support macros. Which of the following would be a valid option? Excel Workbook (.xlsx) Macro-Enabled Workbook (.xlsm) CSV (.csv) Excel Template (.xltx)

Macro-Enabled Workbook (.xlsm)

Open the Macro in the Visual Basic editor. The very first line should say Sub Expenses(). We are not going to change it but if someone changes it to Sub Travel Expenses() what will happen? When we run the macro the heading in the worksheet will say Travel Expenses The Macro will now be called Travel Expenses The macro will not be affected The Macro will generate an error

The Macro will generate an error

In the formula above, if we were to replace the 5 with a 4 what would happen when we ran the macro? The SUM function generated would add the values from D5:D9 The macro will error The SUM function generated would add the values from D5:D8 The macro will not be affected

The SUM function generated would add the values from D5:D8

Find the line of code that says .Size = 20 Change it to .Size = 24 Which of these statements is true: The heading Expenses will be set to size 24 next time the macro is run The column width will be set to 24pt next time the macro is run The column width will immediately be changed to 24pt wide The heading Expenses in the workbook will immediately be changed to size 24

The heading Expenses will be set to size 24 next time the macro is run

Look at the line that says: ' Keyboard Shortcut: Ctrl+Shift+E If we changed it to Ctrl+E what will happen? The Macro will generate an error The macro will not be affected We will now be able to run the Macro using Ctrl+E or Ctrl+Shift+E The keyboard shortcut for the macro will be changed to Ctrl+E

The macro will not be affected