Lab 4 Introduction to VBA (I)

1
Lab 4 Introduction to VBA (I)

• ? Excel Object Model
• ? VBA Basics
• ? Exercise

2
Architecture
Interface
Model
Data
3
Why VBA?

• ? Excel modeling skills will make you valuable to
  virtually any company you choose to work for
• ? This course takes the process one giant step
  farther. It teaches you how to develop
  applications in Excel by using Excels
  programming language - VBA
• ? This will greatly magnify your value as an
  employee

4
What is VBA?

• ? Visual Basic for Applications (VBA) is
  Microsofts common application scripting
  language. - VB vs. VBA
• ? Included in all office xp applications
• (Excel, Word, Access, PowerPoint, etc.)
• ? To understand VBA, you have to be very clear
  about the object concepts

5
Object
6
Inheritance
7
Basic Object Concepts

• ? Object an entity that has unique properties
  and methods
• Property attributes of an object
• Method the thing you can do to an object
• Methods can also have qualifiers arguments,
  which indicates how a method is carried out
• Analogy Objects correspond to nouns, properties
  to adjectives, methods to verbs, and arguments to
  adverbs
• ? Collections
• Similar objects form a collection Worksheet -gt
  Worksheets
• A collection is also an object

8
Encapsulation Black Box

• Object
• Properties
• Methods

9
Car Object Model

• ? A Car has properties associated with it (your
  constants and variables)
• Style (coupe, sedan, hatchback, convertible)
• Color (red, black, pearl, titanium yellow)
• Size (Economy, compact, full, mid, luxury)
• ? A Car has methods associated with it (your
  functions and procedures)
• Accelerate, Brake, Park, Crash
• ? Arguments can be associated with verbs
• At what mileage a car is crashed
• ? One of the most important things about a car
  is an engine. However, an engine isnt simple
  it has lots of variables and lots of functions.
  To deal with this, objects can contain other
  objects. This creates one form of Object
  Hierarchy

10
Back to Excel

• ? Excel is just like a car
• ? Excel object examples include
• - Workbook, Worksheet, chart, range
• ? To organize everything, the programmers built
  Excel with an Object Hierarchy - well see how to
  use this hierarchy later
• ? Object an entity that has unique properties
  and methods
• Property attributes of an object, e.g. Value
• Method the thing you can do to an object, e.g.
  ClearContents
• Arguments how a method is carried out, e.g.
  Destination argument for Copy

11
Excel Object Hierarchy
Application
AddIns
Workbooks
CommandBars
Worksheets
Charts
Names
Range
PivotTable
- Please see p. 10, Fig. 2.2 of the text, or see
Online Help for the complete list of Excel
objects Type in key word Object
12
Refer to an Excel Object (1)

• ? For a contained or member object, specify its
  position in the object hierarchy using a . as
  separator between the container and member
• E.g. Application.Workbooks(IFSM425.xls)
• How do you refer to the cell A1 in the worksheet
  Sheet1 of Workbook Book1.xls?
• Answer Application.Workbooks(Book1.xls).Worksh
  eets(Sheet1). Range(A1)
• ? Use active object to refer to the current
  object (where your curser is)
• When Book1 is currently the active workbook
  ActiveWorkbook.ActiveSheet or ActiveSheet
• If only one workbook and one worksheet open
• Workbooks(1).Sheets(1)

13
Refer to an Excel Object (2)

• ? Refer to a property of an object
• Combine the object with its property, separated
  by a .
• E.g. set the value of cell A1 on sheet1 to 2
• Worksheets(Sheet1).Range(A1).Value 2
• ? Refer to a method of an object
• Specify a method by combining the object with it,
  separated by a .
• E.g. ClearContents and Clear are methods of a
  Range object Range(A1).ClearContents ' clear
  contents only
• Range(A1).Clear ' clear the format as well

14
VBA Basics

• ? Visual Basic Editor (VBE)
• ? VBA Subroutines
• ? Variable Declaration
• ? Some useful VBA tips
• ? InputBox MsgBox Functions

15
Visual Basic Editor (VBE)

• You cant run the VBE separately Excel must be
  running in order for VBE to run.
• Three ways to switch to VBE in Excel
• Press AltF11
• Select Tools-gtMacro-gtVisual Basic Editor
• Click on the VBE Button on the toolbar

16
VBE Windows

• Project Explorer Window displays all open
  workbooks. Each workbook is a project.
• Properties Window lists a set of properties of
  the selected object
• A code window for every item in a project
• Immediate window (CtrlG) Evaluate a statement
  immediately without having to create a
  procedure
• Object Browser lists objects and their
  corresponding properties and methods (Press F2)

17
Immediate Window Practice

• Type in the Immediate Window the following codes
  and see what happens
• ?Application.Name
• ?Application.Version
• ?Format(Date, "long date")
• ?Format(86099.57654, ",0.00")
• ?Time
• InputBox "Your name?", "User name", " "
• MsgBox "Hello!", ,"Say Hello"

18
Add/Remove a VBA Module

• You need at least one module in a project to hold
  the VBA codes you write
• To add a new VBA module to a project Select the
  project, then go Insert -gtModule, or right click
  the project then Insert-gt Module
• To remove a moduleSelect the module, then go
  File -gt Remove, or right click the modules name
  and remove it

19
Code Window

• A code window can hold four types of code
• Sub procedure a set of instructions that
  performs some action.
• Function procedures. a set of instructions that
  returns a single value or an array
