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 Excel’s programming language - VBA ● This will greatly magnify your value as an employee

4. 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

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

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

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”).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)

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

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 (Ctrl+G): 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 ->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

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

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

23. 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

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

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:", _"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

27. 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

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 “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.

29. 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 $.

30. 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