Macro Exam

Titletext

(Optional) Supplies a caption (or title) at the top off your message box (must be in quotation marks).

Buttonoptions

(Optional) 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. The vb stands for variable buttons. We are telling Excel what variable buttons we want included in our Message Box. We do not put this in quotation marks.

Private Macros (Procedures)

- A private macro that is triggered when some predetermined event occurs - It relies upon built-in events that are recognized by Excel - Workbook Events: Open, Activate, Deactivate, BeforePrint, BeforeClose, BeforeSave

Defining and Using Variables

- A variable is a named location in Visual Basic used for storing data during the execution of a macro - It is preferable to define your variable before using it in your macro. - A variable may be of several different data types. These are the data types we will use: Integer (whole number with no decimals), Sngle (number with or without decimal place), Currency (number with dollar sign), String (text)

The 3 Main 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)

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

Public Macros (Subs)

- Stored in Modules in Visual Basic and can be recorded or written from scratch and are initiated by the user. - Syntax: Sub MacroName() End Sub

Private Macros (Subs)

- Stored in ThisWorkbook in Visual Basic and cannot be recorded. They can only be written from scratch, and they run automatically upon the occurrence of an event. - Syntax: Private Sub Workbook_Open() End Sub

Absolute referencing

- Tells Excel we do not want the cell reference to change relative to where we copy and paste a formula or function. - $K$37 - Range("K37")

Relative referencing

- Tells Excel we want the cell reference to change relative to where we copy and paste a formula or function. - K37 - ActiveCell

The 3 Methods We Will Use For Creating a Macro

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

Describe how to use the Record Macro feature to record a new macro in Excel.

1. Click on the Developer tab. 2. Click on Record Macro button in the Code group. 3. Enter information into Record Macro dialog box, including macro name, shortcut key, where to store macro, and description of macro 4. Decide whether relative or absolute referencing should be used while recording your macro, and press the Use Relative Refrences button, when relative referencing is needed 5. Press Stop Recording when done recording macro 6. To view macro code for recorded macro, go into Visual Basic Editor and double-click on Modules (recorded macros are public macros, so they are stored in Modules)

How to assign a button to a macro

1. Click on the Insert button in the Controls group on the Developer tab 2. Select the Button option from the Form Controls area 3. Draw the button where you want it on your Excel worksheet. 4. Select the macro you want to assign the button to. 5. Click OK. 6. Right-click on the Button and select Edit Text to type different text on the button.

Module

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.

Do...Loop

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 a:

Private Macro

A macro that will run every time the file is opened is called a _____ macro.

Variable

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

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

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.

Response

A variable location that has been defined in the macro code that will store the value of which button was selected by the user.

Message Boxes

Allow us to display information in a simple dialog box or ask a question and provide button options the user can click on to respond to the question.

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

Allows Visual Basic to conditionally execute code, depending on a logical condition. If...Then statements created in Visual Basic for a macro work very much like the IF functions we have used in the past Excel worksheets. We use code to specify what we want Excel to do if a test is true and what we want Excel to do if a test is false.

