Writing User Defined Functions in VBA

What is a User Defined Function?

A user defined function, UDF is a function created by users to perform custom calculations in Excel and can used like any other Excel built-in functions, such as SUM, AVERAGE, etc. Custom functions can be written in VBA using Visual Basic Editor or in other languages as components of custom Add-Ins. The focus of this article is to write UDFs in VBA only.

What are the Limitations of UDFs?

UDFs cannot change the look and feel of Excel, i.e. the Excel Environment. This means that UDFs cannot a) insert, delete or format cells, b) change another cell’s value, c) manipulate spreadsheets, d) add names etc. These changes have to be made via macros.

How are UDF calculations performed?

Excel calculations are performed in two steps. 1) Excel determines which are the cells is your UDF dependent on for calculation and decide if your cell is uncalculated It is done each time u make changes to a formula or exit a cell. 2) During calculation itself, Excel determines which cell to calculate first and does the actual processing. As you can see, it is an iterative intelligent process.

What this means for UDFs is that a) the value of your cell may change several times, before Excel decides on the final correct answer, b) your UDF must include in its argument list all cells that it gets inputs from. Otherwise, Excel will not recalculate your UDF all the time when you make changes elsewhere, c) you should avoid unnecessary arguments which will cause your UDF to be recalculated unnecessarily and d) UDFs take up precious CPU capacity getting Excel to read your VBA code iteratively during the calculation process. You can make your UDF recalculate each time Excel does a calculation with the Application.Volatile statement. Finally UDFs written in VBA are not multi-threaded and calculated only on a single core unlike UDFs on xll add-ins.

Scope of UDFs

Most UDFs should be public in scope, which is the broadest possible, resulting them in being recognized by every module in the workbook, as well as in the formula or function bar. You declare the scope of a function like below. A private function will not show up in the insert function dialog box and can only be called by macros in the same module.

Public Function Blarbar()

…..

End Function

Adding Descriptions and Categories

You can specify which category you want to insert your UDF into using the MacroOptions Method. The code below inserts a macro called CLV into the functions list with the description “Returns the Close Location Value”. You paste the code after that into the code window for your workbook to make sure that your UDF is inserted each time you open the file.

Sub AddUDF()

Application.MacroOptions macro:="CLV", _

Description:="Returns the Close Location Value", _

Category:="Technical Indicators"

End Sub

Paste the below into the window you get when you right click view code on your workbook.

Private Sub Workbook_Open()

AddUDF

End Sub

Relevant References

http://support.microsoft.com/kb/170787

http://www.decisionmodels.com/calcsecretsj.htm

http://blogs.msdn.com/excel/archive/2005/11/03/488822.aspx

http://support.microsoft.com/kb/141693

Excel Help File: Excel Developer Home > Excel Object Model Reference > Application Object > Methods>Application.MacroOptions Method

Like what you have just read? Digg it or Tip'd it.

The objective of Finance4Traders is to help traders get started by bringing them unbiased research and ideas. Since late 2005, I have been developing trading strategies on a personal basis. Not all of these models are suitable for me, but other investors or traders might find them useful. After all, people have different investment/trading goals and habits. Thus, Finance4Traders becomes a convenient platform to disseminate my work...(Read more about Finance4Traders)