Mizzou Accountancy 2258 Exam 3 Macros
What are the 3 main objectives of Macros?
1) Automate repetitive Excel tasks. 2) Automate and control input, 3) Guide users through a spreadsheet
What are the 3 methods for creating a Macro?
1) Use Macro Recorder to record a new macro. 2) Write a new macro in Visual Basic Editor. 3) Copy, paste, and edit a previously created macro
Module
A collection of macros in VBE. Can be renamed in the Properties Window which is accessed under the View menu in VBE.
Variable
A named storage location in VBE that contains data you can retrieve and modify while the macro code is being executed. Created and defined within the macro code.
Macro
A series of commands, actions, and functions that can be stored and initiated whenever you want to perform a particular task. Written in Visual Basic and referred to as "procedures"
What is the syntax for moving from the active cell to another using relative referencing?
ActiveCell.Offset(#,#).Select
What is the syntax for selecting a cell with Relative Referencing?
ActiveCell.Select
What is the syntax for changing the value of a cell using relative referencing?
ActiveCell.Value=[additional code]
Message Boxes
Allow us to display information in a simple dialog 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 (copy, delete, save, etc.).
Property
An attribute of an object in Excel that defines/changes one of its characteristics such as name, size, color, formula, value.
Object
An element of Excel such as a worksheet, cell, range of cells, or the entire workbook. Macro code is written to manipulate this by performing a method or changing a property.
What is the syntax for using Name Box to select a cell that has been named
Application.Goto Reference:="CellName"
How does one access Microsoft Visual Basic Help?
Click on Help while in VBE, click on Visual Basic for Application Language Reference
How can a new module be added?
Clicking on Insert menu in VBE then clicking on Module
What is the syntax for defining a variable?
Dim VariableName As DateType ex: Dim Response As String
Local Window
Displays variables and how they change as a macro is run. Useful in debugging a macro. Accessed under the View menu in VBE
Comments
Documentation located in Visual Basic code to help clarify the purpose and meaning of the macro. Provide information about the macro without affecting the actual execution. Indicated with an apostrophe at the beginning of the comment and are color coded green
What are 4 types of variables?
Integer (whole number w/ no decimal), Single (number with or without decimal place), Currency (number with dollar sign) and String (text)
Project Explorer Window
Located on the left side of the VBE, used to access macros that are already recorded or written.
What is the syntax for a message box?
MsgBox(messagetext, [buttonoptions], [titletext]) Dim Response As String Response=MsgBox(messagetext, [buttonoptions], [titletext])
What 4 things are used to create Visual Basic commands?
Objects, Properties, Methods, and Variables
Visual Basic Editor (VBE)
Program embedded in Excel that is accessed through the Developer tab. All macros are stored here and new macros can be created or edited.
What is the syntax for changing the value of a cell using absolute referencing?
Range("Cell Address").Value=[additional code]
What is the syntax for selecting a cell using absolute referencing?
Range("CellAddress").Select
Microsoft Visual Basic Help
Separate from normal Excel Help in that you must be in VBE to access it.
Visual Basic Commands
Steps of the macro that define its functionality. These commands are also called "macro code"
Visual Basic for Applications (VBA)
The programming language used to create macros that is used in all Office products
Syntax
The set of rules specifying how one must enter Visual Basic commands. Must be followed precisely or the macro will not run
Code Window
The window in VBE that displays the Visual Basic code (commands) for a macro
F1 Key
Will retrieve help when pressed while an object, property, or method of Visual Basic is highlighted
What is the general syntax for VBE code involving Methods?
object.method(parameter value)
What is the general syntax to change the Property of an Object?
object.property=expression Range("A3").Font.ColorIndex=3