Accounting 2258 Final Mizzou

Buttonoptions:

(optional) is the text for the buttons you want to have 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 this.

Titletext:

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

Defining and Using Variables:

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

Workbook events that trigger private macros:

-Open -Activate -Deactivate -BeforePrint -BeforeClose -BeforeSave

5 types of Active X Controls:

-Spin Button -Scroll Bar -Option Button -Check Box -Combo Box

3 Main Objectives of Macros:

1. Automate repetitive Excel tasks (improves efficiency) 2. Automate and control input (improves efficiency and accuracy) 3. Guide users through a spreadsheet (improves efficiency and accuracy)

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

Private Macros:

A private macro is triggered when some predetermined event occurs Relies upon built-in events that are recognized by Excel.

Counter:

A variable defined in Visual Basic as an integer (must be whole number). This variable will be used to store the number of times the For...Next loop has been executed. The whole number will automatically be increased each time the loop is executed. The variable doesn't have to be named Counter. It can be called anything you want, as long as you use whatever you name it consistently in each of the lines of syntax shown above.

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

ActiveCell.Offset(#,#).Select

General syntax for selecting a cell using relative referencing:

ActiveCell.Select

General 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

If...Then...Else...End If:

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

Combo Boxes:

Allows the user to select one option from a drop-down menu Properties to change: -Linked Cell -List Fill Range -Bound Column -Column Count

For...Next Loop:

Allows us to repeat a series of Visual Basic statements (lines of code) a specified number of times.

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.

Check Boxes:

Allows user to select as many or as few of the options as they desire Properties to change: -Caption -Linked Cell

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

Application.Goto Reference:="CellName"

Input Boxes:

Can be used to obtain information from the user. Information obtained is then placed in a particular cell on the worksheet.

End:

Indicates how many times the For...Next loop should be executed.

Start:

Indicates what number to start counting with in the stored variable location.

Public Macros are stored in a _________ in Visual Basic Editor.

Module

Option Button:

Only allows user to select one option from several available options Properties to change: -Caption -Linked Cell

The first and last line of our Private Macros will be:

Private Sub Workbook_Open() End Sub

To create a Private Macro, go to Visual Basic Editor, and double-click on ThisWorkbook in the __________ _________ __________.

Project Explorer Window

When we record a macro, a __________ ___________ is created.

Public Macro

________ _______ run when initiated by the user pressing the shortcut keys, clicking on a button, etc.

Public Macro

Basic syntax 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]

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

Scroll Bars:

Useful for choosing a numerical value from a large number of options Properties to change: -Linked Cell -Max -Min -Large Change -Small Change

Spin Button:

Useful for choosing a numerical value from a small number of options Properties to change: -Linked Cell -Max -Min

We define variables for _______________, for ____________ that require a user response, and for ___________________.

User-Defined Functions Message Boxes For...Next Loops

Title (optional):

a caption that will appear at the top of your input box (must be in quotation marks).

Variable:

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.

Property:

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:

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.

Active X Control:

are pre-written macros in Excel

Visual Basic Commands:

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

Prompt:

asks the user for input (must be in quotation marks)

Variable name can:

be anything you want, but you cannot have a space in the variable name.

Module:

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.

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.)

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.

Comments:

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 Editor (VBE):

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.

[elsestatements] indicate what happens if the condition is ___________.

false

Response:

is a variable location that has been defined in the macro code that will store the value of which button was selected by the user. There is more information about defining variables on the next page.

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.

Messagetext:

is the message you want to send to the user (must be in quotation marks).

The second number indicates how many cells we should move:

left or right

Project Explorer Window:

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.

left:

negative

up:

negative

Currency:

number with dollar sign

Single:

number with or w/o decimal place

down:

positive

right:

positive

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.

Microsoft Visual Basic Help:

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.

Macro:

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. 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.

String:

text

ResultCell:

the location on your worksheet where you wish to place the user's response NOTE: The proper syntax is either:Range("A1").Value or ActiveCell.Value

Syntax:

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:

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

[statements] indicate what happens if the condition is ______.

true

The first number indicates how many cells we should move:

up or down

vb stands for:

variable buttons

Integer:

whole number with no decimals

F1 function key:

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