Macro HW

b. Move from Cell F2 to Cell B1 using relative referencing

ActiveCell.Offset(-1,-4).Select

b. Move from Cell B3 to Cell D1 using relative referencing

ActiveCell.Offset(-2,2).Select

1. Open up Excel. Go to Cell B7 and name this cell "Pumpkin". Then go to the Developer tab on the Ribbon, and click on the Record Macro button in the Code group. Name your macro "Referencing" and give it a shortcut key of "R". After clicking OK to close the Record Macro dialog box, do the following: • Select Cell A3 using absolute referencing (do not select the Use Relative Reference button). • Using the drop-down arrow to the right of the Name Box, select "Pumpkin". • Click on the Use Relative Reference button in the Code group on the Developer tab to use relative referencing, and then select Cell C1. • Click on the Stop Recording button on the Code group on the Developer tab. After you have performed these steps, view your code in Visual Basic Editor, and answer the following questions: c. What code was written when you selected Cell C1 using relative referencing?

ActiveCell.Offset(-6, 1).Range("A1").Select

1. Open up Excel. Go to Cell D8 and use the Name Box to name this cell "Christmas". Then go to the Developer tab on the Ribbon, and click on the Record Macro button in the Code group. Name your macro "Referencing" and give it a shortcut key of "R". After clicking OK to close the Record Macro Dialog Box, do the following: • Select Cell E3 using absolute referencing (do not select the Use Relative Reference button). • Using the drop-down arrow to the right of the Name Box, select "Christmas". • Click on the Use Relative Reference button in the Code group on the Developer tab to use relative referencing, and then select Cell A1. • Click on the Stop Recording button in the Code group on the Developer tab. After you have performed these steps, view your code in Visual Basic Editor, and answer the following questions: c. What code was written when you selected Cell A1 using relative referencing?

ActiveCell.Offset(-7, -3).Range("A1").Select

a. Move from Cell B3 to Cell A9 using relative referencing

ActiveCell.Offset(6,-1).Select

a. Move from Cell F2 to Cell H10 using relative referencing

ActiveCell.Offset(8,2).Select

17. The partial worksheet shown below has been developed for course registration at a local community college. When the course number is placed in a cell in the A column, the other columns will display the appropriate information (the information is obtained by using VLOOKUP functions in the other columns). Write the appropriate code to do the following: a. Use the Name Box to select the cell that has been named COURSE (Cell A2). b. Create one dialog box that will ask the user for the number of the course they wish to register for. Your code should place the user's response in the appropriate cell in the A column - A2 for the first course, A3 for the second course, etc. (HINT: use relative referencing.) The description, days, time, location, instructor and credits will automatically appear based on the course number entered in the A column. c. Loop 7 times so that each time a new course is entered, the course number is placed in column A on the next row down, allowing the user to enter up to 7 courses. d. Assume that the total number of credits appears in Cell G9. If that total is 9 or less, create a message box that tells the user that their tuition and fees for the semester are $500. If the total is more than 9, your message should tell the user that their tuition and fees for the semester are $650.

Application.GoTo Reference:="COURSE" Dim Counter As Integer Counter = 1 For Counter = 1 To 7 ActiveCell.Value = InputBox("Enter the course number you want to register for" , "COURSE NUMBER") ActiveCell.Offset(1,0).Select Next Counter If Range("G9").Value <= 9 Then MsgBox("Your tuition and fees are $500", ,"AMOUNT DUE") Else MsgBox("Your tuition and fees are $650", ,"AMOUNT DUE") End If

1. Open up Excel. Go to Cell D8 and use the Name Box to name this cell "Christmas". Then go to the Developer tab on the Ribbon, and click on the Record Macro button in the Code group. Name your macro "Referencing" and give it a shortcut key of "R". After clicking OK to close the Record Macro Dialog Box, do the following: • Select Cell E3 using absolute referencing (do not select the Use Relative Reference button). • Using the drop-down arrow to the right of the Name Box, select "Christmas". • Click on the Use Relative Reference button in the Code group on the Developer tab to use relative referencing, and then select Cell A1. • Click on the Stop Recording button in the Code group on the Developer tab. After you have performed these steps, view your code in Visual Basic Editor, and answer the following questions: b. What code was written when you selected the cell named "Christmas" using the Name Box drop-down menu?

Application.Goto Reference:="Christmas"

