 |
| <<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
|---|
|
In this lesson we will look at User defined functions (UDF's) or Custom functions as they are also called. While Excel already has over 300 Functions available to us, at times it doesn't quite have the one we need, or if it does, it requires nesting several of these to create the formula we want. It is in these situation where UDF's come in very handy.
User Defined Functions Negatives
Creating a UDF requires the use of VBA, there is no way around it. By this I mean a user cannot Record a UDF, you have to create the UDF yourself. This is not to say though, that you cannot copy and paste bits of a Recorded macro into your UDF.
Before we look at UDF's in detail I should point out that UDF's do not have the same flexibility as a standard Procedure. a UDF cannot alter the structure of a Worksheet, such as change the Worksheet name, turn off gridlines, protect 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 don't receive any error, other than 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! For this reason I recommend not calling a standard Procedure from a UDF, unless the standard Procedure was written solely for the UDF. 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. I find the most helpful thing to keep in mind when writing a UDF is that they are only an extension of the Paste Function (Function Wizard).
While all this negativity may leave you thinking "well what is the use of them then!" They can and do come in very handy so 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 normally available to other Excel users.
User Defined Functions Positives
I hope the above section has not put you off UDF's as they really are very handy! I only point out the negative side to them because a new user to UDF's often thinks they are no different to a standard Procedure. In other words they have learnt all this really cool stuff that we can do with VBA but cannot apply it to UDF's and have no idea why their UDF is not working and give up in frustration.
In my opinion the biggest plus for UDF's is that we can use any of the WorsheetFunctions that are available to us while in the VBE. This means with a bit of imagination and "out of the box" thinking we can not only extend the use of the standard WorksheetFunctions we but can also write a whole new Function that will do exactly what we want. Some of Excels standard Functions cannot be used across multiple Worksheets, but with the aid of a UDF we can change that.
But having said this don't be tempted to go overboard with UDF's in your Workbook. A UDF is not as efficient in a lot of cases as a very long nested standard formula. While it may look a whole lot neater in the cell, you can just about be certain that the code behind it is not as efficient as the code behind one of Excels standard Functions.
User Defined Functions Arguments
Now that we are fully aware of what we can and cannot do with UDF's let's move on to seeing some in action. A UDF can be contained within a standard module (Insert>Module) just as a standard Procedure. The difference is that all UDF's must begin with Function (as apposed to Sub) and end with End Function (as apposed to End Sub). So a UDF might look like:
Function MyOwn()
'Code goes here
When we write a standard Procedure we would not normally use the Parenthesis to include any arguments. With a Function it is the opposite, we usually do use them. As you are aware most of Excels standard Functions do take arguments, with the exception of Function like NOW(), TODAY etc. We could if we wanted write a very simple UDF that takes no arguments:
Function SheetName()
SheetName = ActiveSheet.Name
End Function
This would of course returns the name of the sheet that houses the formula in the first example and the full name and path of the Workbook that house the formula in the second. They would be used on the Worksheet like:
=SheetName()
Both take no arguments. Which means they must be entered exactly as shown above. If we tried to put an argument in either one, they would fail. Simply because we have not included any arguments in the Functions. Let's say we wanted the first function to take an argument and return the name of the sheet it refers to:
Function SheetName(rCell)
SheetName = rCell.Parent.Name
End Function
The Parent Property returns the Parent Object of the Object that it is used with.
To use this we would put in any cell:
=SheetName(Sheet3!A1)
Where Sheet3! could be any sheet and A1 could be any cell on the sheet.
While this UDF will and does work, it is missing something that we should always do. That is declare any Variables. In the case of Functions the name of the Function is a Variable as are any arguments supplied. The correct way to write this Function would be:
Function SheetName(rCell As Range) As String
SheetName = rCell.Parent.Name
End Function
As you can see we have declared our argument rCell as a range. This is because we are not the slightest bit interested in the Value of the chosen cell, all we want is the cell itself so that we can use the Parent Property. The "As String" after the Parenthesis is telling the Function to return a String (in this case the name of a Worksheet).
A word of caution with declaring the return type to a UDF. Unless you are certain it cannot be any other type omit declaring it. In the above example we can be certain that we will only ever return a String. But if we were dealing with numbers we must be very cautious as we usually cannot know for certain what size the number will be, or even if it is a whole number or not. For this reason I feel it is worth the trade-off and omitting the declaration of the Function name when dealing with numbers.
You will have noticed in the above examples that we parsed the result to the Function name itself.
SheetName = ActiveSheet.Name
and
In other words the cell that houses the Function will always be the cell that shows the result. This happens by default and cannot be changed. Remember we cannot change the value of any other cell with a UDF!
The number of arguments used in a UDF can vary, we are certainly not restricted to only one. Just remember though a Function used on a Worksheet that has a lot of arguments can be very confusing. So for this reason I would suggest keeping them to as few as possible.
Inputting Into Cells
Once we have written a UDF we can go ahead and place it in a cell to see the result (or possible problems). There are two ways this can be done. One is to just type the Function directly into a cell, preceded with an equal sign. The other way is to activate the Paste Function (Shift+F3) and scroll down to "User Defined". Then in the "Function Name" box to the right you will see a list of all UDF's available to the Active Workbook. Select the Function and then use the Wizard as you would for any normal Function. This is perhaps the easiest method of the two as it will eliminate any possible typos. If we do type it straight in and we miss-spell the name of the Function you will see the #NAME? in the cell. This is Excel telling you that it cannot find any Functions by that name. When you type a standard Function into a Worksheet cell it is always good practice to use all lower case, this way Excel will automatically capitalize the Function name if we have used it in the right context or not misspelt it. This does not happen with UDF's.
We can also nest our UDF with a Standard Function or vice versa. This can give our UDF even more versatility. For example if we had a UDF that only summed the last three entries in a Range we could use:
Function Sum3Max(rCell As Range)
This would work just fine, but only if there are three or more cells in the range we chose, for example:
=Sum3Max(A1:A10)
Would return a #VALUE! error if there are less than 3 numeric entries in the range A1:A10. We could overcome this in a number of ways by nesting our UDF within a standard Function:
Both methods would prevent the #VALUE! occurring. However I would go with the first method as it will only hide the #VALUE! if the number of numeric cells are less than three. The ISERR will hide it any many other error types and you won't know why. It is not good practice to hide any and all errors for any reason as the error is telling you something. Remember UDF's are hard enough to de-bug as they are!
You may find that you have written a UDF that would be very useful in a number of different Workbooks. If this is the case you can either copy the Function code into all workbooks (you will get sick of that pretty quick) or simply store it in a standard module within your Personal Macro Workbook. This way it will always be available to all Workbooks. The down side to this is that you will have to precede the UDF name with: PERSONAL.XLS! So if we stored our Sum3Max Function in our Personal Macro Workbook. When used in a cell it would look like:
Not really a problem if we used the Function Wizard, but definitely a pain if we type it in the cell directly. There is a way that can help (but not eliminate) this and that is to save a Workbook as a one letter name eg; X.xls and place this in the Start folder of Excel, normally C:\WINDOWS\Application Data\Microsoft\Excel\XLSTART\. Then you could place the Function within a module in the Workbook X.xls and use:
But in most cases, for every upside there is downside! In this case it would mean that Excel would need to open two Workbooks as hidden each time Excel is started. Unless of course you don't have a Personal Macro Workbook.
The other thing we can do to make our UDF a bit more user friendly is to include a brief description of what it is designed to do. It's all very well to be liberal with our comments within the code itself, but that will not be of any use when we call it up from within the Paste Function. All of Excels Functions include a brief description in the bottom of the Paste Function dialog box whenever they are selected. Most users do not include this as there is a bit of a trick to doing so. Here what you can do.
Record a Macro and call it the name you will give your function.
In the "Description" box type a brief description of what it does.
Click OK to Stop Recording.
Now open the VBE and change the word "Sub" to "Function" Excel will automatically change the "End Sub" to "End Function"
Write the code as normal. Do not remove any apostrophise though.
Function Sum3Max(rCell As Range)
' Sums the max values in the range as set by 'rCell
User Defined Functions Calculations
When we input a UDF into a cell it will only recalculate when any of the cells it is referencing changes value. This is normally just fine as there is normally no need for it to recalculate whenever any old cell changes. But at other times we may want to force a recalculation as often as possible. This can be done by making the Function what is known as Volatile. Excel already has a number of Volatile functions, with NOW() and TODAY() being the best known. These Volatile functions recalculate whenever any cell on the Worksheet calculates. To do this we simply place Application.Volatile(True) as the very first line within the Function. The (True) is optional as the default is True. So Application.Volatile will do exactly the same. You will most likely use Application.Volatile when dealing with Time periods. You may have a function that you want to retrieve the value of another cell each day, but only after midday.
Function AfterMidDay(rCell As Range)
This Function would obviously need to be forced to recalculate so that it knows what the Time is. To do this we would simply use:
Function AfterMidDay(rCell As Range)
Let's now look at another Function. This particular Function will sum all the cells is a range that have a Background colour the same as a nominated cell.
This would be used in a cell like:
=SumColor(A1,A1:A10)
Where cell A1 contains the Background colour of the cells we want to Sum. There is one drawback with this function though and that is it will not recalculate whenever the Background colour of any cell changes. This is because changing the Background colour of a cell does not cause a recalculation even though we have used Application.Volatile (True). I have only included it to show you the possible drawbacks to UDF's.
The use of Application.Volatile does come at a price though. If you have a lot of Volatile functions within the same workbook calculation can be slowed down dramatically.
Examples
What I will do now is include two User Defined Functions. I have purposely not written any comments in the code because I would like you to put on your thinking cap and see if you can put then to real use. This will of course mean you will need to understand what they are doing. Make full use of the De-bugging tools in the VBE if you need.
I will also help you write some User Defined Functions that you may have in mind for a real situation that you have.
Summary
User Defined Functions are very handy to know and are especially good if you combine then with some of Excel's existing Worksheet Functions. As long as you remember that they cannot act on any other cell and cannot change the structure of the Workbook in any way at all. Be careful that you are not reinventing the wheel when you do write a User Defined Function if the same could be achieved via the use of a Worksheet Function then you are better off doing so. Unless of course it requires a deeply nested function to be written many time over.