Create Excel Formula On Your Own – User Defined Excel Functions (UDF)

Built-in Excel Worksheet Functions

This Is What We Do When Excel Does Not Have A Formula That We Need Desperately. There are plenty of Excel Worksheet Functions or Formula that do predefined Tasks. Few of them are “=Sum()’ , ‘=If()’ , ‘=Now()’, ‘=Vlookup()’.

These functions are grouped under VBA.WorkSheetFunction class and you view a collection of these function under Object Browser in VB Editor Window.

We may end up in situations where we may have to customize these existing functions or Formulas or we need to build our own Excel functions and use them in Excel sheet wherever required. Lets see how to handle those situations.

How To Build Excel Functions On Your Own?

This option is available in Excel for the users to create their own innovative functions and use it in Excel Sheets. It is shortly called as UDF (User Defined Functions) in Excel Terms.

Lets learn how to create our own worksheet Formulas with a simple example. In this below example, we are going to build 2 excel formula which will display the Address of the Cell where it is used and the other will divide any number passed to it by 2 and returns the quotient. To create this formula, follow the simple steps below.

1. Create a new Excel Workbook and Press Alt + F11 to open VB Editor.
2. Go to Menu -> Insert-> Module. Click on this option and it will add Module1 to the current excel sheet project.
3. Double click on Module1. Insert the below code.

Note: Do not press F5 to execute the code). Because this is a Function and we are going to use this inside Worksheet. More over it is defined as function and it will return a string value. So it has to be either invoked by another Sub routine or from Excel Worksheet itself.

Function CellAddr() As String
    CellAddr = ActiveCell.Address
End Function

Function DivideBy2(Num As Integer) As Integer
    DivideBy2 = Num / 2
End Function

More Tips: Auto Trigger a Macro when Any Cell is Edited – Run Macro based on an Event

How to Use UDF in Excel Worksheets?

That’s it. We have completed creating 2 simple UDF and they are ready to be used inside Worksheet.

Go to Sheet1. Enter this text in any cell.

=cellAddr()

This returns the Address of the cell in which it is entered. Assume this function is placed in cell C2 (Row 2 Column 3), then this function will return the value as “$C$2”.

=Divideby2(8)

This function divides the number passed as parameter by 2 and will return the result. In this case it will return value ‘4’.

The Cells will reflect the value returned to the function in the Code. Similarly we can build a lot of UDF and use in Excel sheets.

Note:

• Place these UDF in a new Module. Not as part of any “Sheet” or “Thisworkbook” Objects.
• UDF has certain limitations. It does not allow to change few worksheet properties.

More Tips: Date Format Corrections -Convert Date in String Format to Proper Date Formats