Excel exam 3
Macros are also referred to as __________________ in Visual Basic terminology.
"procedures"
What are the major components of Visual Basic Editor?
- Project explorer window - Code window -Local Window -Comments -VBA statements/code -Modules -ThisWorkbook
What are the 3 main Objectives of macros?
1. Automate repetitive Excel tasks (improves efficiency) 2. Automate and control input (improves efficiency and accuracy) 3. Guide users through a spreadsheet (improves efficiency and accuracy)
What are the 5 different types of ActiveX controls?
1. Spin Button 2. Scroll Bar 3. Option Button 4. Check box 5. Combo box
What are 3 methods we will use for Creating a Macro?
1. Use the Macro Recorder to record a new macro 2.Write a new macro from scratch in Visual Basic Editor 3.Copy, paste, and edit/customize a previously created macro
A user-defined function must be created in __________ in Visual Basic Editor.
A Module
what is the 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 Cell A2 using relative referencing.
ActiveCell.Offset(-1,-1).Select
Move from Cell E6 to Cell D1 using relative referencing:
ActiveCell.Offset(-5,-1).Select
Move from Cell H8 to Cell K2 using relative referencing
ActiveCell.Offset(-6,3).Select
Move from Cell A1 to Cell C4 using relative referencing
ActiveCell.Offset(3,2).Select
Write the appropriate VBA code to move from Cell B1 to Cell D4 using relative referencing.
ActiveCell.Offset(3,2).Select
Move from Cell F3 to Cell B7 using relative referencing:
ActiveCell.Offset(4,-4).Select
What is the GENERAL syntax for selecting a cell using relative referencing
ActiveCell.Select
what is the GENERAL syntax for changing the value of a cell using relative referencing
ActiveCell.Value = [additional code]
What is a combo box?
Allows the user to select an option from a drop-down menu
What is a Do Loop used for?
Allows us to repeat a series of Visual Basic statements (lines of code)until a specified condition is met or while a specified condition ismet
What is a check box?
Allows user to select as many or as few of the options as they desire
Use the Name Box to select a cell that has been named Accounting
Application.Goto Reference:="Accounting"
what is the Basic syntax for using Name Box to select a cell that has been named?
Application.Goto Reference:="CellName"
What properties change with a Check Box?
Caption Linked Cell
What properties change with Option buttons?
Caption Linked cell
What is this code telling you to do? Answer=MsgBox("Do you want a million dollars?",vbYesNo,"Money")
Define a varible called Answer and set it to store text values. This variable will be equal to the User's response to a messafe box that asks the user if they want a million dollars. It will hae a yes no button that can be clicked to indicate a response. This message box will be titles "Money".
what steps do you use to use activeX controls?
Developer Tab - Inssert button in controls group - select desired activeX control from the toolbox - Draw the AXC on excel worksheet - set the properties by clicking on the properties button in controls group.
The first word used in the line of code to define a variable is:
Dim (Define in Memory)
6. 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
What is another syntax for message boxes
Dim Response as String Response=MsgBox("messagetext",[buttonoptions],["titletext"])
Write the appropriate line of code to define a variable named Revenue to store currency Values
Dim Revenue as Currency
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
What is the General Syntax for Do...Loop WHILE?
Do [Statements] Loop while condition
What is the General syntax for Do...Loop UNTIL?
Do [Statements] Loop Until condition
What is the General Syntax for Do Until ...Loop
Do Until condition [statements] Loop
What is the General Syntax for Do While ...Loop
Do While condition [Statements] Loop
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 3 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=Input Box("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
What are comments?
Documentation located in the Visual Basic code to help clarify the purpose and meaning of the macro. Comments basically provide information about the macro without affecting the actual execution of the macro. They are indicated in Visual Basic Editor with an apostrophe at the beginning of the comment, and they are color-coded green.
The last line of code for a user-defined function is:
End Function
How do you get the developer tab on the ribbon?
File - Options - Customize ribbon in excel options dialog box - check the developer options on the right in the Main tabs box - OK
How do you save your workbook as a macro-enabled workbook?
File - Save As - Select Excel Macro-Enabled workbook option from the Save as type text box.
What is the IF... Then... Else SIMPLE format
IF condition THEN statements ELSE elsestatements End if
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 Min:1 Max: 5
What properties change with a combo box?
Linked cell Last Fill Range Bound Column Column Count
what properties change with a scroll bars?
Linked cell Max Min Large Change Small Change
What is a Public Macro?
Macro stored in Modules in Visual Basic editor and can be recorded or written from scratch and are initiated by the user
What properties change with Spin buttons?
Max Min Linked cell
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 What properties need to be changed in order to create this scroll bar, and what should these properties be set to?
Max: 50,000 Min: 3000 Small Change: 100 Large Change: 500 Linked Cell: B3
What is one syntax for message boxes
MsgBox("messagetext",[buttonoptions],["titletext"])
When using variables, What does Currency mean?
Number with dollar sign
When using variables, What does Single mean?
Number with or without decimal place
In the Visual Basic command "ActiveWorkbook.Save", ActiveWorkbook is a(n):
Object
What are Option buttons ?
Only allows user to select one option from several available options
A macro that will run every time the file is opened is called a ____________ macro.
Private Macro
what is private macro syntax?
Private Sub Object_Event() [statements] End Sub
What is the Syntax for the first and last line of code (PRIVATE MACRO)?
Private Sub Workbook_Open () End sub
what are the 3 types of macros?
Public macros, private macro, user defined functions
Move from Cell F3 to Cell B7 using absolute referencing
Range("B7").Select
Select Cell C4 using absolute referencing
Range("C4").Select
Write code to obtain the user's first name and place it in Cell C5,using an absolute reference
Range("C5").Value=InputBox("please enter your first name.","User's First Name")
What is the BASIC SYNTAX for selecting a cell using absolute referencing
Range("CellAddress").Select
what is the General syntax for changing the value of a cell using absolute referencing?
Range("CellAddress").Value = [additional code]
Write the appropriate VBA code to move from Cell B3 to Cell D1 using absolute Referencing.
Range("D1").Select
What is Input box syntax?
Range("L#").Value=InputBox("Prompt",["title"])
What button do you click on to record a new macro?
Record Macro
What is this code telling you to do? Range("K24").Select
Select Cell k24 using Absolute Referencing
What are user defined functions?
Special type of Visual Basic procedure, which returns a result
What is a User defined Function?
Stored in Modules in Visual basic and cannot be recorded. User-defined functions create a function that a user can then use to perform a calculation in excel.
The first word in the first line of a public macro is
Sub
What is the Syntax for the first and last line of code (PUBLIC MACRO)?
Sub MacroName() End Sub
When we use ActiveCell.Offset(#,#).Select, the offset indicates how many cells ______ or ________ and how many cells to the__________ or________ we should move from whatever the activecell is.
Up or down ; Left or right
What is a scroll bar?
Useful for choosing a numerical value from a large number of options
What is a spin button?
Useful for choosing a numerical value from a small number of options
When using variables, What does Integer mean?
Whole number with no decimals
What is a private Macro?
a macro Stored in ThisWorkbook in visual Basic editor and cannot be recorded. They can only be written from scratch, and they run automatically upon the occurrence of an event.
what is a Private Macro?
a macro that is triggered when some predetermined event occurs.
When using Macros in Excel, What do you save the workbook as?
a macro-enabled workbook
What is a variable?
a named storage location in Visual Basic Editor that contains datayou can retrieve and modify while the macro code is being executed. Variablesare created and defined within the macro code. Once you create a variable and give it a value, you can assign the value to an object.
Visual Basic for Applications (VBA) refers to....
a programming language you can use to create macros. It is a descendant of the BASIC programming language that is used in all Office products, as well as some other types of software
what is a Macro ?
a series of commands, actions, and functions that can be stored and-initiated (run) whenever you want to perform a particular task
What is an Input box
can be used to obtain information from the user. Information obtain is then placed in a particular cell on the worksheet.
What tab do we use in Excel to work with macros?
developer
What is a Local window?
displays variables and how they change as a macro is run.It is useful in debugging (correcting errors in) a macro. The Local Window can be accessed under the View menu in Visual Basic Editor.
When we use ActiveCell.Offset(#,#).Select, the second number indicates:
how many cells we should move to the right or left.
When we use ActiveCell.Offset(#,#).Select, the first number indicates:
how many cells we should move up or down from the activecell.
What is the module?
is a collection of macros located in the Visual Basic Editor. You can place several macros in one module or create new modules for each individual macro. Modules can be renamed in the Properties Window, which can be accessed under the View menu in Visual Basic Editor. A new module can be added by clicking on the Insert menu in Visual Basic Editor and then clicking onModule
What is a Method?
is an action that can be performed on an object (such as clear contents, copy, delete, select, save, close, etc.). The general syntax forVisual Basic code involving methods is object.method(parameter value). The parameter values are optional. For example, in the commandRange("A3").Select the method is Select, indicating that Excel should select Cell A3
What is an Object?
is an element of the Excel Application (such as a worksheet, a cell, a range of cells, or the entire workbook). Macro code is written to manipulate an object by performing an action on that object (through a method) or changing a characteristic of an object (through a property). In basic form, Visual Basic code is either written as object.property or object.method. For example, in the command Range("A3").Select, the object is Range("A3"), indicating that CellA3 is the object being manipulated through this macro code.
What is The Project Explore Window
located on the left side of the Visual BasicEditor. In this window, you can access macros you have recorded or written inmodules or ThisWorkbook. In addition, you can insert a new module or click onThisWorkbook in the Project Explorer Window to write a new macro. If it is notbeing displayed, this window can be accessed under the View menu in VisualBasic Editor
What are ActiveX controls?
pre-written macros in Excel
The _______________ window in the Visual Basic Editor is where you access the modules containing public macros you have created.
project Explore window
What is a property?
s an attribute of an object in Excel that defines/changes one of its characteristics (such as its name, size, color, formula, value, or location). All objects have properties. T
Macros allow us to....
simplify our use of computers and alleviate tedious computer tasks
When using variables, What does String mean?
text
What is Syntax?
the set of rules specifying how you must enter Visual Basic commands. You must follow the syntax precisely, or the macro will not run
What is the Code Window?
the window in Visual Basic Editor (VBE) that displays the Visual Basic code (commands) for a macro
When we use ActiveCell.Offset(#,#).Select, if the first number is positive that means......(5)
we should move down that number of cells (Down 5 cells)
When we use ActiveCell.Offset(#,#).Select, if the SECOND number is negative that means...... (-5)
we should move to the LEFT that number of cells (to the left 5 cells)
When we use ActiveCell.Offset(#,#).Select, if the SECOND number is positive that means......(5)
we should move to the RIGHT that number of cells (to the right 5 cells)
When we use ActiveCell.Offset(#,#).Select, if the first number is negative that means...... (-5)
we should move up that number of cells.(up 5 cells)
What is the F1 Function Key?
will retrieve help when pressed while an object, property, or method of Visual Basic code is highlighted in the Visual Basic Editor.
