Transcript Visual Basic for Applications (VBA)

Adding Automated Functionality
to Office Applications
Macros
• A macro is a
recorded
and
or
actions.
• With a single keystroke, users can play back these
activities at a much faster speed than those same
actions can be performed manually.
• Macros can help
the risk of error that typically occur when
performing repetitive tasks.
and
Two different ways macros are created
• K
Macros
– Perform a sequence of operations using keystrokes
and the mouse and the application records what you
are doing
• W
programming
accomplish a task.
to
• Both options use Visual Basic for Applications
(VBA). The difference is how the VBA code is
created
Customize Ribbon ------ Select DEVELOPER as one of the main tabs
File tab
Select OPTIONS
Recording 2 Macros
• First macro: Sum all columns
• Second macro
– Sum all columns
– Paste values into a Copy row
– Deletes answer row (from first step)
Recording Macros
• Benefits
– Q
way to add automated functionality to your
spreadsheet. The macro recorder simply records the keystrokes that
you take or mouse actions that you perform
– Code is guaranteed to be free from syntax/coding errors
– Can serve as a
if you are inexperienced.
• Weaknesses
– The macro recorder does not write the most efficient code. It records actions
and commands you might not need or want.
– May have to record macro several times due to making errors in the recording
process. More chances for making errors.
– If changes are made to the document (add column or row), the macro may not
function as intended.
• Not as robust
– Works well for
, but sometimes you want advanced
functionality that recorded macros can’t make possible (button tasks)
Visual Basic for Applications
(VBA)
Write programming code to
accomplish a task.
Visual Basic for Applications (VBA)
• Visual Basic for Applications (VBA) is a programming
language
– VBA is a
– VBA requires a
of
• Excel, Access, PowerPoint, Word
– You can customize an Excel workbook or enhance Access
database objects.
• Create your own custom functions
• Perform calculations using variables and constants
• Create buttons that initiate process tasks (clicking button results in
something happening)
• Display messages to users
• Automate tasks rather than repeatedly doing something
• Procedures (also known as sub procedures or event procedures) are programming instructions
that
.
• Words in green are
(not executable): apostrophe ‘comment
• Blue text signifies VBA keywords (reserved words) that have special meaning
• Black text is regular text you have added
• Indenting segments of code improves readability.
• VBA is an
programming language:
objects are created and then methods/actions are taken to
manipulate to those objects.
• When a program is running, it needs to be able to accept
input, perform calculations and then display output.
• I
and
boxes (located
on a form or in an
application) are used
to obtain input for
users while a
program is running.
• Programs use constants and variables
– Constants store values that
– Variables store values that can
program is running.
while the
• Data types
– Use
for alphanumeric data (letters, numbers, symbols)
– Use one of the numeric data types if the data will be
used in a
• Typically, a computer program executes instructions
sequentially (
). This is
called a sequence structure.
• However, there are two notable exceptions
–D
Structure
• A comparison is made, and based upon the results of the
comparison, the program executes statements in a certain order
– IF statements
– IF…Then…Else statements
– Select Case
–R
(including ElseIf)
Structure
• Repeats the execution of certain statements while a certain
condition is true OR until a condition is true
– For…Next
– Loops
» Do While
» Do Until
• I
statements specify
what should
happen if a
condition is true,
but there is no
alternative if the
statement is false.
• IF…Then…Else
statements specify
what should
happen if a
condition is true
and what should
happen if a
condition is false.
• Just like our
Select Case
• Select Case allows you to test one value
, and based upon the category
something is in, it returns the appropriate result.
– This is much easier to create and understand than
nesting Ifs inside other Ifs.
Repetition
Structures
• R
the execution
of statements while a
certain condition is true OR
until a condition is true
• For….
statements repeat
execution a
• D
repeat
steps until something
has happened or while
something has not
happened.
• Do until or Do while