MACROS

3 Objectives of Macros

-Automate repetitive Excel tasks (improves efficiency) -Automate and control input (improves efficiency and accuracy) -Guide users through a spreadsheet (improves efficiency and accuracy

3 Methods of Creating Macros

-Use the Macro Recorder to record a new macro -Write a new macro in Visual Basic Editor -Copy, paste, and edit/customize a previously created macro

Comments

are documentation located in the Visual Basic code to help clarify the purpose and meaning of the macro. Comments basically provide information about the macro without affecting the actual execution 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.

Visual Basic Commands

are the steps of the macro that define the macro's functionality. Visual Basic commands are also referred to as "macro code". We use objects, properties, methods, and variables to create Visual Basic commands, or macro code.

Local Window

displays variables and how they change as a macro is run. It is useful in debugging (correcting errors in) a macro. The Local Window can be accessed under the View menu in Visual Basic Editor.

F1 Function Key

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

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.

Module

is a collection of macros located in the Visual Basic Editor. You can place several macros in one module or create new modules for each individual macro. Modules can be renamed in the Properties Window, which can be accessed under the View menu in Visual Basic Editor. A new module can be added by clicking on the Insert menu in Visual Basic Editor and then clicking on Module.

Variable

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

Macro

is a series of commands, actions, and functions that can be stored and initiated (run) whenever you want to perform a particular task. Macros provide a set of instructions to Excel that indicate which commands Excel should execute. A macro is essentially a program, and its instructions are written in Visual Basic, which is a programming language. Macros allow us to simplify our use of computers and alleviate tedious computer tasks. Macros are also referred to as "procedures" in Visual Basic terminology.

Method

is an action that can be performed on an object (such as clearcontents, copy, delete, select, save, close, etc.). The general syntax for Visual Basic code involving methods is object.method(parameter value). The parameter values are optional. For example, in the command Range("A3").Select, the method is Select, indicating that Excel should select Cell A3.

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). All objects have properties. The general syntax for Visual Basic commands that change the properties of an object is object.property=expression. For example, in the command Range("A3").Font.ColorIndex=3, the property is Font.ColorIndex=3, indicating that the font color of Cell A3 should be changed to red.

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). In basic form, Visual Basic code is either written as object.property or object.method. For example, in the command Range("A3").Select, the object is Range("A3"), indicating that Cell A3 is the object being manipulated through this macro code.

Visual Basic Editor (VBE)

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

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. If it is not being displayed, this window can be accessed under the View menu in Visual Basic Editor.

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.

Syntax

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

Code Window

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