User defined functions

Download Report

Transcript User defined functions

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,

What the Variable Can’t Hold

12, -12, -12.2, 12.2, “Toad” , “Toad” , True, False, 12.2, -12.2, “Toad”, True, 12, -12, True, 12.2,

Amount of Memory the Variable Takes

2 Bytes 8 Bytes 2 Bytes (Length of the String) in Bytes (16+) Bytes 33

Why not use the “Variant” data type all of the time?

1.

2.

3.

4.

People reading VBA code may consider the unnecessary use of “variant” as “sloppy” programming.

The “Variant” data type is a memory “hog” that takes away from system resources.

The “Variant” data type “slows” down program execution speed. This is important when VBA code is doing “many” iterations.

Use the “Variant” data type only when it is “needed”.

34

Arguments to a User Defined Function

   The arguments to a user defined function are the variable(s) located inside of the parenthesis.

Arguments can be thought of as a user defined function’s “input(s)”. To specify the data type for an argument, place the word “As” immediately after the variable name followed by the data type.

35

Arguments to a User Defined Function (cont.)

 Examples: Function F_To_C(F As Double) As Double Function Perp_Bisector_Slope(x1 As Double, y1 As Double, x2 As Double, y2 As Double) As Double Function Quadratic_1(A As Double, B As Double, C As Double) As Double Function Letter_Gap(A As String) As Double Function Middle_Digits(A As String) As String 36

Result of a User Defined Function

  To specify the data type for the result of a user defined function, place the word “As” immediately after the last closing parenthesis in the function argument declaration followed by the data type.

The result of a user defined function can be thought of as what the function “outputs”.

37

Result of a User Defined Function (cont.)

 Examples: Function F_To_C(F As Double) As Double Function Perp_Bisector_Slope(x1 As Double, y1 As Double, x2 As Double, y2 As Double) As Double Function Quadratic_1(A As Double, B As Double, C As Double) As Double Function Letter_Gap(A As String) As Double Function Middle_Digits(A As String) As String 38

Local Variables

    A local variable is a variable declared within a user defined function or sub-routine. Local variables can only be used in the user defined function in which they are declared. When a user defined function or sub routine ends, the local variable no longer exists, and Excel frees up the memory that the local variable used. Local variables are often used to store intermediate results and to do other “chores”.

39

Local Variables (cont.)

 Example: Function Perp_Bisector_Intercept(x1 As Double, y1 As Double, x2 As Double, y2 As Double) As Double 'Returns the y-intercept 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 m As Double Dim Midpoint_X As Double, Midpoint_Y As Double

‘blah ‘blah ‘blah End Function 40

Forcing Programmers To Declare Variable Types

  It is considered good programming practice to declare all variables. To enforce this, use the VBA “Option Explicit” directive at the TOP of the module before any function or sub routine declarations.

41

Variable Assignment

    When dealing with assignment statements, VBA always evaluates the expression on the right hand side of the equals sign and stores the result in the variable on the left-hand side of the equals sign. It is sometimes helpful for “beginning programmers” to think of the equals sign as an arrow pointing to the left. Some programming languages automatically initialize variables to a default value (0, or the null-string) while others do not. It is considered “good” programming practice to ALWAYS initialize variables regardless of the programming language used.

42

Variable Assignment (cont.)

Example 8: What are the values of J and K before and after each line of the following VBA code:

“VBA”

K = 4 K = K + 1 J = K J = K+1 K = J K = J+J

Value of J and K

K 4 K K+1 J J K K+1 K K J J+J 43

Variable Assignment (cont.)

Value of J Before Value of K Before Line

?

?

K = 4 ?

4 K = K+1 ?

5 J = K 5 5 J = K+1 6 5 K = J 6 6 K = J+J

Value of J After

?

?

5 6 6 6

Value of K After

4 5 5 5 6 12 44

How to “Return” the Result of a User Defined Function

  Example: Function Hello_World() As String ‘Return “Hello World!!!!” Hello_World = “Hello World!!!!” End Function Example: Function F_To_C(F As Double) As Double ‘Return 5/9*(F-32) F_To_C = 5/9*(F-32) End Function 45

How to “Return” the Result of a User Defined Function (cont.)

 Example: Function Slope(x1 As Double, y1 As Double, x2 As Double, y2 As Double) As Double ‘Returns the slope of a line defined by the points (x1,y1) and (x2,y2) ‘The function does NOT work for vertical lines Dim m As Double m = (y2-y1)/(x2-x1) ‘Return the value of m Slope = m End Function 46

References

   VBA Excel Notes – John Albers Form Controls  Overview: http://office.microsoft.com/en-us/excel help/overview-of-forms-form-controls-and-activex controls-on-a-worksheet-HA010237663.aspx?CTT=3   List Box or Combo Box http://office.microsoft.com/en-us/excel-help/add-a list-box-or-combo-box-to-a-worksheet HP010236681.aspx#BM1 Scroll Bar or Spin Button http://office.microsoft.com/en-us/excel-help/add-a scroll-bar-or-spin-button-to-a-worksheet HP010236682.aspx?CTT=1 Shortcut Keys  http://office.microsoft.com/en-us/excel-help/excel shortcut-and-function-keys-HP001111659.aspx

47