• Property procedures special procedures used in
  class modules you can ignore this one
• Declarations information about a variable that
  you provide to VBA.

20
VBA Subroutines Chpt 4 to pg 39

• Sub subroutine, the logical section of code
  that performs a certain task
• Subs, macros, procedures (all the same thing)
• Collection of subs program
• E.g.,
• Sub AddSum()
• Dim Sum As Integer Variable
  Declaration
• Sum 1 1 Statements
• MsgBox "The answer is " Sum
• End Sub

21
Enter Execute VBA Code

• Enter VBA code in three ways
• Type it from your keyboard
• Use macro recorder in EXCEL -gt generate code
  automatically we will discuss this more next
  class
• Copy the code from another module and paste
• Execute a procedure in three ways
• Make sure the cursor is anywhere within your
  sub, then
• Press F5
• Select Run-gtRun Sub/UserForm
• Click the ? button (Run Sub/User Form Button) in
  VBE

22
Variable Declaration (1) (p29)

• You should always declare your variables
• Bring up Option Explicit to force you to declare
  variables. In VBE, go to Tools -gt Options -gt
  Require Variable Declarations)
• Dim VariableName as
• String
• Integer
• Long
• Boolean
• Single
• Double
• Currency
• Variant
• Object (or the specific object name, such as
  Range)
• E.g., Dim i as Integer, j as Integer, UserName
  as String

23
Variable Declaration (2)

• Object Variable Example
• Define variable R as a Range object
• Dim R as Range
• Set R ActiveWorkbook.Worksheets(Sheet1).Range(
  A1)
• Note the Set statement is only used for
  object variables
• Now, instead of writing
• ActiveWorkbook.Worksheets(Sheet1).Range(A1).F
  ont.Size 14
• you can write
• R.Font.Size 14

24
Two VBA Built-In Functions

• Two of the most common tasks in VBA programs are
  to get inputs from users and to display messages
  or results in some way
• InputBox function
• MsgBox function

25
InputBox Function

• Displays a predefined dialog box to prompt a user
  for input
• SyntaxInputBox(Prompt, title, default,
  xpos, ypos, helpfile)
• Items in the square brackets are optional.
• ExampleSub GetName( )
• Dim Name AS String
• Name InputBox("Please enter your Name",
  _ "User Window", " ")
• MsgBox "Hello, " Name "!, , Say Hello
• End Sub

26
Some Useful Symbols

• Use a space underscore _ for Line
  continuation in VBA for lengthy codes over
  several lines
• E.g. InputBox "Type your name", _"Users name",
  " "
• Concatenation character is the ampersand (). It
  is surrounded by a space from both sides.
• E.g., MsgBox Hello FirstName !
• Assume the FirstName variable contains the value
  of Mary, then the message box will be Hello
  Mary!
• Single quote used to add comments for your code
• To add a comment, start a statement with a single
  quote e.g., Range(A1).Value March Sales '
  This is the title
• Not too few, not too many
• The best means of documentation is the liberal
  use of comments Use your discretion on what
  really needs to be commented
• Useful Comment and Uncomment block tools in
  VBA

27
MsgBox Function

• Displays a predefined message box to list
  information for the user
• SyntaxMsgBox (Prompt, Buttons, title,
  helpfile, context)
• Prompt (required) the message that will be
  displayed
• Buttons a value showing which buttons or icons
  appear. (e.g. Use built-in constants such as
  vbYesNo, vbInformation, and vbExclamation)
• Title The text in the title bar of the message
  box
• Example
• Sub SayHello()
• Dim Msg As String, Ans As String
• Msg "Are you enrolled in IFSM 425?"
• Ans MsgBox(Msg, vbYesNo)
• If Ans vbNo Then
• MsgBox "Oh, never mind!, , Oops
• Else MsgBox "Please join my group!",
  vbExclamation, "Welcome!"
• End If
• End Sub

28
Useful Tips

• VBA adjusts the case of the letters for keywords,
  properties, and methods.
• VBA inserts spaces between operators.
• E.g. It automatically converts Ans12 to Ans
  1 2
• When to use () for MsgBox and InputBox
• parentheses are required when the result is
  captured in a variable or used in some way they
  are optional (and are usually omitted) when no
  result is being captured or used.

29
Assignment

• Write a program, and store it in a file called
  TravelExpenses.xls, that does the following
• it asks for a persons first name and stores is
  it in FName,
• it asks for a persons last name and stores it in
  LName,
• it asks for the number of miles the person
  traveled on a recent trip and stores it in
  NMiles,
• it asks for the average miles per gallon the
  person got on the trip and stores it in MPG,
• it asks for the average price per gallon paid for
  gas on the trip and stores it in AvgPrice,
• it calculates the cost of the trip and stores it
  in TripCost, (TripCost NMiles/MPGAvgPrice) and
  
• it displays a message such as
• Bob Jones traveled 800 miles, got 31.3 miles
  per gallon on average, paid 1.46 per gallon on
  average, and paid a total of 37.32 for gas.
• Make sure there is an Option Explicit line at the
  top of the module and that you declare all of
  your variables appropriately. Check text for
  ideas about formatting result in .

30
Exercise Help

• Open the file
• Get into the VBE
• Add a module
• Start a sub
• Type the code
• Dont forget declaring variables first
• Use InputBox function to give values to your
  variables
• Do the simple calculation
• Use to connect strings
• Use MsgBox function to display message
• Run the program from VBE
• Troubleshooting if necessary