Combo Box (when it's used, what properties have to be changed)

Allows the user to select an 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 (when it's used, what properties have to be changed)

Allows user to select a many or s few of the options as they desire. Properties to change: caption, linked cell.

Method

An action that can be performed on an object (such as clear contents, 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

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"), indicating that Cell A3 is the object being manipulated through this macro code.

Prompt

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

Range("CellAddress").Select

Basic syntax for selecting a cell using absolute referencing

Application.Goto Reference:="CellName"

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

Input Boxes

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

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

For...Next Loop Example

Dim Counter As Integer For Counter = 1 To 6 Do ActiveCell.Value=InputBox("Please enter a valid item number", "Item Number") Loop Until ActiveCell.Offset(0,1).Value<>"Not valid item number" ActiveCellOffset(0,2).Select ActiveCell.Value = InputBox("Please enter the quantity", "Quantity") ActiveCell.Offset(1,-2).Select Dim Response As String Response = MsgBox("Do you have another item to invoice?",vbYesNo,"Another Item?") If Response = vbNo Then Exit for Next Counter

For...Next Loop Syntax

Dim Counter As Integer For Counter = start To end [statements] Next Counter

Create a function to calculate accounts receivable turnover, which is calculated as: Net Sales/((Prior Year Accounts receivable + Current Year Accounts Receivable)/2)

Dim NetSales As Currency Dim NetSales As Currency Dim PYAR As Currency Dim CYAR As Currency Function ARTurnover (NetSales, PYAR, CYAR) ARTurnover=NetSales/((PYAR+CYAR)/2) End Function

Variable Examples

Dim Response As String Dim Counter As Integer Dim Revenue As Currency

Message Box with Yes and No buttons example

Dim Response As String Response=MsgBox("Do you have any questions regarding today's material?", vbYesNo,"Questions")

User Defined Syntax

Dim Variable As Data Type Function FuncName (argumentlist) FuncName=Expression End Function

User Defined Functions Syntax

Dim Variable As DataType Function FuncName (argumentlist) FuncName=Expression End Function

Defining Variables Syntax

Dim VariableName As DataType

Variable Syntax

Dim VariableName As DataType

Local Window

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

Syntax 1: Do...Loop Until (logical condition is tested after the loop has been executed once)

Do [statements] Loop Until condition

Syntax 2: Do...Loop While (logical condition is tested after the loop has been executed once)

Do [statements] Loop While condition

Syntax 3: Do Until...Loop (logical condition tested before loop is executed)

Do Until condition [statements] Loop

Syntax 4: Do While...Loop (logical condition is tested before the loop is executed)

Do While condition [statements] Loop

Comments

Documentation located in the Visual Basic code to help clarify the purpose and meaning of the macro. 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.

Currency (DataType)

Dollar Values

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.

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

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

ActiveCell.Value = [additional code]

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

ActiveCell.Offset(#,#).Select

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

ActiveCell.Select

General syntax for selecting a cell using relative referencing

Object

In the Visual Basic command "ActiveWorkbook.Save", Active Workbook is an

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.

Visual Basic Editor (VBE)

Is embedded within Excel and can be accessed through the Developer tab. To access VBE, you will first need to add the Developer tab to your Ribbon in Excel. All macros created in Excel are stored in the VBE. 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

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.

ActiveCell.Offset(-5,-1).Select

Move from Cell E6 to Cell D1 using relative referencing

Range("B7").Select

Move from Cell F3 to Cell B7 using absolute referencing

Range("B7").Select

Move from Cell F3 to Cell B7 using absolute referencing:

ActiveCell.Offset(4,-4).Select

Move from Cell F3 to Cell B7 using relative referencing:

ActiveCell.Offset(-6,3).Select

Move from Cell H8 to Cell K2 using relative referencing

ActiveCell.Offset(3,2).Select

Move from cell A1 to cell C4 using relative referencing

Message Box with default Ok button example

MsgBox("Have a great weekend!")

Message Box Syntax

MsgBox("message text", [buttonoptions], ["titletext"]) Or Dim Response As String Response=MsgBox("message text", [buttonoptions], ["titletext"})

Single (DataType)

Numerical value with or without decimal places

Option Buttons (when it's used, what properties have to be changed)

Only allows user to select one option from several available options. Properties to change: caption, linked cell.

Private Macro Syntax

Private Sub Object_Event() [statements] End Sub

Example of a procedure that will run upon opening a workbook

Private Sub Workbook_Open() Application.GoToReference:="Start" End Sub

Input Box Example: You want to obtain the user's first name and place it in Cell C5, using an absolute reference.

Range("C5").Value=InputBox("Please enter your first name.", "User's First Name")

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 infall Office products, as well as some other types of software.

Input Box Syntax

ResultCell = InputBox(prompt, ["title"])

Range("C4").Select

Select Cell C4 using absolute referencing:

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.

How to add a comment to macro code

Step 1: Click on the line where you want to insert a comment. Step 2: Type an Apostrophe( ' ) at the start of a line. Step 3: Write the comment you want. Step 4: Press Enter and you fill find the comment written to be green.

String (DataType)

Text

Project Explorer Window

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

Sub

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

ResultCell

The location on your worksheet where you wish to place the user's response. Use the Range("CellAddress").Value for the ResultCell if the response should always go in a specific cell (absolute referencing). Use ActiveCell.Value for the ResultCell if the response should be placed in whatever the current active cell is (relative referencing).

Messagetext

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

Syntax

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

Visual Basic Commands

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, or macro code.

Code Window

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

If condition Then statements Else elsestatements

This one-liner is the most simple format.

If condition Then statements Else elsestatements End If

This syntax is the same as what you see in the one-liner, but can be easier to read when presented this way.

Application.Goto Reference:="Accounting"

Use the Name Box to select a cell that has been named Accounting

Scroll Bar (when it's used, what properties have to be changed)

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 (when it's used, what properties have to be changed)

Useful for choosing a numerical value from a small number of options. Properties to change: linked cell, max, min

Defining Variables

We define variables to create a named storage space in Visual Basic Editor to store values we need to have Excel use in a macro.

Record Macro

What button do you click on to record a new macro?

Developer

What tab do we use in Excel to work with macros?

Integer (DataType)

Whole number without decimal places

Title (optional)

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

When we use ActiveCell.Offset(#,#).Select, the offset indicates...

how many cells up or down and how many cells to the right or left we should move from whatever the active cell is. The first number indicates how many cells we should move up or down from the active cell. If the number is positive, it means we should move down that number of cells. If the number is negative, it means we should move up that number of cells. The second number indicates how many cells we should move to the right or left. If the number is positive, it means we should move to the right that number of cells. If the number is negative, it means we should move to the left that number of cells.

VariableName

the name we want to give to the storage space. cannot have spaces

With...End With purpose

you can perform a series of statements on a specified object without specifying the name of the object multiple times. Within a With statement block, you can specify a member of the object starting with a period, as if the With statement object preceded it.