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.
