Accounting 2258 Exam 3
what are macros referred to as
"procedures" in Visual Basic terminology
EX: 1 Move from Cell A1 to Cell C4 using relative referencing: 2 Move from Cell F3 to Cell B7 using relative referencing: 3 Move from Cell H8 to Cell K2 using relative referencing: 4 Move from Cell E6 to Cell D1 using relative referencing:
1 ActiveCell.Offset(3,2).Select 2 ActiveCell.Offset(4,-4).Select 3 ActiveCell.Offset(-6,3).Select 4 ActiveCell.Offset(-5,-1).Select
EX: 1 Select Cell C4 using absolute referencing 2 Move from Cell F3 to Cell B7 using absolute referencing: 3 Use the Name Box to select a cell that has been named Accounting:
1 Range("C4").Select 2 Range("B7").Select 3 Application.Goto Reference:="Accounting"
3 purposes for why we use macros
1 Automate repetitive Excel tasks 2 Automate and control input 3 Guide users through a spreadsheet
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
Syntax 1&2 vs. Syntax 3&4: logical condition is tested
1&2: : the logical condition is tested after the loop has been executed once. 3&4: : the logical condition is tested before the loop is executed.
What are User Defined Functions
A 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
what is a module, where can they be renames, and how do you add a new module
A 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. 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.
CONTROL STRUCTURES (Syntax, code structures, and VBA code) Iteration controls are also known as what, and what do you use?
AKA looping or repetition Use Do...Loop(will loop indefinitely until a specified condition is met or while a specified condition is met)
Absolute vs. Relative referencing
Absolute: : Range ("CellAddress"). Select Relative: ActiveCell.Select
General syntax for selecting a cell using relative referencing: General syntax for changing the value of a cell using relative referencing: General syntax for moving from the active cell to another cell using relative referencing:
ActiveCell.Select ActiveCell.Value = [additional code] ActiveCell.Offset(#,#).Select
If...Then...Else...End If allows what
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 in Excel worksheets.
What do Iteration Controls allow through the '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.
For...Next Loop allows
Allows us to repeat a series of Visual Basic statements (lines of code) a specified number of times.
what does the Do...Loop do
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.
When a cell has been named and you are selecting the named cell from the Name Box drop-down menu:
Application.GoTo Reference: = "Cell Name"
Macros which are activated by an event, such as opening or closing a workbook, are called what and where are they placed
Called: Private procedures Placed in: ThisWorkbook
what variable is 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.
Counter, it doesn't have to be named Counter, it can be named anything you want, as long as you use whatever name you call it consistently in each of the lines of the syntax
The key word used to declare (or define) a variable is
Dim
For...Next Loop Syntax:
Dim Counter As Integer For Counter = start To end [statements] Next Counter
EX: Create a function to calculate accounts receivable turnover, which is calculated as follows: Net Sales/((Prior Year Accounts Receivable + Current Year Accounts Receivable)/2)
Dim NetSales As Currency Dim PYAR As Currency Dim CYAR As Currency Function ARTurnover (NetSales, PYAR, CYAR) ARTurnover=NetSales/((PYAR+CYAR)/2) End Function
EX: Dim Start As Integer For Start = 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" ActiveCell.Offset(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 Start
User defined functions for Syntax:
Dim Variable As DataType Function FuncName (argumentlist) [statements] ---FuncName=Expression End Function ---^ that part is the mathematical calculation while the rest is the list of arguments you will use in the function
defining and using variables for syntax:
Dim VariableName As DataType ex: Dim Response As String Dim Counter As Integer Dim Revenue As Currency
what does the Local Window display, what is it useful for, and where can it be accessed
Displays variables and how they change as a macro is run useful in debugging (correcting errors) in a macro can be accessed under the view menu in Visual Basic Editor
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
Do...Loop
The last line of a user-defined function is
End function
Open, BeforeSave, and BeforePrint are examples of a(n) _______ which can be used to cause certain macros to run
Event
Form Controls vs. ActiveX Controls
Form: designed to work only within excel many require use of existing macros ActiveX: use macros written specifically for the control (these are macros that have already been pre-written in Excel for you) as opposed to worksheet specific macros
Simple format of Syntax in an easier way to read
If condition Then [statements] [Else [elsestatements]] End If
more complex Syntax, that allows for greater flexibility in building macros with nested IF statements
If condition Then [statements] [ElseIf condition Then [elseifstatements] [Else [elsestatements]] End If
Most simple format of Syntax (one liner)
If condition Then [statements] [Else elsestatements]
with Selection control (branching), you use
If...Then...Else...End If If condition Then [Statements][Else Statements]
what different data types could a variable be
Integer (whole number with no decimals) Single (number with or w/o decimal place) Currency (number with dollar sign) String (text)
what 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)
Macro code
3 ways we can create a macro
Macro recorder Visual Basic Editor Copy and paste
the message you want to send to the user (must be in quotation marks).
Messagetext
FUNCTIONS Message Box (Syntax, code structures, and VBA code)
MsgBox (Message text, [button options], [title text])
In the Visual Basic command "ActiveWorkbook.Save", ActiveWorkbook is a(n)
Object
When we use ActiveCell.Offset(#,#).Select, the offset, first number, and second number indicate:
Offset: how many cells up or down and how many cells to the right or left we should move from whatever the active cell is. first number: 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. second number: indicates how many cells we should move to the right or left. If the number if 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.
Workbook events:
Open Activate Deactivate BeforePrint BeforeClose BeforeSave
Private Macros for Syntax: • Example of a procedure that will run upon opening a workbook:
Private Sub Object_Event() [statements] End Sub ex: Private Sub Workbook_Open() Application.GoTo Reference:="Start" End Sub
what asks the user for input (must be in quotation marks)
Prompt
In the Visual Basic command "Selection.Font.ColorIndex=3, ColorIndex is a
Property
EX: 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")
Basic syntax for selecting a cell using absolute referencing: General syntax for changing the value of a cell using absolute referencing: Basic syntax for using Name Box to select a cell that has been named:
Range("CellAddress").Select Range("CellAddress").Value = [additional code] Application.Goto Reference:="CellName"
the location on your worksheet where you wish to place the user's response is the
ResultCell NOTE: The proper syntax is either: Range("A1").Value or ActiveCell.Value
FUNCTIONS Input Box (Syntax, code structures, and VBA code)
ResultCell = InputBox (Prompt, [title])
Input Boxes Syntax:
ResultCell = InputBox("prompt", ["title"])
what indicates what number to start counting with in the stored variable location. and what indicates Indicates how many times the For...Next loop should be executed.
Start End
The first word in the first line of a public macro is
Sub
Example of Do...Loop Syntax
Sub Highlight() ' 'Highlight Macro 'Macro recorded on ______ by _______ ' Keyboard shortcut: Ctrl+Shift+L ' Range ("A4").Select Do Until ActiveCell.Value="" ActiveCell.Range("A1:H1").Select If ActiveCell.Offset(0,2).Value>40 Then Selection.Font.ColorIndex=3 End If ActiveCell.Offset(1,0).Select Loop End Sub
Syntax format for Message boxes
Syntax: MsgBox("messagetext",[buttonoptions],["titletext"]} or Dim Response As String Response=MsgBox("messagetext", [buttonoptions], ["titletext"])
Defining/using variables, specifying the data type for (Syntax, code structures, and VBA code) String Integer Currency Single
Syntax:: Dim VariableName As Datatype Ex: Dim response as String Dim counter as Integer
The ______ window in the Visual Basic Editor is where you access the modules containing public macros you have created
The Project Explorer
when answering a response(yes or no) message box, where is the user's response stored
The user's response is stored in the variable location named Response as either a vbYes if the Yes variable button was clicked on by the user or a vbNo if the No variable button was clicked on by the user.
a caption that will appear at the top of your input box (must be in quotation marks).
Title (optional)
what does the Visual Basic Editor (VBE) provide?
VBE provides advanced editing capabilities including automatic syntax checking, tips and quick information, color coded programs for readability, etc.
In between syntaxes, what happens when does looping stop
While: continues to loop while the condition is true. (Will stop when condition becomes false.) Until: : continues to loop until the condition becomes true. (Will stop when condition becomes true.)
what is a variable and where are they created/ defined?
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.
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.
Definition of a macro
a series of commands, actions, and functions that can be stored and initiated
What do macros provide
a set of instructions to Excel that indicate which commands Excel should execute.
what is a variable and when is it preferable to define
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 *cannot have a space in the variable name, but it can be whatever you want*
response is what
a variable location that has been defined in the macro code that will store the value of which button was selected by the user.
what do message boxes allow and make available?
allow us to display info in a simple dialogue box can make one or more buttons available to user for closing the message box and responding to the message
Method:
an action that can be performed on an object (such as clearcontents, copy, delete, select, save, close, etc.)
A macro that will run every time the file is opened is called a (2 names)
an event-driven macro or a Private macro
What is an object, how is it written in basic form
an object is an element of the Excel Application (such as a worksheet, a cell, a range of cells, or the entire workbook). 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.
Once you create a variable and give it a value, you can
assign the value to an object.
for Buttonoptions (optional) what is the default, and what would you enter if you want a Yes and No button?
default is an OK button Yes & No: Enter vbYesNO
where are comments located and what do they do
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) is embedded within
embedded within Excel and can be accessed through the Developer tab.
what are macros essentially
essentially a program, and its instructions are written in Visual Basic, which is a programming language. Macros allow us to simplify our use of computers and alleviate tedious computer tasks.
A macro that is triggered by a file being opened must be placed where in order for it to run properly
in ThisWorkbook
all macros created in excel are stored where and what else happens here
in the Visual Basic Editor (VBE) New macros can be created, and previously created macros can be edited or deleted here.
[elsestatements]-simple/complex syntax indicate
indicate what should happen if the condition is false (if the condition is not met).
[statements]-simple/complex syntax indicate
indicate what should happen if the condition is true (if the condition is met).
what is a 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.
what is 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.
what is a syntax and what happens if you don't follow it precisely
is the set of rules specifying how you must enter Visual Basic commands. You must follow the syntax precisely, or the macro will not run.
what is the code window
is the window in Visual Basic Editor (VBE) that displays the Visual Basic code (commands) for a macro
Syntax for 'For...Next' and what will it do
it will loop a specific number of times Syntax: -Dim Counter As Integer -Counter = 1 -For Counter = Start # to End # -[Statements/lines of code] -Next Counter With....End With
Where is the Project Explorer Window located and what can you access through it
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.
Input boxes are used to
obtain information from the user. Information obtained is then placed in a particular cell on the worksheet
Private Macros rely on what
rely upon built-in events that are recognized by excel
how can you access Microsoft Visual Basic Help and what is it separate from
separate from the normal excel help feature, you must be in Visual Basic Editor to access it. 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
CONTROL STRUCTURES (Syntax, code structures, and VBA code) How is VBA executed unless a control structure changes the order in which a set of commands is run?
sequentially
what does vb stand for and what does it do?
stands for variable buttons it tells excel what variable buttons we want included in our message box, we don't put this in quotation marks
Titletext (optional) does what
supplies a caption (or title) at the top of your message box (must be in quotation marks).
what statements are like the value-if-true argument in an IF function?
the simple and complex Syntax statements
Visual Basic Commands are
the steps of the macro that define the macro's functionality. AKA"macro code". We use objects, properties, methods, and variables to create Visual Basic commands, or macro code.
Buttonoptions (optional) is what
the text for the buttons you want to have appear in your message box for the user to click on
Only use for Form Controls
use only to create and assign a button to a macro, so you can click on the button to run a macro you have written yourself
Work sheet specific macros
use to manage specific events triggered when the control is used
when is a private macro triggered?
when some predetermined event occurs
purpose of 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.
Do...Loop order
• Syntax 1: Do...Loop Until -Do -[statements] -Loop Until condition • Syntax 2: Do...Loop While -Do -[statements] -Loop While condition • Syntax 3: Do Until ...Loop -Do Until condition -[statements] -Loop • Syntax 4: Do While...Loop -Do While condition -[statements] -Loop
Syntax/ code structures/ VBA code for PROCEDURES
•Public Subs Syntax: -Sub Name of Macro () -End Sub •Private Subs Syntax: -Private Sub ObjectEvent () -[Statements] -End Sub •User-Defined Functions Syntax: -Dim -Variable -Function FuncName(ArgumentList) -[Statements] -FuncName = Expression -End Function
