Visual Basic for Applications

1
Visual Basic for Applications

• Its all about objects and automation
• Focusing on MS Excel
• ATiB Workshop Fall 2001

2
What is VBA?
Its the glue!

• Common programming component shared among MS
  Office applications
• Also shared with Visual Basic 4.0 and later
• Allows creation and manipulation of application
  objects such as spreadsheets, databases,
  documents, mail, projects
• Facilitates application interoperability

3
What does VBA allow you to do?

• Create applications based on MS Office products
• Manipulate objects in object model of various
  products
• Customize way product appears to others
• Leverage capabilities of pre-built objects in
  various applications
• IT ALLOWS YOU TO DO STUFF IN AUTOMATED FASHION

Limited only by your imagination
4
Excel VBA specifically

• Create user defined functions that can be used
  like any other spreadsheet function
• UDFExamples_Isken.xls
• Automate tedious, routine tasks
• BlueBorder
• Create full-blown applications
• Portfolio Optimization / Web Query example
• Distribute applications to others

5
VBA for Modelers by Albright

• First half is very nice Excel VBA primer
• Many concepts transfer to Access and other
  applications
• Of course, object model is different
• Learning any one flavor of VBA makes learning
  others much easier

6
Why Learn to Program in VBA?

• Algorithmic thinking important to business
  analysis
• Business solutions often require some programming
• automation
• user defined functions
• Simple macro languages a thing of the past
• Huge productivity gains possible
• Ability to code is a very valuable skill
• Its fun

7
Whats needed from students

• No prior programming experience required
• A logical mindset
• Willingness to experiment and learn
• Plenty of practice
• Perseverance

8
VBAGeneral ProgrammingObject Manipulation
Excel VBA Applications
General Programming Concepts
Excel Object Model
9
Recording Macros

• Useful for learning VBA
• Really useful for learning details of object
  model
• A way to start a program
• To use
• start recorder (Tools-Macro-Record New Macro)
• do stuff
• stop recorder (Push the stop button)
• go look/edit code in VBE
• Cant record logic
• Recall the Blue Border example

10
Programming with VBA

• Figure out what you logically want to accomplish
• Pseudo-code and/or Flow chart
• Figure out which objects you need to
  create/modify/access.
• Figure out how to use those objects properties
  and methods to do what you want to do.
• Hunting through help, common sense, object
  browser, record a macro
• READ and practice from VBA for Modelers

11
Visual Basic Editor
Chaps 3, 13

• Project Explorer
• Code Modules
• Immediate Window
• Object Browser
• Locals
• Watch

• Run, continue, reset
• Single step into, over out
• Breaking out
• Using Breakpoints
• Auto List Members
• Auto Quick Info

12
Procedures
Chaps 4, 10

• Subroutines
• Do stuff
• DOESNT return a value
• Takes any number of arguments

• Functions
• Do stuff
• RETURNS a value
• Takes any number of arguments

13
Variables
Sec. 4.3

• Temporary storage of values
• Like algebra
• Can do things like xx1
• Declare variables with Dim
• pick data type for the variable
• first thing you do in a program
• Option Explicit
• forces explicit variable declarations

14
Some Data Types
Sec. 4.3

• String for text like Bob Smith
• Integer integers in 32768 to 32768
• Long for bigger integers
• Boolean only True (-1) or False (0)
• Double numbers with decimals
• Currency monetary values
• Date for dates and times
• Object specific versions for each object type
• Variant let VBA decide how to deal with

15
Simple Communication with User
Sec. 4.4-4.6

• MsgBox show user message, get button click
  response
• InputBox get a single value from the user

16
Learn to use Online Help
17
Control Logic - Condition If Then Else
Endif
If some condition Then a statement to be done if
the condition is True OR If some condition Then
a bunch of statements to be done if the
condition is True Else a bunch of
statements to be done if the condition is False
End If
Sec. 7.3
18
Control Logic - Looping ForNext, Do WhileLoop
For counter start To stop bunch of
statements to be repeated Next counter
Sec. 7.5-7.7
Do While condition bunch of statements to
be repeated Loop
19
The Object Model an analogy
Chaps 2,6,8
We have object called Car
Each Car is part of the collection Cars
Each Car has properties that describe it such as
make, model, color, price, age, purpose, etc.
Each Car has methods that control it such as
accelerate, stop, turn left, turn right,, etc.
20
Object Model for Cars

• Hierarchical
• Collections are plural
• Each object has its own specific set of
  properties and methods

Cars (Car)
Wheels (Wheel)
Horn
Hood
HoodOrnament
21
Object Property Values

• Car Object is a template for creating specific
  car instances
• Each car can have its own set of values for each
  property

NOT a real program
Set property values
Use Drive method
Check a property value and use a method if value
meets condition
22
Intro to (MS Excel) Objects

• Object a single unit containing code and data
  that serve a common purpose
• Properties and Methods
• Appearance and behavior control
• SomeObject.SomeProperty
• SomeObject.SomeMethod method arguments
• Collections of Objects
• the Collection itself is an object
• collections are plural in name

(noun)
(adjective)
(verb)
(adverbs)
23
Excels Worksheet Object
In other words, VBA lets you programmatically
manipulate almost any object in a supporting
application.
24
Accessing specific objects in collections and
down hierarchy

• By index number
• By name
• Down hierarchy
• Worksheets(BreakEvenModel).Range(FCost).Value

Worksheets(1)
Worksheets(BreakEvenModel)
Dots separate objects in hierarchy
Can use Immediate Window to learn about
referencing objects. Lets look at
FirstProgramFinished_Isken.xls
25
A Few Range Properties
P 65-67

• Address
• Cells a strange but useful property
• Font
• Rows
• Columns
• Value
• Name

26
A Few Range Methods
P 65-67

• Clear, ClearContents
• Copy, Cut, PasteSpecial
• Select
• Sort
• Columns
• Value
• Calculate

27
Specifying Ranges w/VBAUsing the Cells
property/object
Sec 6.4
Range("C5E15").Cells(4,2).Value 500
28
Specifying Ranges w/VBAUsing the Cells
property/object
Sec 6.4
Range(B5B14").Cells(3).Value 500
29
Specifying Ranges w/VBAUsing the Offset property
Sec 6.4
Range(A5").Offset(2,3).Value 500
2 rows
3 columns
30
Creating User FormsCh 12 in VBA for Modelers
Open PracticeForm_blank.xls
Blank form
Get to know all the controls
31
Each control has a slew of properties. The form
itself also has a bunch of properties. Select the
form to see them.
32
Get to this stage, see p193-194
Oops, forgot list box
33
Leszynski Naming Convention
tagBaseName

• Makes object names informative
• Standardized vocabulary for teams
• Improved ability to work with objects
• Sorting, self-documenting, find-replace

34
Names for Common ControlsfrmCustomer
35
Now, get it to here, p195-196.
Note how list box based on Customers range
36
Now time for Event Code

• Need code to respond to events like users pushing
  buttons
• Open PracticeForm_Isken.xls
• Tools-Macro-Visual Basic Editor (ALT-F11)
• Lets examine the code

37
Three subs behind the form

• CancelButton_Click()
• OKButton_Click()
• UserForm_Initialize()
• Note that this sub is always called this
  regardless of the form name (go figure)

38
More code
39
Main calling sub and some Public variables