Custom Excel Formulas - User Defined Functions/Formulas

User Defined Functions Introduction

Although Excel has over 300 built in functions, there are those times when you cannot achieve the result you want with a standard Function or you must get your result by creating a deeply nested formula using a combination of standard functions. It is at these times that a Custom Function or User Defined Function (UDF) can be very handy.

There are however a few drawbacks when writing a Function in the VBE as opposed to a Macro or Procedure. As long as we are aware of the rules, we should not encounter any serious problems. Before we look at some examples, let's get the negatives out of the way first. This way we are thinking along the same lines! I will refer to Custom Functions as UDF's (User Defined Functions)

Creating a UDF requires the use of VBA - there is no way around it. This means a user cannot Record a UDF, you have to create the UDF yourself. Nevertheless, this is not to say that you cannot copy and paste bits of a recorded macro into your UDF. UDF's do not have the same flexibility as a standard Procedure.

A UDF cannot alter the structure of a Worksheet, such as changing the Worksheet name, turning off gridlines, protecting the Worksheet etc.

They cannot change a 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. They cannot 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 Excels built in features such as AutoFilters, AdvancedFilters, Find, Replace to name but a few!

We 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 very difficult and leave one scratching their head!

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 (eg 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 very much as the name suggests - 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.

While all this negativity may leave you thinking "what is the use of them then?", they can and do come in very handy as long as we 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.

Gallery

stars (0 Reviews)

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
OzGrid is in no way associated with Microsoft.

Website designed by Astrava Solutions Ltd.