An Excel User Defined Function (UDF) is probably the single most useful feature that remains unused by the majority of users. This post will provide a brief introduction with the obligatory “Hellow World” program, followed by something only a little more complicated and much more useful.
Excel UDFs allow users to write their own functions that operate in a similar way to the built in functions. It would for instance be possible, and in fact quite easy, to write a MySum() function that behaved exactly the same as the Excel Sum() function. Fortunately it is also possible to write functions providing functionality that is not built into Excel.
UDFs are written in Excel’s built-in programming language, Visual Basic for Applications (VBA), which is accessed through the Visual Basic Editor (VBE). To access the VBE:
1. Open Excel with a new blank workbook, and save it as Hello.xls
2. Press Alt-F11 to open the VBE. You should have a tree structure on the left displaying VBAProject(Hello.xls), with similar branches for any other workbooks or add-ins you may have open. Make sure that Hello.xls is selected, then select Insert-Module:
3. A new “Modules” folder will apear under VBAProject(Hello.xls), containing one module named “Module1? by default. Double-click this module to open it, and a blank (or near blank) edit window will appear on the right.
New functions may now be defined, which will be available whenever the Hello.xls workbook is open. For instance, in the edit window type the following code:
Hello = "Hello World!"
Now return to the spreadsheet and enter =Hello() in any cell. If you have followed the instructions correctly the cell will display “Hello World!”So that is how to transfer information from VBA to the worksheet, using a UDF. Transferring data in the other direction is just as easy. Go back to the VBE and
amend the function as shown below:
Hello = "Hello " & Name & "!"
Now return to the workshheet and enter “Fred” (say) in cell A1, and Hello(A1) in any other cell. The function will display “Hello Fred!”. Perhaps not the most useful functions in the world, but using the same principles we can write UDFs that perform complex functions otherwise not available in Excel (or any other spreadsheet).
For instance, the function below will find the solution to any quadratic equation with real solutions:
Function Quadratic(a, b, c, Out)
Disc = (b^2 - 4 * a * c)^.5
If out = 1 then
Quadratic = (-b - Disc) / (2 * a)
Quadratic = (-b + Disc)/(2*a)
This UDF will find the solutions to the quadratic equation: ax^2 + bx + c = 0, returning the lower solution if Out = 1, and the higher solution for any other value of Out.