Acct2258 Final Exam

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]

Spin Button

Allows a person to select a region Properties: Linked cell, max, min

When a cell has been named and you are selecting the named cell from the Name Box drop-down menu

Application.Goto Reference:="CellName"

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

Application.GotoReference:="CellName"

User Defined Functions: returns a result, use to create custom function to perform calculations in your macros or to use directly in workbook

Dim Variable as (string, integer, currency, or single) Function FuncName (argumentlist) [statements} FuncName=Expression End Function

How to Record a Macro to record a new macro

Go to/add in the developer tab, Code Group, Select Record Macro. Enter the macro name. shortcut key. Select where you want to store it, make a short descriptor on what the macro does, click ok and begin recording.

Project Explorer Window

Located on left side of VBE; access macros you have recorded or written in modules or ThisWorkbook; you can insert a new module or write a new macro; accessed under View button in VBE

Why are macros used?

Macros allow us to simplify our use of computers and other tedious computer tasks. Series of commands, actions, and functions

Message Box: Allow us to display info in a simple dialog box, can make one or more buttons available to user for closing it or responding to message

MsgBox("messagetext", [buttonoptions], ["titletext"])

Private Macro

Private Sub Workbook_Open() End Sub

Combo Box

Properties: Bound column, column count, linked cell, list fill range

Check Box

Properties: linked cell, caption Can choose multiple

Option Button

Properties: linked cell, caption Can choose only one

Scroll Bar

Properties: linked cell, max, min, small change, large change

Input Box: Can be used to obtain in formation from the user, info obtained is place in a particular cell on worksheet

Range("A1").Value=InputBox("prompt","title")

General syntax for Visual Basic commands that change properties of an object is object.property=expression

Range("A3").Font.ColorIndex=3

Basic syntax for selecting a cell using absolute referencing

Range("CellAdress").Select

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

Range("CellAdress").Value = [Additional Code]

The code to clear the contents of a cell

Selection.ClearContents

Public Macro

Sub MacroName () End Sub

Three Methods that can be used to create a macro

Use a Macro recorder to record a new macro; write a new macro in visual basic editor; copy, paste, and edit/customize a previously created macro

Visual Basic Editor

all macros in excel are stored here; they can also be edited, deleted, and created here

Module

collection of macros located in VBE; can place several macros in one module or create new modules for each individual macro; can be renamed in Properties Window (can be access under View in VBE); new module can be added by clicking insert menu in VBE then click module

Local Window

displays variable and how they change as a macro is run. Useful in debugging a macro and can be accessed under view menu in VBE

Comments

documentation located in Visual Basic code to help clarify the purpose and meaning of macro; basically, provide info about the macro without affecting the actual execution of the macro. Indicated in VBE with an apostrophe at beginning of comment and are color-coded green

Visual Basic code

object.property object.method Range("A3").Select

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

Visual Basic Application (VBA)

refers to 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

Code Window

the window in visual editor that displays the visual basic code for a macro

Button options for Message Box

vbYesNo.

ThisWorkbook

where private macros are stored