Macros
Data types of a variable: (Dim Variable Name as Datatype)
1. Integer = whole #, NO decimals 2. Single = # with or without a decimal place 3. Currency = # with a dollar amount 4. String = text
A variable may be of several different data types:
1. Integer = whole number with no decimals 2. Single = number with or without decimals places 3. Currency = number with dollar sign 4.String = test Variable name can be anything
How is a private macro/procedure activated?
By built in events recognized by Excel -Open -Activate -Deactivate -BeforeClose -BeforePrint -BeforeSave
How can a new module be added?
By clicking on the Insert menu in VBE and then clicking on Module
When assigning short cuts, you should use....
Capital Letters
Microsoft Visual Basic Help
Seperate from normal Excel help feature. Must be in Visual Basic to access this help. Click Visual Basic for Applications Language Reference link
User Defined Functions
Special types 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
Titletext (optional) in a Message Box
Supplies a caption or title at the top of your message box (MUST be in quotation marks)
Where can modules be renamed?
The Properties Window which is accessed under the View menu in VBE
Object
an element of the Excel Application -ex. 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
How do you write a new macro from the Project Explorer Window?
by inserting a new module or clicking on ThisWorkbook in the Project Explorer Window
Where are variables 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
What are the 3 main objectives of Macros?
1. Automate repetetive Excel task - improves efficiency 2. Automate and control input -improves efficiency and accuracy 3. Guide users through a spreadsheet - improves efficiency and accuracy
Define Variable
A named location in Visual Basic used for storing data during the execution of a macro -Define before using
Message Boxes
Allow us to display information in a simple dialog box. You can make one or more buttons available to user for closing the message box and responding to the message
If....Then....Else....End If
Allows Visual Basic to conditionally execute code, depending on a logical condition
Snytax "Dim variablename"
Creates a variable with no data type specified Takes up more room space because enough room is reserved for all data types
What does Dim stand for when declaring a variable?
Define in Memory
What should be the last line of code for a user-defined function?
End Function
T/F: User defined functions must be stored in ThisWorkbook in Visual Basic Editor
FALSE stored in module
Define Visual Basic Editor (VBE)
Is embedded within Excel and can be accessed through the Developer Tab. Macros can be created, edited, or deleted here.
Are spaces allowed in macro names?
NO
What are Macros referred to in in Visual Basic terminology?
Procedures
Visual Basic Commands
The steps of the macro that define the macro's functionality. We use objects, properties, methods, and variable to create Visual Basic Commands
Buttonoptions (optional) within a message box
The text for the buttons you want to have appear in your message box for the user to click on -default is an OK button
The Code Window
The window in Visual Basic Editor (VBE) that displays the Visual Basic code (commands) for a macro
What is the benefit of a Macro?
They allow us to simplify our use of computers and alleviate tedious computer tasks
Until Loops
Until: continues to loop UNTIL the condition becomes true (Will stop when condition becomes true)
How to use relative referencing when recording a Macro
Use relative referencing button under the Code section on the developer tab
What does vb stand for in a Message Box buttonoption?
Variable Buttons, don't use quotation marks for this
All Macros created in Excel are stored where?
Visual Basic Editor (VBE) on the Developer tab
Where are Macro instructions written?
Visual Basic which is a programming language
Different Buttonoptions
Want a Yes and a No button - vbYesNo
The else statements in an If..Then indicates what?
What should happen if the condition is false (if the condition is not met)
The statements in an If.. Then indicates what?
What should happen if the condition is true (if the condition is met)
How are comments indicated within Visual Basic Code (commands)?
With an apostrophe at the beginning of the comment and they are color-coded green
Messagetext within a message box
Within the message boxes -the message you want to send to the user""
Module
a collection of macros located in Visual Basic Editor. Several macros can be placed in one module or each maco can have it's own module
Variable
a named storage location in Visual Basic editor that contains data you can retrieve and modify while the macro code is being executed
Method
an action that can be performed on an object - ex. clearcontents, copy, delete, select, save, close
Property
an attribute of an objet in Excel that defines/changes one of its characteristics (name, size, color, formula, value, or location.)
Define debugging
correcting errors in a macro
Local Window
displays variables and how they change as a macro is run. Useful in debugging a macro. Accessed under the View menu in Visual Basic Editor
Comments
documentation located in Visual Basic code (commands) to help clarify the purpose and meaning of the macro. Provide info on the macro without affecting the actual execution of the macro.
Project Explorer Window
located on the left side of Visual Basic Editor. Within, you can access macros you have recorded or written in modules or ThisWorkbook. Can be accessed under the View menu in Visual Basic Editor
The general syntax for Visual Basic code involving methods
object.method(parameter value). - parameter values are optional - Ex. Range("A3").Select = method is select
General syntax for commands that change the properties of an object
object.property=expression - Range("A3").Font.ColorIndex=3 = property is Font.ColorIndex=3
What are used to create macro code (VB commands)?
objects, methods, properties and variables
All objects have what?
properties
Syntax
the set of rules specifying how you must enter Visual Basic commands. You must follow syntax precisely or the macro will not run
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
What are the 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
Macro
A series of commands, actions and functions that can be stored and initiated (run) whenever you want to perform a particular task. (a program)
What does a Macro provide?
A set of instructions to Excel that indicate which commands Excel should execute
The If.....Then.....Else.....End If code
Allows code to be conditionally executed depending on whether a specified logical condition has been met
Do....Loop
Allows us to repeat a series of Visual Basic statements (lines of code) UNTIL a specified condition is met or WHILE a specific condition is met
How to add the developer tab
File, Options, Customize Ribbon, Select check box for Developer
How to save/enable macros?
File, Save As, Save As type drop down to "Excel Macro-Enabled Workbook"
What are Visual Basic Commands also referred to as ?
Macro Code
While Loops
While: continues to loop WHILE the condition is true (Will stop when condition becomes false)
Define Visual Basic for Applications (VBA)
a programming language you can use to create macros. - a descendant of the BASIC programming lang. used in all Office products
Response in a Message Box
a variable location that has been defined in the macro code that will store the value of which button was selected by the user