1. Open up Excel. Go to Cell B7 and name this cell "Pumpkin". Then go to the Developer tab on the Ribbon, and click on the Record Macro button in the Code group. Name your macro "Referencing" and give it a shortcut key of "R". After clicking OK to close the Record Macro dialog box, do the following: • Select Cell A3 using absolute referencing (do not select the Use Relative Reference button). • Using the drop-down arrow to the right of the Name Box, select "Pumpkin". • Click on the Use Relative Reference button in the Code group on the Developer tab to use relative referencing, and then select Cell C1. • Click on the Stop Recording button on the Code group on the Developer tab. After you have performed these steps, view your code in Visual Basic Editor, and answer the following questions: b. What code was written when you selected the cell named "Pumpkin" using the Name Box drop-down menu?

Application.Goto Reference:="Pumpkin"

5. Write the necessary code to define a variable in Visual Basic named Calories to store whole numbers.

Dim Calories As Integer

7. Write the necessary code to define a variable in Visual Basic named Code to store whole number values.

Dim Code As Integer

4. Write the necessary code to define a variable in Visual Basic named Discount to store dollar values.

Dim Discount As Currency

14. Write the necessary code to allow the user to perform the following calculation within Excel using a function named ERA: ERA = (EarnedRuns/InningsPitched)*9

Dim EarnedRuns As Single Dim InningsPitched As Single Function ERA (EarnedRuns, InningsPitched) ERA = (EarnedRuns / InningsPitched) * 9 End Function

5. Write the necessary code to define a variable in Visual Basic named Item to store text values.

Dim Item As String

6. Write the necessary code to define a variable in Visual Basic named Price to store values in dollar format.

Dim Price As Currency

3. Write the necessary code to define a variable in Visual Basic named State to store text values.

Dim State As String

8. Write the necessary code to allow the user to perform the following calculation within Excel using a function named NetCarbs: NetCarbs = TotalCarbs - Fiber

Dim TotalCarbs As Integer Dim Fiber As Integer Function NetCarbs (TotalCarbs, Fiber) NetCarbs = TotalCarbs-Fiber End Function

6. Write the appropriate code to create a dialog box asking the user to enter their employee ID number. Give the dialog box the title Number. Your code should place the user's response in Cell A4 on your worksheet. Then create a loop around your code that will loop while Cell A4 is blank. 7. Assume the employee ID number entered in the above dialog box is expected to be greater than or equal to 1 and less than or equal to 99999. Rewrite your loop so it requires the user to enter a number that is equal to or between 1 and 99999.

Do Range("A4").Value = InputBox("Please enter your employee ID number", "Number") Loop Until Range("A4").Value >= 1 and Range("A4").Value <= 99999

6. Write the appropriate code to create a dialog box asking the user to enter their employee ID number. Give the dialog box the title Number. Your code should place the user's response in Cell A4 on your worksheet. Then create a loop around your code that will loop while Cell A4 is blank.

Do Range("A4").Value = InputBox("Please enter your employee ID number", "Number") Loop While Range("A4").Value = ""

8. Write the code that would create a dialog box asking the user to enter their student number. Give the dialog box the title STUDENT NUMBER. Your code should place the user's response in Cell C10 on your worksheet. Then create a loop around your code that will loop until Cell C10 is not left blank.

Do Range("C10").Value = InputBox("Please enter your student number" , "STUDENT NUMBER") Loop Until Range("C10").Value <> ""

4. A loop that will allow you to run a set of VBA statements over and over again until a specified condition is met is known as a ___ _____ .

Do Loop

9. The last line of a user-defined function is _____ _____.

End Function

18. You would like to create a scroll bar in Cell C3, which will allow the user to select a car loan amount ranging from $1,000 to $100,000. When you click on one of the arrows at either end of the scroll bar, you want the dollar amount to change by $100 increments. When you click between the arrows, you want the dollar amount to change by $1,000 increments. The loan amount selected by using the scroll bar should be displayed in cell B3. A B C 1 Car Loan Payment Calculator 2 3 Loan Amount $25,000 4 Loan Term (in years) 5 6 Annual Interest Rate 6.75% 8 9 Monthly Payment ($492.09) 10 What properties need to be changed in order to create this scroll bar, and what should these properties be set at? Why would it not make sense to put a spin button in Cell C3 instead of a scroll bar?

Linked Cell: B3 Max: 100000 Min: 1000 Small Change: 100 Large Change: 1000 A spin button should only be used when there are few possible values. When there are a lot of possible values, it is better to use a scroll bar. If you created a spin button for the car loan amount, and someone was purchasing a very expensive car, it would take a long time for them to get to the correct car loan amount by just clicking on an arrow. The scroll bar is much more efficient when there are a lot of possible values, because you can drag the scroll bar to go through the values very quickly.

