Welcome back guest blogger, Nick Williams. In this article he discusses the intricacies of User Defined Functions. UDFs are arguably the single most important feature of Excel because it adds virtually infinite depth to Excel’s functionality, supplementing the already extensive list of ~350 built-in functions. The only limit on what you can do with UDFs is the depth of your imagination. Nick is an Access tutor based in the U.K.
Creating User Defined Functions in Excel
Most users are familiar with at least some of the functions built into Excel, for example SUM and AVERAGE. Whilst these are useful tools, there are times when they are not capable of performing some of the more specialised tasks that advanced Excel users require. Creating a custom function is a way of performing a calculation over and over again without the need to run a macro each time or create a series of complicated formulas.
Enabling the Developer tab
User defined functions are created in the Visual Basic Editor, which can be accessed from the Developer ribbon. If the developer ribbon is not visible, it can be enabled by going to the File menu and choosing Options. Select Customize Ribbon from the list on the left hand side, and ensure that there is a tick next to Developer on the list of tabs on the right.
Creating a new module
The Visual Basic Editor can be opened by pressing Alt+F11, or by clicking the Visual Basic Editor icon on the Developer ribbon. Once in the Visual Basic Editor, the first step towards creating a custom function is to create a new module. Although it is possible to add a function to an existing module, putting it in its own module makes it easy to export and reuse in another workbook.
To create the new module, from the menu click Insert, followed by Module. The module should be inserted into the current workbook by default. It is best to store the module within the workbook itself, rather than in the Personal workbook, as anything stored in the Personal workbook is only available to the current user on the current computer. If the file were to be opened on another computer, or even by another user on the same computer, errors would occur wherever the function has been used.
Writing the function
The code for the new function can be entered into the new module window. The actual content will vary depending on what the function is doing, however, it will always start with ‘Function’ at the top and ‘End Function’ at the bottom. The variables used by the function go in the brackets after the function name. The below example is for a very simple function that works out speed.
The first line declares the name of the function (SPEED) and its two parameters (Distance and Time). The ‘As Double’ after each of the variables is telling Excel what type of data to expect from these parameters, in this case a double-precision floating-point number. The second line is the code telling the function how to calculate the value it needs to return. In this basic example it is simply one line stating that speed is equal to distance divided by time. The final line End Function is telling Excel that is the end of the function and there is no more code to run.
In practice, most functions will much longer and more complex than this, as speed is something that could be easily be calculated using a simple formula. Although there is nothing wrong with making calculations in this way, especially if they are being performed over and over again within the document, user defined functions really come into their own when doing more complex calculations, for example where multiple if statements are required.
Using the function
Once the function has been created in the Visual Basic Editor, it can be called up in a workbook in the same way as any of the built in functions. The below shows how the speed of three people has been calculated using the newly created SPEED function. The formula refers to other cells to get the variables distance (Cell B2) and time (Cell C2) and uses these to calculate the speed.
Unlike built in functions, custom functions do not show what variables are required once the open bracket symbol is used. This can however be displayed by pressing Ctrl-Shift-A, however rather than displaying as a tip, the variable names will be entered into the formula bar as a prompt for what they need to be replaced with.
Saving the workbook
As the user defined function is created using VBA code, it will be necessary to save the workbook as a macro-enabled workbook with a .xlsm file extension. When it comes to saving, simply select this as the file type. If this step is not taken, or the workbook had been previously saved as a standard Excel workbook with a .xlsx extension, a prompt will be shown warning that the function will not be saved.
Importing and exporting functions
If a function needs to be used in more than one workbook, rather than rewrite it, it can be exported from the Visual Basic Editor, and then imported into another workbook. When in the Visual Basic Editor, click on the module to be exported and select File > Export file… from the menu. In the new workbook, open the Visual Basic Editor and from the menu select File > Import file… from the menu to import the function.