Excel VBA : User Defined Functions (UDFs)

Best Online Course : SAS Programming with 50+ Case Studies

- Explain Programming Concepts in Simple English
- Live Projects & Case Studies
- Job Placement Assistance
- Get 10% off till Sep 11, 2017
- Batch starts from October 8, 2017

What is a UDF?

The acronym UDF stands for User Defined Function. With the UDF, you can create a custom function (i.e. not a part of excel built-in functions). It comes in very handy when it requires nesting of multiple built-in excel functions.

Can we record a UDF?

No, you cannot record a UDF. You have to write code for UDF yourself in module area of visual basic editor (VBE).

Benefits of UDFs

  1. It comes in very handy when it requires nesting of multiple built-in excel functions. By creating a UDF one time, you can use it anywhere in the workbook. It will do what you want.
  2. You can use a UDF in the Sub procedure.

Limitations of UDFs

  1. No macro recording facility
  2. Cannot change the structure of a worksheet such as insert/delete/rename a workbook or sheet, formatting etc
  3. Cannot use many of built in excel features such as AdvancedFilters, Find- Replace, Pivot Table etc

Difference between Sub Procedures and UDFs

UDFs must begin with Function keyword and end with End Function, whereas Sub procedures begins with Sub keyword and ends with End Sub keyword

Syntax
Function First()
'Your code
End Function


UDFs must begin with Function keyword, followed by function name and then define parameter with in parentheses (), followed by As keyword and functions's data type and end funtion with End Function.

Important Point : If you have no parameter, you still need to write parentheses ().

How to use

  1. Open an Excel Workbook
  2. Press Alt+F11 to open VBA Editor
  3. Go to Insert Menu >> Module
  4. In the module, write code for the function you want
  5. Save the file as Macro Enabled Workbook (xlsm) or Excel 97-2003 Workbook (xls)  
  6. Insert user defined function by typing the function i.e. =first()

Example 1

Suppose you are asked to write a custom function that converts Fahrenheit to Celsius.

Function Celsius(Fahrenheit As Double ) As Double
     Celsius = (Fahrenheit - 32) * 5 / 9
End Function

How to use

Like the other excel built-in functions, enter your custom function with = followed by function name.

In this example, 100 is the Fahrenheit value.


Example 2

If student's score is greater than or equal to 75, he/she will get A grade.
If student's score is less than 75 but greater than 50, he/she will get B grade.
If student's score is less than or equal to 50, he/she will get C grade.

Function grade(score As Single) As String
         If score >= 75 Then
         grade = "A"
         ElseIf score > 50 Then
         grade = "B"
         Else
         grade = "C"
         End If
End Function








Excel Tutorials : 100 Excel Tutorials

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has close to 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like retail and commercial banking, Telecom, HR and Automotive.


While I love having friends who agree, I only learn from those who don't.

Let's Get Connected: Email | LinkedIn

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

1 Response to "Excel VBA : User Defined Functions (UDFs)"

  1. Nicely explained! Keep up the good work :-)

    ReplyDelete

Next → ← Prev