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:

Function Hello()

Hello = "Hello World!"

End Function

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:

Function Hello(Name)

Hello = "Hello " & Name & "!"

End Function

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)

else

Quadratic = (-b + Disc)/(2*a)

end If

end function

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.

Let the learning begin! I have never used UDFs before, but am looking forward to using them a lot more. Very cool

Learning #1 was you can’t always copy and paste the above text directly. VBA didn’t like the smart quotes (Syntax error) – had had to replace them with “straight” ones.

LikeLike

Pingback: vba user function

I get a #VALUE error when I try the Quadratic function. Do you have an idea why that would happen?

LikeLike

Are you sure your quadratic has real solutions?

If it does, and if macros are enabled, you could try stepping through the code:

In the VB Editor insert a break point by clicking in the left hand side bar (you should get a brown blob).

Go back to the worksheet, select the UDF, press F2 (edit) then enter.

The VBE should then display, with the line with the break point highlighted in yellow.

You can then step through the function by pressing the F8 key, and see where the error occurs.

LikeLike