Transcript Visual Basic for Applications (VBA)

Adding Automated Functionality
to Office Applications
Macros
• A macro is a series of written instructions or
recorded keystrokes and mouse 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 eliminate wasted time and the
risk of error that typically occur when performing
repetitive tasks.
Two different ways macros are created
• Keystroke Macros
– Perform a sequence of operations using keystrokes
and the mouse and the application records what you
are doing
• Writing programming code to accomplish a task.
• 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
– Quick and easy 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 starting point 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 basic tasks, 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 subset of Visual Basic
– VBA requires a host application
• 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 tell the computer what to do.
• Words in green are comments (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 object-oriented 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.
• Input boxes and
text 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 never change
– Variables store values that can vary or change while the
program is running.
• Data types
– Use string for alphanumeric data (letters, numbers, symbols)
– Use one of the numeric data
types if the data
will be used in a
calculation.
• Typically, a computer program executes instructions
sequentially (in the order they are written). This is
called a sequence structure.
• However, there are two notable exceptions
– Decision 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
(including ElseIf)
– Repetition 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
• IF…Then
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 IFs
in Excel.
Select Case
• Select Case allows you to test one value against
multiple conditions, 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
• Repeats the execution of
statements while a certain
condition is true OR until
a condition is true
• For….Next statements
repeat execution a
certain number of
times.
• Do Loops repeat steps
until something has
happened or while
something has not
happened.
• Do until or Do
while