Accounting 2258 Exam#3 MACROS Hockman

Variable

A named location used for storing data during the execution of a macro. preferable to define your variable before using it in your macro

What is a MACRO

A series of commands, actions, and functions that can be stored and initiated whenever you want to perform a particular task.

Relative Referencing (Syntax for moveing from the active cell to another cell)

ActiveCell.Offset(#,#).Select (First#=up or down) (Second#= left or right)

Relative Referencing (General syntax for selecting a cell)

ActiveCell.Select

Relative Referencing (Syntax for changing the calue of a cell)

ActiveCell.Value = [additional code]

Visual Basic Editor (VBE)

All Macros in Excel are created and stored here.

Message Boxes (Uses)

Allow us to display information in a simple dialog box. Can make one or more buttons available to user for closing the message box and responding to the message.

If...Then...Else...End If (what it does)

Allows Visual Basic to conditionally execute code, depending on a logical condition.

Absolute Referencing (Syntax for using Name Box to select a cell that has been named.

Application.Goto Reference:="CellName"

Three Main Objectives of Macros

Automate repetitive excel Tasks Automate and control input Guide users through a spreadsheet

User Defined Functions (Syntax)

Dim Variable Function FunName (argrmentlist) [statements] FuncName=Expression End Function

Variable Syntax

Dim VariableName As DataType

Do...Loop (Syntax)where logical condition is tested after the loop is executed.

Do [statements] Loop Until condition #2 Do [statements] Loop While condition

Do...Loop (Syntax) where logical condition is tested before the loop is executed

Do Until condition [statements] loop #2 Do While condition [statements] loop

If... Then complex Syntax

If Condition Then [statements] [elself condition Then [elseifstatements] [Else elsestatements]] End If

If...Then simple Syntax

If condition Then [statements][else elsestatements]

DataTypes for Variables

Integer (Whole number with no decimals) Single (Number with or without decimal place) Currency (Number with dollar sign) String (Text)

Private Macros

Macro that is triggered by some predetermined event (workbook events) Open Activate Deactivate BeforePrint BeforeClose BeforeSave

Message Box Syntax

MsgBox(Messagetext,[buttonoptions],[titletext]) Messagetext=the message you want to sent user Buttonoptions=text for the buttons you want to appear. Default "OK" Titletext=Caption or title of the box.

Private Macro Syntax

Private Sub Object_Event() [Statements] End Sub

Absolute Referencing (Syntax for selecting a cell)

Range("CellAddress").Select

Absolute Referencing (Syntax for changing the value of a cell)

Range("CellAddress").Value = [additional code]

Code Window

This is the window in the Visual Basic Editor that displays the Visual Basic code for a macro

Three Methods for creating a Macro

Use the Macro Recorder Write a new macro in Visual Basic Editor Copy, Paste, and edit a previously created macro.

User Defined Functions (What they do)

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

Module

a collection of macros located in the Visual Basic Editor.

Do...Loop (what it does)

alloes 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 Object is an element of the excel application such as a worksheet, a cell, a range of cells, or the entire workbook. `

Method

an action that can be performed on an object.

In Do...Loops While stands for

continues to loop While the condition is true

In Do...Loops Until stands for

continues to loop until the condition becomes true.

Local Window

displays variables and how they change as a macro is run.

Comments

documentation located in the Visual Basic Code to help clarify the purpose or meaning of the macro

Variable

is a named storage location in the Visual Basic Editor that contains data you can retrieve and modify whole the macro code is being executed.

Property

is an attribute of an object in excel that defines/changes one of its characteristics EX: font.colorindex=3

(argumentlist) is the

list of arguments you will be using int he function

Visual Basic for Applications (VBA)

refers to a programming lanquage you can use to create macros.

What does the F1 Key do

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