Accounting 2258 Exam 3

अब Quizwiz के साथ अपने होमवर्क और परीक्षाओं को एस करें!

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


संबंधित स्टडी सेट्स

Series 66 - Insurance-Based Products

View Set

MBE (Torts, Con law, Crim law/Crim Pro

View Set

Chapter 24 The origin of species

View Set

Modern World History Patterns Final

View Set