Mizzou Accountancy 2258 Exam 3 Macros

What are the 3 main objectives of Macros?

1) Automate repetitive Excel tasks. 2) Automate and control input, 3) Guide users through a spreadsheet

What are the 3 methods for creating a Macro?

1) Use Macro Recorder to record a new macro. 2) Write a new macro in Visual Basic Editor. 3) Copy, paste, and edit a previously created macro

Module

A collection of macros in VBE. Can be renamed in the Properties Window which is accessed under the View menu in VBE.

Variable

A named storage location in VBE that contains data you can retrieve and modify while the macro code is being executed. Created and defined within the macro code.

Macro

A series of commands, actions, and functions that can be stored and initiated whenever you want to perform a particular task. Written in Visual Basic and referred to as "procedures"

What is the syntax for moving from the active cell to another using relative referencing?

ActiveCell.Offset(#,#).Select

What is the syntax for selecting a cell with Relative Referencing?

ActiveCell.Select

What is the syntax for changing the value of a cell using relative referencing?

ActiveCell.Value=[additional code]

Message Boxes

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

Method

An action that can be performed on an object (copy, delete, save, etc.).

Property

An attribute of an object in Excel that defines/changes one of its characteristics such as name, size, color, formula, value.

Object

An element of Excel such as a worksheet, cell, range of cells, or the entire workbook. Macro code is written to manipulate this by performing a method or changing a property.

What is the syntax for using Name Box to select a cell that has been named

Application.Goto Reference:="CellName"

How does one access Microsoft Visual Basic Help?

Click on Help while in VBE, click on Visual Basic for Application Language Reference

How can a new module be added?

Clicking on Insert menu in VBE then clicking on Module

What is the syntax for defining a variable?

Dim VariableName As DateType ex: Dim Response As String

Local Window

Displays variables and how they change as a macro is run. Useful in debugging a macro. Accessed under the View menu in VBE

Comments

Documentation located in Visual Basic code to help clarify the purpose and meaning of the macro. Provide information about the macro without affecting the actual execution. Indicated with an apostrophe at the beginning of the comment and are color coded green

What are 4 types of variables?

Integer (whole number w/ no decimal), Single (number with or without decimal place), Currency (number with dollar sign) and String (text)

Project Explorer Window

Located on the left side of the VBE, used to access macros that are already recorded or written.

What is the syntax for a message box?

MsgBox(messagetext, [buttonoptions], [titletext]) Dim Response As String Response=MsgBox(messagetext, [buttonoptions], [titletext])

What 4 things are used to create Visual Basic commands?

Objects, Properties, Methods, and Variables

Visual Basic Editor (VBE)

Program embedded in Excel that is accessed through the Developer tab. All macros are stored here and new macros can be created or edited.

What is the syntax for changing the value of a cell using absolute referencing?

Range("Cell Address").Value=[additional code]

What is the syntax for selecting a cell using absolute referencing?

Range("CellAddress").Select

Microsoft Visual Basic Help

Separate from normal Excel Help in that you must be in VBE to access it.

Visual Basic Commands

Steps of the macro that define its functionality. These commands are also called "macro code"

Visual Basic for Applications (VBA)

The programming language used to create macros that is used in all Office products

Syntax

The set of rules specifying how one must enter Visual Basic commands. Must be followed precisely or the macro will not run

Code Window

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

F1 Key

Will retrieve help when pressed while an object, property, or method of Visual Basic is highlighted

What is the general syntax for VBE code involving Methods?

object.method(parameter value)

What is the general syntax to change the Property of an Object?

object.property=expression Range("A3").Font.ColorIndex=3