19. Now assume that you want to create a spin button in Cell C4 to allow the user to click on the arrows to change the loan term in Cell B4. The user should not be allowed to enter anything less than one or anything greater than 5 for the loan term. What properties need to be changed in order to create this spin button, and what should these properties be set at?

Linked Cell: B4 Max: 5 Min: 1

8. Write the code that would create a dialog box asking the user to enter their student number. Give the dialog box the title STUDENT NUMBER. Your code should place the user's response in Cell C10 on your worksheet. Then create a loop around your code that will loop until Cell C10 is not left blank. 9. Rewrite your loop so that it will loop while Cell C10 is blank. (You may just rewrite the line of code that contains the condition.) 10. Assume that student numbers are six digit numbers from 100000 to 999999. Rewrite your loop so that it requires the user to enter an appropriate number equal to or between 100000 and 999999. (You may just rewrite the line of code that contains the condition.) 11. Assume that 592864, 637100, and 685229 are the only valid responses. Rewrite your loop so that it requires the user to enter one of these three student numbers. (You may just rewrite the line of code that contains the condition.)

Loop Until Range("C10").Value = 592864 Or Range("C10").Value = 637100 Or Range("C10").Value = 685229 -OR- Loop While Range("C10").Value <> 592864 And Range("C10").Value <> 637100 And Range("C10").Value <> 685229

8. Write the code that would create a dialog box asking the user to enter their student number. Give the dialog box the title STUDENT NUMBER. Your code should place the user's response in Cell C10 on your worksheet. Then create a loop around your code that will loop until Cell C10 is not left blank. 9. Rewrite your loop so that it will loop while Cell C10 is blank. (You may just rewrite the line of code that contains the condition.) 10. Assume that student numbers are six digit numbers from 100000 to 999999. Rewrite your loop so that it requires the user to enter an appropriate number equal to or between 100000 and 999999. (You may just rewrite the line of code that contains the condition.)

Loop Until Range("C10").Value >= 100000 and Range("C10").Value <= 999999 -OR- Loop While Range("C10").Value < 100000 or Range("C10").Value > 999999

8. Write the code that would create a dialog box asking the user to enter their student number. Give the dialog box the title STUDENT NUMBER. Your code should place the user's response in Cell C10 on your worksheet. Then create a loop around your code that will loop until Cell C10 is not left blank. 9. Rewrite your loop so that it will loop while Cell C10 is blank. (You may just rewrite the line of code that contains the condition.) 10. Assume that student numbers are six digit numbers from 100000 to 999999. Rewrite your loop so that it requires the user to enter an appropriate number equal to or between 100000 and 999999. (You may just rewrite the line of code that contains the condition.) 11. Assume that 592864, 637100, and 685229 are the only valid responses. Rewrite your loop so that it requires the user to enter one of these three student numbers. (You may just rewrite the line of code that contains the condition.) 12. Cell D10 of your worksheet contains a VLOOKUP that finds the user's name by looking up the student number that is entered in Cell C10. If the student number is invalid, the message "INVALID STUDENT NUMBER" will appear in Cell D10. Rewrite your loop so that it will loop until this message does not appear in Cell D10. (You may just rewrite the line of code that contains the condition.)

Loop Until Range("D10").Value <> "INVALID STUDENT NUMBER" -OR- Loop While Range("D10").Value = "INVALID STUDENT NUMBER"

8. Write the code that would create a dialog box asking the user to enter their student number. Give the dialog box the title STUDENT NUMBER. Your code should place the user's response in Cell C10 on your worksheet. Then create a loop around your code that will loop until Cell C10 is not left blank. 9. Rewrite your loop so that it will loop while Cell C10 is blank. (You may just rewrite the line of code that contains the condition.)

Loop While Range("C10").Value = ""

1. In the Visual Basic command "ActiveWorkbook.Save", ActiveWorkbook is a(n) _____

Object

4. Define the following terms as they related to Visual Basic code: object, method, and property. Give an example of each.

Object is an element of the Excel Application. Examples include a cell, a worksheet, or an entire workbook. Property is an attribute of an object that defines one of its characteristics. Examples include name, size, color, value, formula, or location. Method is an action that can be performed on an object. Examples include clear contents, copy, insert, delete, select, save, or close.

2. A macro that will run every time the file is opened is called an event-driven macro or a ______ macro.

Private

