User Defined Functions (UDF) Excel

User Defined Functions (UDF) Excel

Functions:

By definition a function is pre-compiled code which always returns a value as response. Excel provides a large collection of inbuilt functions. Many more can be added by installing the external add-ins.

What is UDF?: stands for user defined functions. Sometimes user want to perform certain operations which cannot be accomplished with the help of available functions in excel. VBA (Visual Basic for Applications) extends it next level where user can write his own function and put it in Excel.

User Defined Functions are often called “Custom Functions”. A UDF can remain in a code module attached to a workbook, in which case it will always be available when that workbook is open. Alternatively you can create your own add-in containing one or more functions that you can install into Excel just like a commercial add-in. These functions must be declared public in scope to access in Excel Worksheet.

Public Function JoinTextWithValues(a As String, b As String) As String
	JoinTextWithValues = a + b
End Function

Output: activate worksheet back and put “=” in any cell and try typing “Join” excel will automatic generates list of functions starting with “Join” and you will notice your function in the populated list.

Application.Volatile: above function gets called automatically when you made changes to argument cells which you supplied. If you wish to calculate this function every time when Excel is in dirty state or user makes changes to anything, you need to make function as Volatile by putting following line in the starting of function:

Public Function JoinTextWithValues(a As String, b As String) As String
    Application.Volatile
    JoinTextWithValues = a + b
End Function

Note: Application.Volatile is very resource consuming statement hence, developers are advised to make function volatile only when it is needed.

Next>> Advanced Filter Excel VBA

Leave a Reply

Your email address will not be published.