Visual Basic for Applications

1
Visual Basic for Applications

• Its all about objects and automation
• Focusing on MS Excel

2
Preview

• What is Visual Basic for Applications
• VBA for Modelers by Albright
• Why learn to program? Why VBA?
• Basics of Programming with VBA
• The Excel Object Model

3
Computer Programming

• Tell the computer exactly what you want it to do
  and how to do it
• A very detailed business process model
• Flow chart the logic of the business process to
  be programmed
• The computer does what you tell it to do
• this is both good and bad

4
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

5
What does VBA allow you to do?

• Create applications based on programmable
  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 AN AUTOMATED FASHION

Limited only by your imagination
6
VBA for Modelers by Albright

• First half is very nice Excel VBA primer
• Many concepts transfer to Access and other Office
  applications
• Of course, object model is different
• Well do a little Access VBA later in term (Excel
  front end Access database)

7
More Resources

• The Spreadsheet Page Walkenbach
• The EXCEL-L Developers ListServ
• See Resource Center section of course web site
  for tons of links to Excel VBA related sites

8
Why Learn to Program in VBA?

• Algorithmic thinking important to business
  analysis
• Business solutions often require some programming
• Appreciation of programming by managers
• Huge productivity gains possible
• Ability to code is a very valuable skill
• Its fun

9
Whats needed from you

• No prior programming experience required
• A logical mindset
• Willingness to experiment and learn
• Plenty of practice
• Perseverance
• It will be frustrating, exacting, challenging,
  and rewarding

10
Common uses of Excel VBA

• Automate, tedious, repetitive tasks
• Examples BlueBorder, TheShader, MIS646 Project
  Grading template, ConcatRange
• Create user defined functions
• Example 4DigitMilitaryTime, SSN de-hyphenator,
  ConcatRange
• Create add-ins
• Examples _at_Risk, MegaStat, Solver, Walkenbachs
  Power Utility Pak, YASAI (free, open source
  Monte-Carlo simulation)
• Create spreadsheet based applications
• Example Scheduling, Eureka, 446/646 project
  examples, The Portfolio Optimizer

11
VBAGeneral ProgrammingObject Manipulation
Excel VBA Applications
General Programming Concepts
Excel Object Model
12
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

13
Where does code live in Excel and Access (and
Word, etc)?

• Code behind forms
• Code related to controls on forms
• Respond to events (e.g. button click)
• Code Modules
• General subroutines and functions
• Not specific to a form
• Visual Basic Editor for both

14
Structured Programming Concepts

• Divide code into independent procedures
• Each with its own purpose
• Procedures related hierarchically
• They talk through a list of arguments or
  parameters
• Logic
• sequence
• selection
• iteration

15
(No Transcript)
16
A First Program

• Download FirstProgram.xls to your PC or open
  yours if you have it
• Open and Save As FirstProgram_YourName.xls
• Lets explore the program and the Visual Basic
  Editor (VBE)

17
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

18
Subroutine declaration
Conditional logic Check if cell SalesCutoff.
If so, increment the counter
Inner Loop
19
Customizing the VBE
Uncheck to turn off compile error dialogs
Check this to force Option Explicit to be added
to your modules which then forces you to Dim all
of your variables.
20
Variables
Sec. 5.3

• Temporary storage of values (recall from
  algebra?)
• xx1
• AvgCost TotalCost/NumItems
• Declare variables with Dim
• pick data type for the variable
• Reserves memory for the variable
• Example Dim NumItems As Integer
• Dim dimension
• Option Explicit
• Placed at top of module
• forces explicit variable declarations

21
Some VBA Data Types
22
Building Blocks of Expressions
(1) Relational and Arithmetic Operators
(2) Logical Operators
(3) Literals
23
Useful String functions and other constructs

• Left, Right, Mid get parts of a string
• Len get the length of a string
• Trim get rid of trailing and leading spaces in
  a string
• Format converts numeric to string with user
  specified display format
• - concatenation operator
• Val converts string to number
• Line continuation _
• There are a zillion useful built in functions in
  VBA. Learn how to find and use some of them. Here
  are some great links
• http//www.mvps.org/dmcritchie/excel/strings.htm

24
The Averaging Program

• Lets explore VBA program to
• Let user enter a series of numbers, one at a time
  into a simple data input form
• When the user is done entering numbers, calculate
  the average of the numbers entered
• Display the calculated average to the user
• Well explore in Excel
• More about the Visual Basic Editor
• Lets figure out how the code works
• Make some program enhancements

25
The Averaging Program

• Lets demo it
• Many issues to deal with
• flow of program logic
• what intermediate values will need to be tracked?
• how will we know when user is done?
• what kinds of things might the user enter and how
  to deal with them?
• Lets look at flow chart and the basic version of
  the program
• Lets make some modifications to this basic
  version of the program
