Lab 4 introduction to vba i
This presentation is the property of its rightful owner.
Sponsored Links
1 / 30

Lab 4 Introduction to VBA (I) PowerPoint PPT Presentation


  • 34 Views
  • Uploaded on
  • Presentation posted in: General

Lab 4 Introduction to VBA (I). ► Excel Object Model ► VBA Basics ► Exercise. Architecture. Interface. Model. Data. Why VBA?. ● Excel modeling skills will make you valuable to virtually any company you choose to work for

Download Presentation

Lab 4 Introduction to VBA (I)

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Lab 4 introduction to vba i

Lab 4 Introduction to VBA (I)

► Excel Object Model

► VBA Basics

► Exercise


Architecture

Architecture

Interface

Model

Data


Why vba

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 Excel’s programming language - VBA

● This will greatly magnify your value as an employee


What is vba

What is VBA?

●Visual Basic for Applications (VBA) is Microsoft’s 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


Object

Object


Inheritance

Inheritance


Basic object concepts

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 -> Worksheets

  • A collection is also an object


Encapsulation black box

Encapsulation & Black Box

  • Object

  • Properties

  • Methods


Car object model

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 isn’t 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


Back to excel

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 - we’ll 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


Excel object hierarchy

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” …


Refer to an excel object 1

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”).Worksheets(“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)


Refer to an excel object 2

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


Vba basics

VBA Basics

Visual Basic Editor (VBE)

 VBA Subroutines

 Variable Declaration

Some useful VBA tips

InputBox & MsgBox Functions


Visual basic editor vbe

Visual Basic Editor (VBE)

  • You can’t run the VBE separately; Excel must be running in order for VBE to run.

  • Three ways to switch to VBE in Excel:

    • Press Alt+F11

    • Select Tools->Macro->Visual Basic Editor

    • Click on the VBE Button on the toolbar


Vbe windows

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 (Ctrl+G): Evaluate a statement “immediately” without having to create a procedure

  • Object Browser – lists objects and their corresponding properties and methods (Press F2)


Immediate window practice

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"


Add remove a vba module

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 ->Module, or right click the project then Insert-> Module

    • To remove a module:Select the module, then go File -> Remove, or right click the module’s name and remove it


Code window

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.


Vba subroutines chpt 4 to pg 39

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


Enter execute vba code

Enter & Execute VBA Code

  • Enter VBA code in three ways

    • Type it from your keyboard

    • Use macro recorder in EXCEL -> 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->Run Sub/UserForm

    • Click the ► button (Run Sub/User Form Button) in VBE


Variable declaration 1 p29

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 ->Options -> 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


Variable declaration 2

Variable Declaration (2)

Object Variable Example

  • Define variable R as a Range object:

    Dim R as Range

    SetR = ActiveWorkbook.Worksheets(“Sheet1”).Range(“A1”)

    Note: the ‘Set’ statement is only used for object variables

  • Now, instead of writing

    ActiveWorkbook.Worksheets(“Sheet1”).Range(“A1”).Font.Size = 14

    you can write

    R.Font.Size = 14


Two vba built in functions

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


Inputbox function

InputBox Function

  • Displays a predefined dialog box to prompt a user for input

  • Syntax:InputBox(Prompt[, title][, default][, xpos][, ypos][, helpfile])

    • Items in the square brackets are optional.

  • Example:Sub GetName( )

    Dim Name ASString

    Name = InputBox("Please enter your Name:", _"User Window", " ")

    MsgBox "Hello, " & Name & "!“, , “Say Hello”

    End Sub


Some useful symbols

Some Useful Symbols

  • Use a space + underscore “_” for Line continuation in VBA – for lengthy codes over several lines

    • E.g. InputBox "Type your name:", _"User’s 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


Msgbox function

MsgBox Function

  • Displays a predefined message box to list information for the user

  • Syntax:MsgBox (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


Useful tips

Useful Tips

  • VBA adjusts the case of the letters for keywords, properties, and methods.

  • VBA inserts spaces between operators.

    • E.g. It automatically converts “Ans=1+2” 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.


Assignment

Assignment

Write a program, and store it in a file called TravelExpenses.xls, that does the following:

  • it asks for a person’s first name and stores is it in FName,

  • it asks for a person’s 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/MPG*AvgPrice) 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 $.


Exercise help

Exercise Help

  • Open the file

  • Get into the VBE

  • Add a module

  • Start a sub

  • Type the code

    • Don’t 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


  • Login