Transcript VBA Lecture 2

Objectives
•
•
•
•
•
•
•
Understand the software development lifecycle
Perform calculations
Use decision structures
Perform data validation
Use logical operators
Use the For...Next loop statement
Use the Do...Loop statement
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall.
1
Launching the VBA Interface
• To launch the VB
Editor in your
application:
• Click the File tab,
click Options,
click Customize
Ribbon, click the
Developer check
box in the Main
Tabs list, and then
click OK.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall.
2
Using the VB Editor
Menu bar and
toolbar
The Project
Explorer lists the
Workbook name
Properties window
used to set
properties for
objects.
Code Window
The VB Editor for an Excel Workbook
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall.
3
Project Explorer
• The left side of the VB Editor contains the
Project Explorer, which is similar to the
Windows Explorer.
• A project is a collection of modules and objects
needed to run an application.
• A module is a container to organize
programming code.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall.
4
Properties Window
• The Properties window displays the properties
or attributes for the currently selected object in
the Project Explorer.
• The Properties window is used to set properties
for an object that you can change or set a value.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall.
5
Code Window
• The Code window is a text editor for writing and editing
VBA programming statements.
• You enter your code statements in design time.
• VBA executes the procedure in run time.
• A procedure is a named sequence of programming
statements that performs a set of actions.
– Programming statements include: procedures, variable and
constant declarations, and mathematical expressions.
– When you create several macros in a workbook, Excel stores
each macro as a procedure in one module.
– The name you enter when you record a macro becomes the
name of an individual procedure in VBA.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall.
6
Identify Code in the Code Window
• Procedure name
• A procedure is a
named sequence of
programming
statements that
performs a set of
actions.
Programming
statements include:
procedures, variable
and constant
declarations, and
mathematical
expressions.
Comments
• Keywords - text or a
symbol used for a
specific purpose in a
programming
language
• Indented VBA
statements
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall.
7
Creating Procedures
• An event is an action, such as the user
clicking a button at run time that
triggers a program instruction
• A sub procedure is a procedure that
performs an action but does not return
a specific value, such as:
Sub Display_Message ()
Indented VBA statements
End Sub
• A function procedure performs an
action and returns a value.
• A property procedure creates or
manipulates a custom property.
• A public procedure is available to any
object in the application.
• A private procedure is available only to
a specific object or module
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall.
8
Getting Help and Debugging Errors
•
•
•
•
Use Help to learn more about VBA.
Explanation of terminology
Examples of programming code
For specific context assistance, click on a
keyword, such as Sub, and then press F1.
• The Debug menu helps you identify
errors in programming code
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall.
9
Declaring and Using Variables
• Variables are
• A program code
programmer-defined
statement can change the
names.
value at any time.
• Variables can store values • A value for a variable
that can change while the
exists only while a
application is running.
program is running.
• Variables store values in
computer memory.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall.
10
Declaring and Using Constants
• Constants store values that are specified at design time.
• Constants remain the same while the application is
running.
• Programmers can change a constant’s value at design
time.
• Types of constants include:
– Intrinsic—specific to an application
– User-defined—created by a programmer
– Conditional compiler—defined in the host application
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall.
11
Select a Data Type
• All variables and constants have a data type.
• The data type refers to the type of data the
variable or constant can hold.
• The data type defines how the data is stored in
the computer’s memory.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall.
12
Select a Data Type (Cont.)
• Common VBA data types include:
– Decimal—Value that contains decimal numbers
scaled by a power of 10
– Double—Double-precision floating-point numbers
with 14 digits of accuracy
– Integer—Whole numbers, no decimal points or
places behind the decimal
– String—Alphanumeric data including letters,
numbers, spaces, punctuation, and other characters
with a fixed length
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall.
13
Declaring Variables and Assigning
Values
• The declaration (Dim)
statement assigns:
– Name
– Data type
– Allocates memory to store a
value for the variable or constant
• Variables named with the
Public prefix can be used by
all procedures.
• The Scope specifies which
statements can access a
variable or constant.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall.
14
Creating an Input Box
• To obtain data from the user,
create an input box.
• The InputBox function
(InputBox) prompts the user to
enter a value.
• The MessageBox function
displays the resulting message.
• Concatenate is the process of
joining two or more text strings.
Use the ampersand character (&)
or plus sign (+) to concatenate,
or join, two values, such as
MsgBox "Last Name is: " &
strLastName shown in this
example.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall.
15
Convert Strings to Numeric Values
• User input using the
InputBox function is
returned to the
procedure as a text string
by default.
• Data for calculations
must be converted to
numeric data by using
the Val Function.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall.
16
Performing Calculations
• Create arithmetic
expressions using
variables and
mathematical operators.
• Order of precedence is
the order arithmetic
expressions are
performed.
• Combine multiple
operations with
parentheses ( ).
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall.
17
Format Output Results
• Use Format function to
format results of
calculations.
• Uses predefined formats
to change the appearance
of text.
• Examples:
– Currency
– Decimal
– Percent
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall.
18
Common Programming Structures
• Sequence structures execute statements in the
order they appear.
• Decision structures make comparisons between
values, variables, and/or constants and then execute
statements based on the comparison.
• Repetition structures repeat the execution of a
series of statements at run time.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall.
19
Using Decision Structures
• If…Then
statements
represent the
simplest type of
decision structure.
• If…Then…Else
statements test for
a condition and
specify one option
if the test evaluates
to True and
another if it
evaluates to False.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall.
20
Create a Select Case Statement
• The Select
Case
statement
compares an
expression or
a value to a set
of cases,
situations, or
ranges.
• A case is an
individual
condition to
test.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall.
21
Repetition Structures
• Repetition structures complete an operation a
specific number of times.
• Generally while a certain condition is true or until a
condition tests true
• Two common repetition structures
– For…Next loop
– Do…Loop
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall.
22
Using the For…Next Loop Statement
• The For…Next
statement repeats:
– Loop—set of statements
or a procedure
– A specific number of
times
• The counter keeps track
of the number of
iterations through the
loop.
• The step value is the
number the counter is
incremented or
decremented during each
iteration of the loop.
23
Using the Do…Loop Statement
• A Do…Loop
statement executes a
block of statements
while:
– A condition remains
true
– Or until a condition is
true
• Can be written with a
pretest or posttest loop
• A pretest performs
the logical test first and
executes the code
within the loop if the
test is true.
• A posttest executes
the code within the
loop one time and then
performs the logical
test to determine if the
loop iterates again.
24
Loop Until a Condition Becomes True
• Do Until
loop repeats
the iterations
of the loop
until the
specified
condition
evaluates to
True.
• Can be written
with a pretest
or posttest
loop
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall.
25