• see FlowChart-Averager.ppt

26
Simple Communication with User
Sec. 5.5-5.6

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

27
Learn to use Online Help
28
Built in Constants
Sec. 5.4

• Visual Basic, Excel, and other MS Office products
  make heavy use of built in constants
• Integer valued variables that are part of a set
  of related integer variables called enumerations
• Prefaced by vb (VisualBasic), xl (Excel), or mso
  (MS Office)
• Examples
• vbBlack, vbBlue, vbCyan, vbGreen, vbMagenta,
  vbRed, vbWhite, and vbYellow are 8 VB constants
  for font color
• xlDown, xlToRight, xlToLeft, xlUp are Excel
  constants for working with cell selecting
• Makes code more readable
• Avoids having to remember arcane codes
• Allows clever addition of constants (youll see)

29
Control Logic - Condition If Then Else
Endif
Sec. 7.3
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
Single line
Block style (preferred)
30
Control Logic - Condition If Then Else
Endif
If dblValue non-negative OR If dblValue MsgBox Must enter non-negative Else
dblSummation dblSummation dblValue intSample
Size intSampleSize 1 End If
Go back and look at FirstProgramFinished-Isken.xls
See CountHighSales_TitleBar() for if then else
and for enhancing MsgBox
31
Control Logic - Looping ForNext
For counter start To stop bunch of
statements to be repeated Next counter
Sec. 7.5-7.7
For intMonth 1 To 12 TotalCost TotalCost
Cost(intMonth) Next intMonth
32
Control Logic - Looping Do WhileLoop
Do While condition bunch of statements to
be repeated Loop
Sec. 7.5-7.7
Do While blnStillEnteringNumbers
intNumEntered intNumEntered 1 BLAH BLAH
BLAH ..... Loop
Look at FirstProgram for example of For Next How
could you modify to use Do While Loop instead?
33
VB stuff you must learn quicklyRead Chapter 5
Carefully!!!

• Declaring functions and subroutines
• Declaring variables and data types
• Assigning values to variables and doing
  calculations
• Arithmetic operators and VB functions
• Conditional Logic If..Then..Else, Select Case
• Iteration Do..Loop, For..Next
• Simple communication with user via InputBox() and
  MsgBox() functions
• Logically designing programs consisting of
  multiple procedures working together to
  accomplish some task

34
Programs as Recipes

• Name of recipe
• List of ingredients
• bread
• peanut butter
• jelly
• Steps to create the final product

• Name of procedure
• Declare variables
• Dim intCount as Integer
• Dim dblCost as Double
• Step by step instructions you want the computer
  to do

35
Main Program and SubroutinesDivide programs into
independent proceduresTalk via passed
parameters
Filename
Main Program
Results
Values
Results
Values
Read data file
Output results
2
3
1
Do calculations
36
Procedures
Chaps 5, 10

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

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

37
User Defined Functions

• Write your own and use like any other Excel
  function
• Useful for encapsulating complex, multi-part
  formulas
• Examples 4 digit military time and SSNs
• Download SimpleSubsAndFunctions.xls,
  UDFExamples.xls and NastyMainFrameTimeStamp.xls

38
Some programming style tips

• Use meaningful variable names
• See p51 for discussion of variable naming
  convention
• Indent
• Use lots of comments
• Create End If, Loop, End With, Next right after
  you create If, Do While, With, For so you dont
  forget
• Use white space

39
The Object Model an analogy
Chaps 2,4,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.
40
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
41
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

SET property values
I am NOT a real program
Use Drive method
GET a property value and use a method if value
meets condition
42
VBA Object Models

• You can view using Office applications as
  manipulation of various things, or objects
• Excel objects workbooks, sheets, ranges, many
  more
• Word objects documents, headers and footers,
  dictionaries, many more
• Access objects forms, reports, macros, queries,
  many more
• Outlook objects messages, tasks, etc.
• Objects of the same type are often grouped into
  collections
• Each Workbook object is part of the Workbooks
  collection
• Objects have attributes that describe them
• In VBA, these are called properties, and they
  take specific values
• Example the Excel Worksheet object has a
  property called Name that is a text value
  corresponding to the name on the worksheet tab
• You do things with or to an object
• In VBA, we use methods to do this
• Example in Excel, we use the Paste method with
  the Selection object to paste a selection
  somewhere
• Methods may take additional qualifiers to specify
  how the method is to be performed
• Example the PasteSpecial method needs qualifiers
  to specify what should be pasted

43
Objects and Events

• Many objects have defined events that get fired
• When a workbook is opened, its Open event gets
  fired
• Objects can respond to events that get fired
• We can tell a workbook to do certain things every
  time it is opened (e.g. activating a certain
  worksheet)
• We will write event handler code to specify what
  should happen to various objects when various
  events get fired

