Excel Exam 3 Macros

macros

a series of commands, actions, and functions that can be stored and initiated (run) whenever you want to perform a particular task. Provides a set of instructions to Excel that indicate which commands Excel should execute

Virtual 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 Editor VBE

is embedded within Excel and can be accessed
through the Developer tab. all macros created in Excel are stored in this. new macros can be created and previously created macros can be deleted or edited here.provides advanced editing capabilities including automatic syntax checking, tips and quick information, color coded programs for
readability, etc

Visual Basic Commands

are the steps of the macro that define the macro’s
functionality. are also referred to as “macro code”. We use objects, properties, methods, and variables to create these

The Code Window

is the window in Visual Basic Editor (VBE) that displays the
Visual Basic code (commands) for a macro

Comments

are documentation located in the Visual Basic code to help clarify the purpose and meaning 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

F1

function key will retrieve help when pressed while an object, property, or method of Visual Basic code is highlighted in the Visual Basic Edito

Microsoft Visual Basic Help

is separate from the normal Excel Help feature.
You must be in Visual Basic Editor to access this help

The Local Window

displays variables and how they change as a macro is run.
It is useful in debugging (correcting errors in) a macro

Method

is an action that can be performed on an object (such as
clearcontents, copy, delete, select, save, close, etc.).

Module

a collection of macros located in the Visual Basic Editor. You can place several macros in one or create new ones for each individual macro.

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)

Project Explorer Window

is located on the left side of the Visual Basic
Editor. In this window, you can access macros you have recorded or written in modules or ThisWorkbook. In addition, you can insert a new module or click on ThisWorkbook in the Project Explorer Window to write a new macro

Properties

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 these

syntax

is the set of rules specifying how you must enter Visual Basic
commands. You must follow the it precisely, or the macro will not run

Variable

a named storage location in Visual Basic Editor that contains data you can retrieve and modify while the macro code is being executed. They are created and defined within the macro code. Once you create one and give it a value, you can assign the value to an objec

ActiveCell.Select

General syntax for selecting a cell using relative referencing

ActiveCell.Value = [Additional Code]

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

ActiveCell.Offset(#,#).Select

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

up or down, right or left

When we use ActiveCell.Offset(#,#).Select, the offset indicates how many cells ___ and how many cells ____we should move from whatever the active cell is.

Range(“CellAddress”).Select

Basic syntax for selecting a cell using absolute referencing:

Range(“CellAddress”).Value = [Additional Code]

General syntax for changing the value of a cell using absolute referencing

Application.Goto Reference:=”Cell Name”

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

Range(“C4”).Select

Select Cell C4 using absolute referencing

Range(“B7”).Select

Move from Cell F3 to Cell B7 using absolute referencing

Application.Goto Reference:=”Accounting”

Use the Name Box to select a cell that has been named Accounting

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

Message Boxes

Allow us to display information to the user in a simple
dialog box or ask the user a question and provide button
options the user can click on to respond to the question

Messagetext

is the message you want to display for the user
(must be entered in quotation marks)

Buttonoptions

(optional) is text for the buttons you want to
appear in your message box for the user to click on. The
default is an OK button. If we want a Yes button and a No
button, we would enter vbYesNo for the buttonoptions. The
vb stands for variable buttons. We are telling Excel what
variable buttons we want included in our Message Box. We
do not put quotation marks around the buttonoptions

Titletext

(optional) displays a caption (or title) at the top of
your message box (must be entered in quotation marks)

Response

is a variable location that has been defined in the
macro code that will store the value of the button that was
selected by the use

If…Then…Else…End If

works like IF function, provide code for excel to test, use one or more lines of code to specify what we want Excel to do if the test is true and one or mor elines of code to specify what we want excel to do if test is false

private macro

a macro that is triggered when some predetermined event occurs It relies upon built-in events that are recognized
by Excel.

user defined functions

Special type of Visual Basic procedure, which
returns a calculated result. Used to create custom functions to perform calculations in your macros or to use directly in your workbook

Do…Loop

tells excel to keep running one or more lines of code over and over again until a certain condition is met or while a certain condition is met

Do…Loop Until

excel tests condition AFTER running through lines of code in loop once first, loop continues UNTIL the condition becomes true

Do…Loop While

excel tests condition AFTER running through lines of code in loop once first, loop continues WHILE the condition is true

Do Until…Loop

excel tests condition FIRST BEFORE running through lines of code in loop, loop continues UNTIL the condition becomes true

Do While…Loop

excel tests condition FIRST BEFORE running through lines of code in loop, loop continues WHILE the condition is true

MsgBox(“Prompt”)

syntax for a simple Message Box used to display information

Response = MsgBox(“Prompt”,vbYesNo,”Title”)

syntax for a message box that will ask a question and provide the user with a Yes and No button to click on in response to the question. dim response as string