Excel – Create Your Own Custom or User Defined Function (UDF)
As we all know Excel has more built in functions than any one human could ever need. Personally I have never had to use the BESSELK function and I very much doubt I ever will….in fact I have no idea what it does.
But what if there is some sort of calculation you do on a regular basis for which there is no built in function? You may well have a calculation that is specific to your company or job role and you use it on a daily basis. The calculation itself might be quite complex and requires lots of brackets, sub-calculations etc making it prone to typing errors or simply takes you ages to put together each time you need it.
What if you could create your own function with all the calculations built in and all you had to do was enter the basic data? Well…you can, with the help of VBA.
First of plan your function on paper; what is the calculation going to do and how?
For each element within your calculation, how many variables do you have? This is where it gets a bit confusing for people not used to working with or even knowing what I am referring to as variables.
Let’s try and build a simple example to understand what a variable is and how we can build variables into our function.
I want to be able to calculate the cost of a car journey so I can work out costs more accurately rather than just apply a flat rate per mile.
So what would I need to know to work this out?
• The distance driven
• The cost of fuel
• The average fuel consumption of the car being used to make the journey
Of these three values I need, how many will vary each time I need to do the calculation?
• Each journey will be different so the mileage will vary each time
• The cost of fuel is always changing (usually upwards!)
• Unless you only have one pool car, chances are you will be using different cars, each with their own fuel consumption rate
These are VARIABLES. In each case, there is no fixed value whenever we come to do the calculation. Hopefully, that clears up what a variable is.
So our calculation might be;
(distance driven/fuel consumption) * (cost of fuel * 4.5)
The *4.5 bit is more of a UK thing….fuel prices are quoted as cost per litre but we base fuel consumption on miles per gallon, and one UK gallon is 4.5 litres.
If we were to put numbers into this we might have;
(200miles/35mpg)*(£1.41 per litre * 4.5 litres) = £36.26
i.e. the cost of fuel to do a 200 mile journey in a car that does 35 miles per gallon.
Now that we have planned our function on paper, we now need to translate it into VBA code. Make sure you have a blank workbook open.
Go to the DEVELOPER tab and click on VISUAL BASIC, or press CTRL + F11. If you use 2007 or 2010 it may not be visible. For 2007 users, go to the OFFICE button, select EXCEL OPTIONS and on the POPULAR tab tick SHOW DEVELOPER TAB IN THE RIBBON. If you are on 2010, right click in the ribbon and select CUSTOMIZE RIBBON and tick the DEVELOPER option.
Remember earlier on in this task we opened a blank workbook? In the project window you should see something like VBAProject(Book1). Right click on the name of the workbook (project in VBA terms), click on INSERT, then click on MODULE. A MODULE is simply a folder that stores code within the workbook.
You should now have a blank page to the right of the project explorer window…that’s the list of open workbooks on the left hand side.
Now I need to declare my variables by giving them a name and data type.
The data type you use is important when working with variables. Each variable can only store one type of information. The basic variable types you are likely to use are as follows:
There are others but if you know these it’s a good start.
All that’s left now is to create the function itself substituting the names of the variables into our formula. Start by using the name you have given to your function (make sure you do this or your function won’t work);
If you are not used to working with variables, this does seem a bit odd to start with, looking at a formula that contains words rather than numbers.
To test your new function start typing the name in a cell (in the same workbook that contains the code module) where you want to perform the calculation;
The new function appears in the pop up list of functions! If you cannot remember the order of the variables you need to enter to make the function work, click on the fx button next to the formula bar. This will show you the wizard for your function, showing each variable you need to add in.
Use the wizard as you would any normal function and hey presto…you have an answer without the need to type out all the brackets, constants, operators etc.
If you are happy with your new function and want to make it a permanent part of YOUR Excel you need to save the workbook containing the code as an ADD-IN.
Make sure the workbook is clear: no calculations or data in the workbook. Then just do a SAVE AS, selecting EXCEL ADD-IN(*.XLAM), name the workbook, ideally using the function name and click on SAVE.
To make the function available in Excel you need to activate the add-in. Go to the OFFICE button (2007) or FILE (2010), then click on EXCEL OPTIONS, and then ADD-INS.
At the bottom of the window make sure EXCEL ADD-INS is showing and click on GO.
Tick your newly saved function as an add-in, and click on OK. You are now free to use your function whenever you want in any workbook on your laptop/PC. So if you are some sort of budding quantum physicist or you just have a special way of calculating your home finances, you can now put your own functions into Excel to work on all your data.