44
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
• Example
• Selection.Font.Bold True
• Selection.Font.ColorIndex 3
• Selection.PasteSpecial PastexlPasteFormats

(noun)
(adjective)
(verb)
(adverbs)
45
Recording Macros - reminder

• 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
• Lets look at Recording.xls

46
The With construct (5.11)

• Shortcut to working with many properties of one
  object
• Example

Excel has numerous built in constants which begin
with the characters xl
With Selection.Font .Name "Antique
Olive" .Size 14 .Strikethrough
False .Superscript False
.Subscript False .OutlineFont False
.Shadow False .Underline
xlUnderlineStyleNone .ColorIndex
xlAutomatic End With
Open Recording.xls and lets record some macros
and examine the code. Its a good way to start
becoming familiar with objects
47
Collections of Objects

• Worksheets collection is most common
• A collection of Worksheet objects
• the Collection itself is an object
• collections are usually plural in name
• Workbooks, PivotTables, Names, FormatConditions,
• We can reference individual objects in a
  collection by number or name
• By index number Worksheets(1)
• By name Worksheets(BreakEvenModel)
• Use Count property to see how many items are in a
  collection
• n Worksheets.Count

48
Excels Worksheet Object
In other words, VBA lets you programmatically
manipulate almost any object in a supporting
application.
49
Object Models are Hierarchical

• Objects are related to other objects in a
  hierarchical fashion
• Higher level objects often consist of many lower
  level objects
• Workbooks made up of multiple worksheets lets
  reference a single sheet in BreakEven.xls
• Workbook(BreakEven.xls).Worksheets(BreakEvenMod
  el)
• Dots separate objects in hierarchy
• Worksheets(BreakEvenModel).Range(FCost).Value

Dots separate objects and their properties as well
50
The very important Range object (Ch 6)

• Represents a cell, a row, a column, a selection
  of cells containing one or more contiguous blocks
  of cells, or a 3-D range.
• Many different ways to retrieve values from, or
  to put values into, range objects
• Excel programming relies heavily on manipulating
  range objects

51
Learning about Excel Objects

• ExcelObjectExamples.xls (Downloads)
• I created this file to demonstrate a host of
  common manipulations of Workbooks, Worksheets,
  and Range objects
• Ranges.xls (Downloads)
• C. Albright, the author of our text, created this
  file to demonstrate a host of common
  manipulations of Range objects

52
A Few Range Properties
P 83-85

• Address
• Cells a strange but useful property
• Font both an object and a property
• Formula
• Row, Rows
• Column, Columns
• Value
• Name

53
A Few Range Methods
P 85-86

• Activate
• Clear, ClearContents
• Copy, Cut, Delete, PasteSpecial
• Calculate
• Find, Select, Insert
• Sort
• Goalseek

54
Specifying Ranges w/VBA(1) Using range name
directly or (2) string variable
Dim SalesName As String SalesName
"Sales Range(SalesName) 500
Range(Sales) 500
Sec 6.4
55
Specifying Ranges w/VBAUsing the Cells
property/object
Sec 6.4
Range("C5E15").Cells(4,2).Value 500
56
Specifying Ranges w/VBAUsing the Cells
property/object
Sec 6.4
Range(B5B14").Cells(3).Value 500
57
Specifying Ranges w/VBAUsing the Offset property
Sec 6.4
Range(A5").Offset(2,3).Value 500
2 rows
3 columns
58
Specifying Ranges w/VBAUsing a Range object
variable
Sec 6.4
Range is a specific object type
Dim SalesRange As Range Set SalesRange
Range(Sales) SalesRange.Font.Bold True
Note use of Set when setting value of an object
59
Download BreakEven-W07-Problem.xls file from
course web. Name your file BreakEven-lastname.xls
BreakEven
Your assignment Get this program working right
now. Theres some code already in the file. What
we want to do is step through the range named
CostScenarios, taking each value, setting the
range VCost equal to that value. That updates the
range (a single cell) named Profit. Then we want
to put profit in the range MyProfit in the same
row as the corresponding cost in CostScenarios.
We will also compute break even volume in Col E
using Goal Seek.
See BreakEven-W07-Demo.xls for how it should work
This program mimics the functionality of a Data
Table.
60
Array Variables (Ch 9)

• Arrays are just collections of related variables
• They have a type assigned upon declaration
• Also have a size the of elements the array can
  store
• Example Assume we have a variable called Costs
  that takes on a different value for each month
• Dim Costs(1 to 12) As Double
• Now we can reference individual costs by
  including the index desired
• Costs(6) 500
• Arrays can have multiple dimensions
• Example Assume we have costs for 10 different
  stores for each of the 12 months
• Dim Costs(1 to 10,1 to 12) As Double
• Costs(1,6) 500 Sets the cost for store 1 in
  month 6 to 500
• Think of first dimension as the row and the
  second as the column