259 Lecture 9 Spring 2013

Advanced Excel Topics – Visual Basic for Applications (VBA)

VBA Topics

        Developer Tab Form Controls User Defined Functions VBA Editor Comments Variable Data Types Local Variables Variable Assignment 2

Form Controls

  Form controls consist of items seen in windows programs and dialog boxes such as buttons, scroll bars, spinners, list boxes, check boxes, radio buttons, … In order to be able to add form control items, the “developer tab” MUST be added.

3

Adding the Developer Tab to the Ribbon

  Windows: File, Options, Customize Ribbon, Check “Developer” Mac: Excel, Preferences, Ribbon, Check “Developer” 4

Add a List Box Control

 Example 1: Create a worksheet that will allow the user to select an “Employee Name” from a “list box” and return the corresponding “Employee Number”. Also highlight in ORANGE, the selected “Employee Name” and “Employee Number”.

5

Add a List Box Control (cont.)

 Step 1: Create an empty “List Box” .

 Developer, Insert (Icon), Form Controls, List Box, then Drag a Rectangle 6

Add a List Box Control (cont.)

 Step 2: Populate the “List Box” with the input items, and link the output of the “List Box” to a cell reference.

 Right-Click, Format Control, Control, …    Input range: A1:A5 Cell Link: C1 Test the bi-directional link between the “List Box” and cell C1. Notice how manually changing cell C1 also updates the “List Box” .

7

Add a List Box Control (cont.)

 Step 3: Add a formula to cell C2 to get the “Employee Number”  =INDEX(B1:B5,C1,1) 8

Add a List Box Control (cont.)

 Step 4: Add the conditional formatting to highlight in ORANGE, the selected “Employee Name” and “Employee Number”.

 Select cell A1    Home, Styles, Conditional Formatting, Manage Rules, New Rule, Use a formula to determine which cells to format =ROW()=$C$1 Use the “Format Painter” to apply the Conditional Format to the other cells.

9

Add a Scroll Bar Control

 Example 2: Create a worksheet that will decode a one word “Caesar Cipher”. Allow the user to select the “shift” value by using a “Scroll Bar”.

10

Add a Scroll Bar Control (cont.)

 Step 1: Create a “Scroll Bar”  Developer, Insert (Icon), Form Controls, Scroll Bar, then Drag a Rectangle 11

Add a Scroll Bar Control (cont.)

 Step 2: Set the properties for the “Scroll Bar”, and link the output of “Scroll Bar” to a cell reference.

 Right-Click, Format Control, Control, …   Minimum Value: 0 ; Maximum Value: 25 ; Incremental Value: 1 ; Cell Link: O1 Test the bi-directional link between the “Scroll Bar” and cell O1. Notice how manually changing cell O1 also updates the “Scroll Bar” .

12

Add a Spinner Control

 Example 3: Create a worksheet that will decode a one word “Caesar Cipher”. Allow the user to select the “shift” value by using a “Spinner”.

13

Add a Spinner Control (cont.)

 Step 1: Create a “Spinner”  Developer, Insert (Icon), Form Controls, Spin Button, then Drag a Rectangle 14

Add a Spinner Control (cont.)



Step 2:

 Set the properties for the “Spinner”, and link the output of “Spinner” to a cell reference.

Right-Click, Format Control, Control, …   Minimum Value: 0 ; Maximum Value: 25 ; Cell Link: O1 Test the bi-directional link between the “Spinner” and cell O1. Notice how manually changing cell O1 also updates the “Spinner” .

15

Add a Spinner Control (cont.)

 Example 4: Create a worksheet that will decode a one word “Caesar Cipher”. Allow the user to select the “shift” value by using a “Spinner”. The worksheet should indicate that the user has “LOCKED” in on a “real” word by highlighting the characters of the decoded word in BLUE.

 Decode the following word: XDGOWJ  Decode the following word: ZOYZVO 16

Add a Spinner Control (cont.)

17

Moving, Resizing, Deleting Form Controls

 Right-Click on the Form Control, ESC 18

Computer Program

  Def: A computer program or program is a sequence of instructions that a computer can interpret and execute. (from dictionary.com) Def: A computer program is one or more instructions that are intended for execution by a computer. Without programs, computers would not run. Moreover, a computer program does nothing unless its instructions are executed by a central processor. Computer programs are the result of the compilation or interpretation of programming languages, are embedded into hardware, or may be manually inputted to the central processor of a computer. (from Wikipedia) 19

Computer Program (cont.)

  Software such as Excel, Mathematica, or MATLAB can be can be used to write computer programs when more than single step or an iterative process is required for some application or problem that needs to be solved.

There are many programming languages that can be used to write computer programs, including Visual Basic for

Applications.

20

