Excel Exam 3

The basic syntax for a For...Next Loop:

- Dim Counter As Integer - For Counter= # to # (replace first # with starting number, and replace second # with ending number) - Statements (lines of code to be repeated in the For...Next Loop) - Next Counter

General Syntax for a Message Box the user needs to respond to is:

- Dim Response as String - Response=MsgBox(Prompt",VariableButtons,"Title")

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

- Private Sub Workbook_Open() - End Sub

The basic syntax for an Input Box is:

- ResultCell=InputBox("Prompt","Title") -The ResultCell will be one of two things: ActiveCell.Value (relative referencing) Range("CellAddress").Value (absolute referencing)

Combo Boxes

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

Check Boxes

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

Input Box

-An Input Box is another type of dialog box that can be created within a macro. -an Input Box asks the user to type (input) information into the dialog box, and then places that information in a specified location in Excel.

Recording a Macro Information

-Click on the Record Macro button in the Code group on the Developer tab to record a new macro. -Click on the Stop Recording button in the Code group on the Developer tab when you are done recording you macro. -When we record a macro, a Public Macro is created. -Public Macros run when initiated by the user pressing the shortcut keys, clicking on a button, etc. -Public Macros are stored in a Module in Visual Basic Editor -Visual Basic Editor is a program embedded in Excel

For...Next Loops

-For...Next Loops repeats statements (lines of code) between the For line and the Next link a specified number of times.

Option Buttons

-Only allows user to select one option from several available options -The properties to change are: 1. Caption 2. Linked Cell

Scroll Bars

-The are useful for choosing a numerical value from a large number of options -The properties to change are: 1. Linked Cell 2. Max 3. Min 4. Large Change 5. Small Change

Spin Buttons

-They are useful for choosing a numerical value from a small number of options -The properties to change are: 1. Linked Cell 2. Max 3. Min

Defining Variables

-We define variable to create a named storage space in Visual Basic Editor to store values that we need to have Excel use in a macro. -Syntax for defining a variable: Dim VariableName As DataType -We define variables for User-Defined Functions, for Message Boxes that require a user response, and for For...Next Loops

Message Box Info

-a Message Box is a dialog box that can provide a user with information or it can ask the user a question that the user can respond to by clicking a variable button option -If the Message Box asks the user a question that the user needs to respond to, the user's response needs to be stored. -We define a variable to store the user's response to the Message Box -The response can then be recalled later in the macro to indicate what Excel needs to do with the response

Private Macro Information

-a Private Macro runs automatically when a specified event takes place -this macro must be created in ThisWorkbook in Visual Basic Editor -To create a Private Macro, go to Visual Basic Editor, and double-click on ThisWorkbook in the Project Explorer Window -When we create Private Macros for this class, the event we will always use to trigger the macro will be opening the workbook.

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 for Excel to indicate which commands Excel should execute. -A macro is essentially a program, and its instructions are written in Visual Basic, where 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.

User-Defined Function

-is a type of macro that allows you to create your own function in Excel. -Once you crate a User-Defined Function in Excel, then you can use that function just like you would use the functions that are pre-set in Excel. -A User-Defined Function must be created in a Module in Visual Basic Editor.

Message Box

-is one type of dialog box that can be created within a macro. -A Message Box is used to display information to the user and/or to ask the user a question and provide the user with buttons to click on to respond to the question.

What are the five types of ActiveX Controls that we use

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

4 Different types of Do...Loop code:

1. Do...Loop Until 2. Do...Loop While 3. Do Until...Loop 4. Do While...Loop

4 Data types

1. Single (Numerical Value with or without decimal places) 2. Integer (whole number without decimal places) 3. String (Text) 4. Currency (dollar values)

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

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

ActiveCell.Offset(#,#).Select

What is the general syntax for selecting a cell using relative referencing?

ActiveCell.Select

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

ActiveCell.Value=[additional code]

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

Application.GoTo Reference:="Cell Name" Example: Use the Name Box to select a cell that has been named Accounting - Application.GoTo Reference:="Accounting"

The key word used to define a variable is

Dim

a loop that will allow you to run a set of VBA statements over and over again until a specified condition is met is known as ___?___

Do Loop

The las line of a user-defined function is

End Function

A macro name is not required when creating a public macro

FALSE

A private macro/procedure is activated by the user pressing the shortcut keys or clicking on a button

FALSE

One way to create a new macro code is to use New Macro Wizard

FALSE

The formula for a user-defined function must be entered in quotation marks.

FALSE

The last line of code for a user-defined function should be "End Sub"

FALSE

In the Visual Basic command "ActivateWorkbook.Save" , ActiveWorkbook is the

Object

a macro that will run every time the file is opened is called an event-driven macro of a _____________ macro

Private

The _____ _______ window in the Visual Basic Editor is where you access the modules containing public macros you have created

Project Explorer

In the Visual Basic command "Selection.Font.ColorIndex=3 , ColorIndex is a

Property

what is the basic syntax for selecting a cell using absolute referencing?

Range("CellAddress").Select

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

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

The first word in the first line of a public macro is

Sub

Do... Loops are used in macros to repeat lines of code until or while a specified condition is met.

TRUE

Objects, variable, and methods are used to write a macro code

TRUE

The If... Then... Else... End If code allows code to be conditionally executed depending on whether a specified logical condition has been met

TRUE

The area where code is displayed in Visual Basic is called the Code Window

TRUE

When declaring a variable, DIM stands for "define in memory"

TRUE

Absolute Referencing

Tells Excel we do not want the cell reference to change relative to where we copy and paste a formula or function

F1 Function

The 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

A macro that is triggered by a file being opened must be placed in ______________________ in order for it to run properly

ThisWorkbook

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. The are indicted in the Visual Basic Editor with an apostrophe at eh beginning of the comment, and the 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.

Methods include:

clearcontents, copy, delete, save, select, close

Do...Loop

code that tells Excel to keep running one or more lines of code over and over again until a certain condition is met or while a certain condition is met.

Open, BeforeSave, and BeforePrint, are examples of a ________ which can be used to cause private macros to run automatically when the event occurs

event

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 the Insert menu in Visual Basic Editor.

Variable

is a named storage location in the Visual Basic Editor that contains data you retrieve and modify while the macro code is being executed. Variable 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.

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 involves methods is object.method(parameter value). The parameter values are optional. For example, in the command Range("A3").Select is 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 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.

Syntax

is the set of rules specifying how you must enter the 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.

Properties include:

name, size, color, formula, value, location

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.

Relative Referencing

tells Excel we want the cell reference to change relative to where we copy and paste a formula or function

Local Window

this 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 in menu Visual Basic Editor

Microsoft Visual Basic Help

this is separate from the normal Excel Help feature. You must be in the Visual Basic Editor to access this help. Click on the 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