User Defined Functions
User Defined Functions
Conestoga College
?
v.1.5
Peter Komisar
reference: 'VBA & Macros' Microsoft Excel 2010, Bill Jelen & Tracy Syrstad This note has been generated for private academic use and is not meant to be published - PK
// We discuss how to create functions followed by a survey of Mr. Excel's team's functions
Excel calls user defined functions UDFs. They are used just like built-in functions. UDFs can only be entered into standard modules and not into Sheet and ThisWorkbook modules.
Building a Custom Function
? Open a new module ? type the following function code in ? when finished save the module
The function is ready to be used!
A Simple Function
Function CopyRight( ) As String CopyRight = "?"
End Function
Simple Sub To Run This Function
Sub CopyR() Range("A1").Value = CopyRight End Sub
The function name is CopyRight, it takes no arguments and returns a String value, the copyright symbol.
Another Function Example
Function ConCat(N1 As String, N2 String As String) As String Concat = N1 & N2 End Function
Summary of Function Structural Details
ConCat function name Parenthesis house two arguments 'As String' makes the variable type 'String'
Notice each argument within the braces is set to String type but also the overall function is set to 'As String' implying the value that will be returned by the function is a String type.
Setting the return type of the function seems to be optional, as the function works without this declaration. One assumes the compiler can figure out the return type of the function from what is assigned to the name of the function. It is good to set the function return type though. A reader can easily tell what return value the function generates.
Assigning the Function Return Type By Assigning to the Function Name
Another unique aspect to Excel functions, is the assignment of the results of an operation inside the function to the name of the function. This maneuver establishes what return value the function will have.
Example
Add = N1 + N2
// where Add is the name of the function
To Use UDFs, User Defined Functions // specifically this Add function
? Enter a worksheet ? type numbers into cells A1 & A2 ? select A3 ? press Shift + F3 // or hit the fx icon ? select User Defined under category ? select target function ? select A1 in arg box 1 ? select A2 in arg box 2 ? click OK
This function can also be called from a macro as is shown below.
A Procedure Calling a User Defined Function
Sub ToAdd() Range("A1").Value = Add(10001, 20002) End Sub
A Word on Editing, Overwriting and Deleting Functions
Note Functions are not as accessible as Macros to editing which is probably an intentional design consideration. What if you write a second function with the same name? Rather than replacing the original you get an error message that the name of the function is ambiguous and Excel then qualifies the name with the name of the module. So the function name becomes Module11.Add or something like that. So how do I get rid of functions to start fresh? The functions are stored in modules even though they are not offered up for editing like macros are. Use Project Explorer in the VB Editor to locate the module the function is stored in and edit it there or you may delete it altogether.
Storing UDFs
User defined functions can be stored in different places to suit specific requirements. Mr. Excel lists the following.
? Personal.xlsb ? for private use and not in a workbook opened on another computer
? Workbook ? store it in the workbook in which it is being used ? useful for distributing to many people
? Add-in ? putting the UDF in an Add-in allows group sharing
? Template ? A UDF can be stored in a Template ? for use in creating several workbooks ? distributed to many people
Mr. Excel doesn't tell us yet the details of these sharing techniques so for now we will store by the default method which associates the function with the current opened workbook.
Excel 2010, Chapter 4 vs Excel 2013 Chapter 13
A quick look shows the two chapters have the same content except the final method, that uses the Case statement has had the example switched.
Contributor Examples
Mr. Excel dives into a survey of a large number of functions that were designed by contributors to his book. There is a lot of code offered for viewing that is not explained at all. We take up doing the survey anyway explaining various salient features we encounter along the way.
Because we are taking up this chapter a little later in our course, there are points made that have already been covered. This sort of material is left in to serve as a general review.
Text Download Resource // for reference purposes
Note the text's download resource has these functions listed. There is at least one function for which there are two variations, probably to work with earlier Excel versions. Also some functions in the download are a bit different than in the book so check as you go.
The Workbook Name Function
The first contributor function makes a call on the Name property to return the name of the current workbook. Notice again the return type of the function is String and in this case it takes no arguments. Also notice what is returned is assigned to the name of the function.
Example // from 'VBA & Macros' Microsoft Excel 2010, Bill Jelen & Tracy Syrstad
Function MyName() As String MyName = This Workbook.Name
End Function
OUTPUT
Book1.xlsm
The following form of the above function supplies the complete path to the workbook.
Example // from 'VBA & Macros' Microsoft Excel 2010, Bill Jelen & Tracy Syrstad
Function MyFullName() As String MyFullName = This Workbook.FullName
End Function
OUTPUT
C:\Documents and Settings\Peter\My Documents\VBA\TrustedMacros\Book1.xlsm
A Function To Test If a Book Is Open
The next one of Mr.Excel's contributor functions tests if a workbook is open. Notice the function returns the Boolean type and takes a String value.
Boolean Type ? limited to the values True or False, or 1 or 0
Inside this function, T is declared as the object type Excel.Workbook. Recall that when we assign a value to an object reference the Set operator is used.
Example
Dim T as Excel.Workbook '. . . Set T = Application.Workbooks(BK)
// a little review
A call is made on Err object. Err is not in the Object browser but ErrObject is and it has the following information is provided. The ErrObject module contains properties and procedures used to identify and handle run-time errors using the Err object In it there a Clear method which has the following description. Clear - clears all property settings for the Err object.
All programming languages have error handling code to deal with unexpected problems that arise in code. The ErrObject is part of VBA's error handling code.
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- excel functions fx troyclic
- mmss eexxcceell 22000077 ccoonn pprrooggrraammaacciióónn
- user defined functions
- manual bÁsico para empezar a trabajar con macros
- text functions function description
- frequency domain using excel stem2
- excel vba programming functions
- computer programming in excel vba part 3 functions
- formulas functions in microsoft excel webhome main
- a programming in vba springer
Related searches
- piecewise defined functions pdf
- user defined function matlab
- tableau user defined function
- programmer defined functions matlab
- defined benefit vs defined contribution
- user defined function in c
- user needs vs user requirements
- piecewise defined functions worksheet with answers
- sql user defined function
- python user defined type
- user defined function in sql
- types of user defined functions in sql