Visual Basic for Applications (VBA)

     BASIC – Beginners All Purpose Symbolic Instruction Code (1960) Visual Basic for Windows – Introduced by Microsoft in 1991, GUI interface, OOP – Object Oriented Programming Visual Basic for Applications – Introduced by Microsoft (Excel 5) as a common scripting language for Word, Excel, Power Point, and Access.

VBA is “BIG” and takes years to completely master. We are getting a “CRASH” course in VBA dealing specifically with “User Defined Functions”.

21

Visual Basic (VB) Editor

  Add in the Developer Tab  File, Options, Customize Ribbon, Main Tabs, Developer Start the Visual Basic Editor  Press ALT+F11 or Developer Tab, “Visual Basic” 22

User Defined Functions (UDF)

  User defined functions (UDF), are functions created by the user, but behave just like built in Excel functions. User defined functions are written in VBA code.

23

User Defined Functions (cont.)

  Example 5: Create a “Hello World” user defined function (UDF) named Hello_World() that accepts no arguments and returns the phrase “Hello World”. For example, entering the formula =Hello_World() into cell A1, will produce “Hello World” in cell A1.

24

User Defined Functions (cont.)

  

VBA code:

Function Hello_World() As String Hello_World = “Hello World” End Function VBA code must be stored in a “Module” which is a place for holding related VBA functions and sub-routines. In this example, the UDF Hello_World() will be stored in a module named: “First_Program”.

25

1.

2.

3.

4.

Steps to create the Hello_World() user defined function

5.

6.

7.

8.

9.

Activate the VBA Editor.

Insert, Module Enter the VBA code Insert “Option Explicit” at the very top before any function or subroutine declarations. MAKES SURE THAT ALL VARIBLES ARE DEFINED … GIVES ERROR IF NOT ALL DEFINED Click on “Module1”, change “(name)” property from “Module1” to “First_Program” Save File, “Close and Return to Microsoft Excel” Use the function Hello_World() just like any other built-in Excel function.

Save the worksheet as a “Macro Enabled Workbook” 26

Fahrenheit to Celsius UDF

   Example 6: Create a user defined function F_To_C() that will convert a temperature given and degrees Fahrenheit and return the equivalent temperate in degrees Celsius. For example, =F_To_C(32) should return 0 and =F_To_C(212) should return 100.

VBA code:

Function F_To_C(F As Double) As Double F_To_C = 5/9*(F-32) End Function 27

Recalculating User Defined Functions

    The F9 key is used to recalculate all worksheets in all open workbooks.

Pressing the F9 key will not cause “User Defined Functions” to recalculate. By default, CTRL+ALT+F9 must be pressed to “force” all formulas to be recalculated. It is important to press CTRL+ALT+F9 after making changes to the VBA code of a UDF.

28

Line Continuation

  Use the _ character for line continuation.

As an example, the following code is “too long” for a single line:  Function PerpBisectorIntercept(x1 As Double, y1 As Double, x2 As Double, y2 As Double) As Double To make code more “readible”, use multiple lines for VBA code, via the _ character.

Function PerpBisectorIntercept(x1 As Double, _ y1 As Double, _ x2 As Double, _ y2 As Double) As Double 29

Inserting Comments

    Comments are ignored by VBA. They are notes intended to be read by humans. Comments begin with a single quote character ‘ or rem. Comments are often used for the following purposes:  Use comments to describe briefly the purpose of the user defined function.

    Use comments to describe changes made to the user defined function.

Use comments to indicate that you are using functions or code in an unusual or non-standard manner.

Use comments to describe the purpose of variables so that you and other people can decipher otherwise cryptic names.

Use comments to “temporarily” disable lines of VBA code.

30

Inserting Comments (cont.)

 Example 7: Add comments to the following user defined function to help make the VBA code more “readable” : Function Perp_Bisector_Slope(x1 As Double, y1 As Double, x2 As Double, y2 As Double) As Double

'Returns the slope of the line that is the "perpendicular bisector" of the line segment 'determined by points (x1,y1) and (x2,y2) 'The function does NOT work for vertical lines

Dim m1 As Double, m2 As Double m1 = (y2 - y1) / (x2 - x1)

'negative reciprocal of m1

m2 = -1 / m1 Perp_Bisector_Slope = m2 End Function 31

Variable Data Types

  A variable’s data type determines both the type of information that can be stored, and also the amount of computer memory taken. The next slide contains a “condensed” list of the data types available in VBA: 32

Variable Data Types (cont.)

Type

Boolean Double Integer String Variant

What the Variable Can Hold

True or False -12.42424, 3.156788, -12, 12 Integers in the interval [-32768, 32767] “Toad” , “12” , “-12” , “12.2” , “True” True, “Toad”, -12.24252, -12,