Macro Excel Exam
First number in an offset (#,#)
(+): down (-): up
Second number in an offset (#,#)
(+): right (-): left
Method
-An action that can be performed on an object (such as clearcontents, copy, delete, select, save, close, etc.) -object.method(parameter value)
Variable types
-Integer -Single (# with or without decimal place) -Currency -String (text)
Comments
-denoted in Visual Basic by an apostrophe -provide info without affecting the execution of a macro
Local Window
-displays variables and how they change as a macro is run -useful in debugging a macro
What are the 3 main objectives of macros?
1. Automate repetitive Excel tasks (improving efficiency) 2. Automate and control input (improves efficiency and accuracy) 3. Guide users through a spreadsheet (improves efficiency and accuracy)
Until
Continues to loop UNTIL the condition becomes true. (Will stop when condition becomes true)
While
Continues to loop WHILE the condition is true. (Will stop when condition becomes false)
Dim meaning
Define In Memory
For...Next Loop Syntax
Dim Counter As Integer For Counter = start To end [statements] Next Counter
User Defined Functions syntax
Dim Variable As DataType Function FuncName (argumentlist) FuncName=Expression End Function
Syntax for defining a variable
Dim VariableName As DataType
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 a user-defined function is _______ ______________.
End function
Message Box Syntax
MsgBox ("messagetext", [buttonoptions],[titletext]) Dim Response As String Response=MsgBox("messagetext",[buttonoptions],["titletext"])
Where is the Project Explorer Window located?
On the left side of the Visual Basic Editor.
Create a macro that will run when the file is opened
Private Sub Workbook_Open()
The ____________ _____________ window in the Visual Basic Editor is where you access the modules containing public macros you have created.
Project Explorer
Where can modules be renamed?
Properties window
What is the object in: Range("A3").Select
Range("A3")
You want 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")
Basic syntasx for selecting a cell using ABSOLUTE REFERENCING
Range("CellAddress").Select
General syntax for changing the value of a cell using ABSOLUTE REFERENCING
Range("CellAddress").Value = [additional code]
Syntax for Input Boxes
ResultCell = InputBox("prompt", ["title"])
What does the F1 function key do?
Retrieves help when pressed while an object, property, or method of Visual Basic code is highlighted in the Visual Basic Editor
What is the method in: Range("A3").Select
Select
If...Then Else End If
Similar to the value-if-true argument in an IF function
User Defined Functions
Special type of Visual Basic procedure, which returns a result Use to create custom functions to perform calculations in your macros or to use directly in your workbook
The first word in the first line of a public macros is ______.
Sub
Titletext
Supplies a caption (or title) at the top of your message box (must be in quotation marks)
Syntax
The set of rules specifying how you must enter Visual Basic commands.
A macro that is triggered by a file being opened must be placed in ________ _____________ in order for it to run properly.
This Workbook
T/F: All objects have properties
True
What are macro instructions written in?
Visual basic
Input Boxes
can be used to obtain information from the user. Information obtained is then placed in a particular cell on the worksheet.
The key word used to define a variable is ________.
dim (define in memory)
Visual Basic Editor (VBE)
embedded within Excel and can be accessed through the Developer tab. All macros created in Excel are stored here
Open, BeforeSave, and BeforePrint, are examples of a(n) event which can be used to cause private macros to run automatically when the event occurs.
event
In the Visual Basic command "Selection.Font.ColorIndex=3, ColorIndex is a(n) _____________.
property
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.
Visual Basic Commands
steps of the macro that define the macro's functionality
3 Methods for Creating a Macro
1. Use the Macro Recorder to record a new macro 2. Write a new macro in Visual Basic Editor 3. Copy, paste, and edit/customize a previously created macro
Property
A Property is an attribute of an object in Excel that defines/changes one of its characteristics (such as its name, size, color, formula, value, or location) object.property=expression
Module
A collection of macros located in Visual Basic Editor (VBE).
Variable
A named storage location in Visual Basic Editor that contains data you can retrieve and modify while the macro code is being executed.
Macro
A series of commands, actions, and functions that can be stored and initiated (run) whenever you want to perform a particular task.
Response
A variable location that has been defined in the macro code that will store the value of which button was selected by the user.
General syntax for moving from the active cell to another cell using RELATIVE REFERENCING
ActiveCell.Offset(#,#).Select
Selecting a cell using RELATIVE REFERENCING
ActiveCell.Select
General syntax for changing the value of a cell using RELATIVE REFERENCING
ActiveCell.VAlue = [additional code]
Do...Loop
Allows us to repeat a series of Visual Basic statements (lines of code) until a specified condition is met or while a specified condition is met
Object
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.
Basic syntax for using Name Box to select a cell that has been named
Application.Goto Reference:="CellName"
What is the property in: Range("A3").Font.ColorIndex=3
Font.ColorIndex=3
Message Boxes
allow us to display information in a simple dialog box and can make one or more buttons available to user for closing the message box and responding to the message
Microsoft Visual Basic Help
is separate from the normal Excel Help feature. You must be in Visual Basic Editor to access this help. Click on Help while in Visual Basic Editor. Click on the Visual Basic for Applications Language Reference link to access various help topics related to using Visual Basic.
In the Visual Basic command "ActiveWorkbook.Save", ActiveWorkbook is a(n) ______________.
object
Syntax for an object?
object.property object.method
A macro that will run every time the file is opened is called an event-driven macro or a ____________ macro.
private
Code Window
the window in Visual Basic Editor (VBE) that displays the Visual Basic code (commands) for a macro
What does vb stand for?
variable buttons
Give an example of a variable button.
vbYesNo