Accounting 2258 (excel) Exam 3 macros
3 main objective 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)
Data Types of Variables
1. Integer(whole number w/no decimals) 2. single (number with or w/out decimal place) 3. Currency(number with dollar sign) 4. String (text)
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
How many different types of Do...Loop code are available to use?
4
Which of the following would you see in code when using relative referencing?
ActiveCell
When creating macros what is. t he difference between relative and absolute referencing?
ActiveCell-Relative Range("K37")- Absolute
General Syntax for moving from the active cell to another cell using relative referencing:
ActiveCell.Offset(#,#).Select
General syntax for selecting a. cell using relative referencing:
ActiveCell.Select
General syntax for changing the value of a cell using relative referencing:
ActiveCell.Value = [additional code]
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.
Basic syntax for using Name Box to select a cell that has been named:
Application.Goto. Reference:="CellName"
Which property do you change for a Combo Box to indicate the column from the List Fill Range you want displayed into the Linked Cell?
Bound Column
What property do you change for a Check Box to indicate what information should be displayed to the right of the check box?
Caption
Which ActiveX Control would you use to create a drop-down menu for users to click on to select from a list of options?
Combo Box
Which step should be completed first when creating a user-defined function?
Define variables for the arguments in your function.
For...Next Loop Syntax
Dim Counter As Integer For Counter = start To end [statements] Next Counter
Which of the following is the correct line of code to define a variable named Stock to store numerical values that are whole numbers?
Dim Stock as Integer
user defined function syntax:
Dim Variable as DataType Function FuncName (argument list) [statements] FuncName=Expression End Function
Defining and Using Variables Syntax:
Dim VariableName As DataType
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
If you want to place a Scroll Bar in Cell F4 that will change the value displayed in Cell F3, what would you enter for the Linked Cell property?
F3
T/F A Do Loop can be used to delete lines of code.
False
T/F ActiveX Controls are created from the Form Controls toolbox.
False
T/F An Input Box includes buttons for the user to click on to respond to a question.
False
T/F Application.Reference:="Pumpkin" is the correct line of code for to indicate the cell named Pumpkin should be selected using the Name Box.
False
T/F The Linked Cell property for a Combo Box indicates where the ActiveX Control has been placed on the worksheet.
False
T/F User-defined functions are stored in Sheets in the Visual Basic Editor.
False
T/F You should add the Macros tab to the Ribbon in Excel, so you can record, create, edit, and run macros.
False
T/F You should use Decimal as the Type if you are defining a variable that may or may not have decimals.
False
If Then Else Syntax
If condition Then [statements] Else [else statements] End if
Where are public macros stored?
In a module in Visual Basic Editor
Which of the following is the correct data type to use when defining a variable to store whole numbers?
Integer
Message Box Syntax
MsgBox("messagetext", [buttonoptions], ["titletext"]) or Dim Response As String Response=MsgBox("messagetext",[buttonoptions],[:titletext"])
Workbook Events
Open Activate Deactivate BeforePrint BeforeClose BeforeSave
Private Macros Syntax
Private Sub Object_Event() [statements] End Sub
What is the correct line of code for the first line of a Private Macro that will run when the workbook is opened?
Private Sub Workbook_Open()
Basic syntax for selecting a cell using absolute referencing:
Range("CellAddress").Select
General syntax for changing the value of a cell using absolute referencing:
Range("CellAddress").Value =[additional code]
What is the correct line of code to select Cell V43 using absolute referencing?
Range("V43").Select
Input Boxes Syntax
ResultCell=InputBox (prompt,[title])
What is not one of the properties you change for a Spin Button?
Small Change or Large Change
User Defined Functions
Special type of Visual Basic procedure, which returns a result and Used to create custom functions to perform calculations in your macros or to use directly in your workbook
T/F Option Buttons are useful when you only want the user to be able to select only one choice from a list of options.
True
T/F The Large Change indicates how much the value increases or decreases by when you click between the arrows on a Scroll Bar.
True
T/F You can define a variable to store the user's response to a message box.
True
T/F You should use String as the data type when defining a variable to store text.
True
Private Macro
a macro that is triggered when some predetermined event occurs
macro
a series of commands, actions, and functions that can be stored and initiated (run) whenever. you want to perform a particular task
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.
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)
What tab on the Ribbon do you go to if you want to record a macro?
developer
Local Window
displays variables and how they change as a macro is run
Comments
documentation located. in the visual basic code to help clarify the purpose and meaning of the macro
T/F Private Macros are stored in a Module in Visual Basic.
false
Where are Private Macros stored?
in ThisWorkbook in Visual Basic Editor
Where are User-Defined Functions stored?
in a module in Visual Basic editor
Module
is a collection of macros located in the visual basic editor
Variable
is a named storage location in visual basic. editor that contains data you are retrieve and modify while the macro code being executed
Method
is an action that can be performed on an object (such as clearcontents, copy, delete, select, save, close, etc)
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)
object
is an element of the excel application (such as a worksheet, a cell, or. the entire workbook)
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 hoe. you must enter visual basic
ActiveX control
prewritten macro in excel (can change to properties to what you need it to do)
VIsual. Basic gor Applications (VBA)
refers to a programming language you can use to create macros --a descendant of BASIC programming
Absolute referencing
tells excel we do not want the cell reference to change relative to where we copy and paste a formula of function
relative referencing
tells excel we want the cell reference to change relative to where we copy and paste a formula of function
Syntax 1&2:
the logical condition is tested after the loop has been executed once
Syntax 3&4:
the logical condition is tested before the loop is executed
Code Window
the window in Visual Basic Editor (VBE) that displays the Visual Basic code (commands) for a macro