15. Create a macro that will run when the file is opened. Set up a dialog box that will ask the user if they are graduating in May 2007. You should include a yes button and a no button in this dialog box, so the user can respond appropriately. If the user responds by clicking on the yes button, then you should display the message "Happy Graduation!" - just use an okay button here. If the user responds by clicking on the no button, then you should display a dialog box asking when they graduate. The user's answer should be placed in Cell B7.

Private Sub Workbook_Open() Dim Response As String Response = MsgBox("Are you graduating in May 2007?" , vbYesNo) If Response = vbYes Then MsgBox("Happy Graduation!") Else Range ("B7").Value = InputBox("When will you graduate?", "GRADUATION DATE") End If End Sub

9. Create a macro that will run when the file is opened. Set up a dialog box that will ask the user if they have done any Christmas shopping yet. The dialog box should be titled Christmas Shopping. You should include a Yes button and a No button in this dialog box, so the user can respond appropriately. If the user responds by clicking on the No button, then you should display the message You better get started! If the user responds by clicking on the Yes button, then you should display another dialog box asking where their favorite place to do Christmas shopping is. The user's answer should be placed in Cell B5. Give this dialog box the title Favorite Place. Then display the message Happy shopping!

Private Sub Workbook_Open() Dim Response As String Response = MsgBox("Have you done any Christmas shopping yet?" , vbYesNo,"Christmas Shopping") If Response = vbNo Then MsgBox("You better get started!") Else Range ("B5").Value = InputBox("Where is your favorite place to do Christmas shopping?","Favorite Place") MsgBox("Happy shopping!") End If End Sub

3. The _______ _______ window in the Visual Basic Editor is where you access the modules containing public macros you have created.

Project Explorer

1. Open up Excel. Go to Cell B7 and name this cell "Pumpkin". Then go to the Developer tab on the Ribbon, and click on the Record Macro button in the Code group. Name your macro "Referencing" and give it a shortcut key of "R". After clicking OK to close the Record Macro dialog box, do the following: • Select Cell A3 using absolute referencing (do not select the Use Relative Reference button). • Using the drop-down arrow to the right of the Name Box, select "Pumpkin". • Click on the Use Relative Reference button in the Code group on the Developer tab to use relative referencing, and then select Cell C1. • Click on the Stop Recording button on the Code group on the Developer tab. After you have performed these steps, view your code in Visual Basic Editor, and answer the following questions: a. What code was written when you selected Cell A3 using absolute referencing?

Range("A3").Select

c. Move from Cell F2 to Cell C7 using absolute referencing

Range("C7").Select

1. Open up Excel. Go to Cell D8 and use the Name Box to name this cell "Christmas". Then go to the Developer tab on the Ribbon, and click on the Record Macro button in the Code group. Name your macro "Referencing" and give it a shortcut key of "R". After clicking OK to close the Record Macro Dialog Box, do the following: • Select Cell E3 using absolute referencing (do not select the Use Relative Reference button). • Using the drop-down arrow to the right of the Name Box, select "Christmas". • Click on the Use Relative Reference button in the Code group on the Developer tab to use relative referencing, and then select Cell A1. • Click on the Stop Recording button in the Code group on the Developer tab. After you have performed these steps, view your code in Visual Basic Editor, and answer the following questions: a. What code was written when you selected Cell E3 using absolute referencing?

Range("E3").Select

13. Write the necessary VBA code to perform the following steps: a. Select Cell F9 using absolute referencing b. If the value in Cell F9 is less than 10, then the contents of Cell F9 should be cleared.

Range("F9").Select If ActiveCell.Value < 10 Then Selection.ClearContents End If

5. The first word in the first line of a public macro is ___.

Sub

10. The partial worksheet shown below has been developed for course registration at a local community college. When the course number is placed in a cell in the A column, the other columns will display the appropriate information (the information is obtained by using VLOOKUP functions in the other columns). Using the blank space provided on the next page, write the appropriate code to do the following: a. Create a macro named Registration. b. Use the Name Box to select the cell that has been named COURSE (assume that Cell A2 has been named COURSE). c. Create one dialog box that will ask the user to enter the number of the course they wish to register for. Your code should place the user's response in the appropriate cell in the A column - A2 for the first course, A3 for the second course, etc. (HINT: use relative referencing.). The title for this dialog box should be Course Number. Write the code for this dialog box, so it will be repeated if the course number entered is not greater than or equal to 10000 and less than or equal to 50000. Note that the description, days, time, location, instructor and credits will automatically appear based on the course number the user enters in the A column. d. Loop 7 times so that each time a new course is entered, the course number is placed in column A on the next row down, allowing the user to enter up to 7 courses if necessary. e. Assume that the total number of credit hours appears in Cell G10. If that total is 6 hours or less, create a message box telling the user their tuition is $1,750. If that total is between 6 and 12 hours, create a message box that tells the user their tuition is $3,500. If the total is 12 hours or more, your message should tell the user their tuition is $5,250. The message box should be given the title Tuition Fees.

