MAKING EXCEL WORK FOR YOU

Heading

 © Roy Cox 2005 to 2013

Our Privacy & Cookie Policy


 © Roy Cox 2005 to 2013

Our Privacy & Cookie Policy


Creating your own Excel Functions

Introduction to User Defined Functions

Excel has some 300+ built in functions, but there are occasions when a standard Function will not give you the result that you need or you need to replace a complicated nested formula using a combination of standard Excel functions. If this is the case then you can create a Custom Function or User Defined Function, that can be used the same way you would use SUM() or some other built-in Excel function. However, you should be aware of the limitations of these Functions If you want to create a UDF then you have to write the VBA for the UDF, you cannot record the code, however you can copy and paste snippets of a recorded macro into your UDF.

Some Limitations

UDF's do not have the same flexibility as a standard Procedure. A UDF cannot alter the structure of a Worksheet, such as giving the tab a new name, turning off gridlines, protecting the Worksheet etc. A UDF cannot change the physical characteristic of a cell, including the one that houses the UDF, so we cannot use a UDF to change the font colour, background colour, etc of any cell. Nor can they be used to try and change any part of another cell in any way at all. This means a UDF cannot place a value into any other cell except the cell housing the UDF. A UDF cannot use many of Excel's built in features such as AutoFilters, Advanced Filters, Find, Replace and others You can use a UDF to Call (Run) another standard Procedure, but if we do the standard Procedure will then be under the same restrictions as the UDF itself. To make matters even worse, when you use a line of code in a UDF that cannot be executed you may not receive a Run-time error. You just end up with one of the error values (eg; #VALUE!) in the cell housing the UDF. This can make de-bugging UDF's rather difficult. A UDF should update automatically if the data content of any cell it references changes, this does not include format changes etc. To have any UDF update as a Volatile Function (e.g. NOW, TODAY etc) you would need to use: Application.Volatile as the first line. However, be aware that too many Volatile function can slow down Excel. Another option to force recalculation is to use: Edit>Replace and Replace = With = So basically a UDF is exactly that - a "User Defined Function", with the emphasis on Function. They should only be used to perform a calculation of some sort and not take the place of a Procedure. Another thing to keep in mind is that a UDF will more often than not be less efficient than a deeply nested group of standard Functions. This is because Excel's built in Functions are written in the extremely fast language, C++. VBA, unfortunately, is a very slow programming language. Despite all this, UDFs can be very useful as long as you are aware of the restrictions imposed upon them. When used in the correct context, and you become comfortable with them, you can build your own library of Functions that are not available to other Excel users. Your UDFs can be stored in the PERSONAL.xls or an addin (*.xla), in 2007 PERSONAL.xlsb or *.xlam. I have added a UDF to convert numbers to text here