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