Sub Registration() Application.GoTo Reference:="COURSE" Dim Counter As Integer Counter = 1 For Counter = 1 To 7 Do ActiveCell.Value = InputBox("Enter the course number you want to register for" , "Course Number") Loop Until ActiveCell.Value>=10000 and ActiveCell.Value<=50000 ActiveCell.Offset(1,0).Select Next Counter If Range("G10").Value<=6 Then MsgBox("Your tuition is $1,750",vbOKOnly,"Tuition Fees") ElseIf Range("G10").Value>6 and Range("G10").Value<12 Then MsgBox("Your tuition is $3,500",vbOKOnly,"Tuition Fees") Else MsgBox("Your tuition is $5,250",vbOKOnly,"Tuition Fees") End If End Sub

16. You want to write a macro named Students from beginning to end to perform the following steps: a. Select Cell B5 using absolute referencing. b. If the value in Cell B5 is greater than 90, then "Senior" should be the value displayed in Cell C5. c. If the value in Cell B5 is greater than 60, then "Junior" should be the value displayed in Cell C5. d. If the value in Cell B5 is greater than 30, then "Sophomore" should be the value displayed in Cell C5. e. Otherwise, the value displayed in Cell C5 should be "Freshman". f. Move down one cell below the active cell using relative referencing. g. Write this macro so steps b, c, d, e, and f will be performed over and over again until Excel comes to a blank cell (or while the active cell is not blank). Make sure the loop is written so the statements of code will be executed once before the condition is evaluated to determine if the code should be repeated.

Sub Students( ) Range("B5").Select Do If ActiveCell.Value > 90 Then ActiveCell.Offset(0,1).Value="Senior" ElseIf ActiveCell.Value > 60 Then ActiveCell.Offset(0,1).Value="Junior" ElseIf ActiveCell.Value > 30 Then ActiveCell.Offset(0,1).Value="Sophomore" Else ActiveCell.Offset(0,1).Value="Freshman" End If ActiveCell.Offset(1,0).Select Loop Until ActiveCell.Value = "" End Sub

3. When you click on the Insert button in the Controls group on the Developer tab, there are Form Controls and ActiveX Controls displayed to select from. When should you use the ActiveX Controls and when should you use the Form Controls?

The ActiveX controls are macros that have already been written, which we can change properties of in order to work in a specific way for us. The ActiveX controls are used to manage specific events triggered when the control is used. We use the ActiveX controls to create things like spin buttons, scroll bars, option buttons, check boxes, and combo boxes. The Form Controls allow you to assign a button to a macro you created/wrote yourself, and it is designed to only work within Excel to run a macro.

15. Create a macro that will run when the file is opened. Set up a dialog box that will ask the user if they are graduating in May 2007. You should include a yes button and a no button in this dialog box, so the user can respond appropriately. If the user responds by clicking on the yes button, then you should display the message "Happy Graduation!" - just use an okay button here. If the user responds by clicking on the no button, then you should display a dialog box asking when they graduate. The user's answer should be placed in Cell B7. What type of procedure is this? Where should you write this macro in Visual Basic Editor so it will run properly?

This is a private procedure (private sub/macro). The code for private procedures should always be written in This Workbook in Visual Basic Editor.

8. Write the necessary code to allow the user to perform the following calculation within Excel using a function named NetCarbs: NetCarbs = TotalCarbs - Fiber Dim TotalCarbs As Integer Dim Fiber As Integer Function NetCarbs (TotalCarbs, Fiber) NetCarbs = TotalCarbs-Fiber End Function What type of procedure is this? Where would this code need to be written in Visual Basic Editor?

This is a user-defined function. The code for user-defined functions should always be written in a module in Visual Basic Editor.

8. A macro that is triggered by a file being opened must be placed in __________ in order for it to run properly.

ThisWorkbook

10. The key word used to declare (or define) a variable is ___.

dim

7. Open, BeforeSave, and BeforePrint, are examples of a(n) ____ which can be used to cause certain macros to run.

event

6. In the Visual Basic command "Selection.Font.ColorIndex=3, ColorIndex is a(n) _______.

property