MACRO excel exam 3

general syntax for moving from the active cell to another cell using relative referencing

ActiveCell.Offset(#,#).Select

Write the appropriate VBA code to move from cell B3 to A2 using relative referencing

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

Write the appropriate VBA code to move from cell B1 to D4 using relative referencing

ActiveCell.Offset(3,2).Select

general syntax for selecting a cell using relative referencing

ActiveCell.Select

general syntax for changing the value of a cell using relative referencing

ActiveCell.Value=[additional code]

basic syntax for using Name Box to select a cell that has been named:

Application.Goto Reference:="Cell Name"

the first word used in the line of code to define a variable is

DIM (define in memory)

Explain what these lines of code are telling Excel to do: Dim Answer As String Answer=MsgBox("Do you want a million dollars?",vbYesNo,"Money")

Dim Answer As String- Defining a variable named Answer in Visual Basic Memory and we are setting this variable to store text values Connect this variable to a message box that will ask the user If they want a million dollars, and they will have to click on the yes or no button in response to the question. "Money" = it is going to give the message box the name Money. When the user clicks on a response, their answer will be stored in the variable storage place called Answer.

Write the appropriate line of code to define a variable named Color to store text values

Dim Color As String (text values= String)

Write the appropriate line of code to define a variable named Quantity to store whole numbers

Dim Quantity As Integer (quantity= quantity while whole numbers=integers)

Write the appropriate line of code to define a variable named Revenue to store currency values

Dim Revenue As Currency (revenue= revenue while currency values=currency)

1. Write the necessary code to create a macro that will allow the user to perform the calculation shown below using a function named NetCarbs. Assume TotalCarbs and Fiber can be values with or without decimal places. NetCarbs = TotalCarbs - Fiber User Defined Function= calculation/ equation Have to define variables first. (totalcarbs-fiber) With or without decimals= single

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

1. Write the appropriate code to create a dialog box asking the user to enter their age. Give the dialog box the title Age. Your code should place the user's response in Cell B3 on your worksheet. Write a loop so the dialog box will continue to pop up until the user enters a number that is greater than or equal to 18 and less than or equal to 100. Write the loop so the condition will be tested after running through the lines of code in the loop once first. remember: title goes at the end

Do Range("B3").Value=InputBox("Please Enter your age","Age") Loop Until Range("B3").Value>=18 and Range("B3"). Value<=100

1. Write the appropriate code to create a dialog box asking the user to enter the size for a food order. Give the dialog box the title Size. Your code should place the user's response in Cell C3 on your worksheet. Then create a loop around your code that will loop until Cell C3 contains "Small", "Medium", or "Large". Write the loop so the condition will be tested after running through the lines of code in the loop once first. Options= or values= and

Do Range("C3").Value =InputBox("Please enter the size ", "Size") Loop Until Range("C3").Value= "Small" or Range("C3").Value= "Medium" or Range("C3").Value="Large"

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 A3 on your worksheet. Then create a loop around your code that will loop while Cell A3 is blank. Write the loop so the condition will be tested first before running through the lines of code in the loop. side note: code always uses absolute referencing unless specified

Do While Range ("A3"). Value="" Range("A3").Value=InputBox("Please Enter your Employee ID","Number") Loop

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

the last line of code for a user-defined function is

End function

You would like to create a scroll bar in Cell C3, which will allow the user to select a car loan amount ranging from $3,000 to $50,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 $500 increments. The loan amount selected with the scroll bar should be displayed in Cell B3. Car Loan Payment Calculator: Loan Amount= $25,000 Loan Term (in years)= 5 Annual Interest Rate= 6.75% Monthly payment= $492.09 What properties need to be changed in order to create this scroll bar, and what should these properties be set to? Small change- arrows big change- scroll bars

Linked Cell: B3 Max: 50000 Min:3000 Small Change: 100 Large Change: 500

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 displayed in Cell B4. The user should not be allowed to enter anything less than 1 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 to?

Linked Cell: B4 Max: 5 Min: 1

1. Write the appropriate code to create a macro that will run when the file is opened. Set up a dialog box that will ask the user if they are working this summer. You should include a Yes button and a No button in this dialog box, so the user can respond appropriately. The dialog box should have the title Work. If the user responds by clicking on the No button, then the macro should display the message Enjoy your free time! If the user responds by clicking on the Yes button, then the macro should display a dialog box asking where they will be working. The user's response to the dialog box should be placed in Cell B3. Give this dialog box the title Summer Job. Private Macro= run when file is opened ()= private/pubic use () after workbook Vb= variable button (answer yes or no)

Private Sub Workbook_Open() Dim Response As String Response= MsgBox("Are you working this summer?","vbYesNo,"Work") If Response= vbNo Then MsgBox("Enjoy your free time!") Else Range("B3").Value=InputBox("Where will you be working?","Summer Job") End If End Sub

general syntax for changing the value of a cell using absolute referencing:

Range("Cell Address").Value=[additional code]

basic syntax for selecting a cell using absolute referencing

Range("CellAddress").Select

Write the appropriate VBA code to move from cell B3 to Cell D1 using absolute referencing

Range("D1").Select

Explain what this line of code in a macro is telling Excel to do: Range("K24").Select

Select Cell K24 using absolute referencing

the first word in the first line of a public macro is

Sub

1. Write the necessary code to create a macro named Grades from beginning to end that will run when the user clicks on a button or uses a shortcut key. The macro should perform the following steps when it is run: Select Cell H3 using absolute referencing. If the value in the cell that is one cell to the left of the active cell is greater than or equal to 70, then "Pass" should be the value displayed in the current active cell. Otherwise, the value displayed in the current active cell should be "Fail". Move down one cell below the active cell using relative referencing. Display a dialog box with the message "This is your final grade". (0,2)= didn't go up or down (2,0)= didn't go left or right

Sub Grades() Range("H3"). Select If ActiveCell.Offset(0,-1). Value>=70 Then ActiveCell.Value= "Pass" Else ActiveCell.Value= "Fail" ActiveCell.Offset(1,0)Select MsgBox("This is your final grade") End Sub

a user-defined function must be created in __________ in Visual Basic Editor

a module

what tab do we use in excel to work with macros

developer

in the visual basic command "ActiveWorkbook.Save", ActiveWorkbook is an

object

a macro that will run every time the file is opened is called a __________ macro

private macro

the ________ window in the visual basic editor is where you access the modules containing public macros you have created

project explorer window

What button do you click on to record a new macro

record macro