Macro

3 ways to create a macro

- Macro Recorder Button -Write new Macro in VBE - Copy, past, and edit and old macro

How many different types of Do...Loop code are available to use?

4

A user-defined function must be created in

A module

Move Cell with relative reference

ActiveCell.Offset("Cell Address").select

Type the appropriate line of code to move from Cell A1 to Cell B3 using relative referencing.

ActiveCell.Offset(2,1).Select

Type the appropriate code to create an Input Box that will ask the user what their favorite country is. The Input Box should include the following title: Favorite Country. The user's response should be placed in the current active cell.

ActiveCell.Value=InputBox("What is your favorite country?","Favorite Country")

Type the appropriate code to select a cell that has been named Supplies using the Name Box.

Application.Goto Reference:="Supplies"

Which property do you change for a Combo Box to indicate the column from the List Fill Range you want displayed into the Linked Cell?

Bound column

What property do you change for a Check Box to indicate what information should be displayed to the right of the check box?

Caption

Which ActiveX Control would you use to create a drop-down menu for users to click on to select from a list of options?

Combo Box

Active X Control

Combo Box Spin Button Scroll Bar Option Button Check Box

Which step should be completed first when creating a user-defined function?

Define variables for the arguments in your function.

What tab on the Ribbon do you go to if you want to record a macro?

Developer

to assign a button to your macro

Developer tab - Insert - Under Form Controls - Select Button

How to use record Macro feature

Developer tab - code group - record Macro - enter name and shortcut

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

Dim

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

Dim Color As String

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

Dim Quantity As Integer

Type the appropriate code to create a Message Box that asks the user if they like turkey. The Message Box should have a Yes button and a No button and should have the title Turkey. Make sure you include a line of code to store the user's response.

Dim Response As String Response=MsgBox("Do you like turkey",vbYesNo,"Turkey")

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

Dim Revenue As Currency

correct line of code to define a variable named Stock to store numerical values that are whole numbers

Dim Stock as Integer

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

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.

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

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.

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 whileCell A3 is blank. Write the loop so the condition will be tested first before running through the lines of code in the loop.

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

If you want to place a Scroll Bar in Cell F4 that will change the value displayed in Cell F3, what would you enter for the Linked Cell property?

F3

A Do Loop can be used to delete lines of code. T/F

False

A macro name is not required when creating a pubic macro T/F

False

A private macro/procedure is activated by the user pressings the short cut keys or clicking on a button T/F

False

ActiveX Controls are created from the Form Controls toolbox. T/F

False

Application.Reference:="Pumpkin" is the correct line of code for to indicate the cell named Pumpkin should be selected using the Name Box T/F

False

Last line of code for a user-defined function should b End Sub T/F

False

One way to create a new macro is to use New Macro Wizard T/F

False

The Linked Cell property for a Combo Box indicates where the ActiveX Control has been placed on the worksheet.

False

The formula for a user-defined function must be entered in quotation marks. T/F

False

User-defined functions are stored in Sheets in the Visual Basic Editor. T/F

False

You should add the Macros tab to the Ribbon in Excel, so you can record, create, edit, and run macros. T/F

False

You should use Decimal as the Type if you are defining a variable that may or may not have decimals. T/F

False

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 $500increments. The loan amount selected with the scroll bar should be displayed in Cell B3.

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

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

Private Macro

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. If the user responds by clicking on the No button, then you should display the message Enjoy your free time! If the user responds by clicking on the Yes button, then you should display a dialog box asking where they will be working. The user's answer should be placed in Cell B3. Give this dialog box the title Summer Job.

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

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

Project Explorer Window

Move Cell with absolute reference

Range("New cell").select

correct line of code to select Cell V43 using absolute referencing

Range("V43").Select

What button do you click on to record a new macro?

Record Macro

To clear a cell

Selection.ClearContents

Insert Row

Selection.EntireRow.Insert

Which of the following is not one of the properties you change for a Spin Button?

Small change

Select Cell H3 using absolute referencing.b. 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.c. Otherwise, the value displayed in the current active cell should be "Fail". d. Move down one cell below the active cell using relative referencing.e. Display a dialog box with the message "This is your final grade".

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

Do... Loops are used to repeat lines of code T/F

True

Objects, Variables and Methods are used to write macro code T/F

True

Option Buttons are useful when you only want the user to be able to select only one choice from a list of options. T/F

True

The Large Change indicates how much the value increases or decreases by when you click between the arrows on a Scroll Bar. T/F

True

You can define a variable to store the user's response to a message box. T/F

True

in the Visual Basic command "ActiveWorkbook.Save", ActiveWorkbook is a(n)

object

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

sub