Create Custom / User Defined Functions or UDF in Excel using VBA.

Creating a UDF (User Defined Function) in Excel is one of the advanced VBA skills which must know.
After watching this video you will be very much comfortable in building your Excel custom functions (UDF) from scratch using VBA.

User-Defined Function (UDF) or Custom Function in Excel
Excel has many inbuilt functions or formulas which cover most of the requirements of most of the areas or work domains.
But in some cases, we can not find or have a simple or direct formula that is inbuilt in excel.

✍️✍️Text version of the topic✍️✍️

🌐 Websites 🌐

In excel we do not have any named formula or formula for
- Square of a Number Example: =Square(Number as Integer) (Though we can make formula to calculate the same but it is not like something inbuilt)
- Circle Area formula Example: =CircleArea(Radius as integer) (Though we can make formula to calculate area by referring cell but it is not like something inbuilt)
- Formula for calculating a color count in a range.
- Formula for extracting number from text (Though we have this using complex array formula which is not very user friendly requires much understanding to implement)

******Advantages of having Named function or UDF******
- Much user-friendly user not to worry about implementation or working part. Put input/inputs get an output.
- Reduces complexity, all complexities are handled in the backend.

Take Circle Area formula example
=CircleArea(Radius as integer) this UDF takes only radius of circle as input and outputs it’s area.

If done using Cell reference
=(22/7)* radius*radius though seems simple but less concise than UDF for CircleArea which takes only one input and here we need to multiply three things.

******Disadvantages of UDF******
- Little slower than inbuilt functions.
- We can not see information about inputs required for a UDF in excel user interface while typing UDF function. But we are going to see alternate way for this limit.

Note: Don’t reinvent the wheel first search whether there is any inbuilt function in excel which fulfills your requirement and if not found then you can make your own UDF.
Otherwise making UDF for already present inbuilt function will going to be slow as compared to its inbuilt function.

Just for learning purpose we are going to build two simple UDF
1 Square UDF which will give square of inputted number.
2 CircleArea UDF which will give area of a circle after inputting radius of the circle.

******Writing UDF in excel******

Note : Always remember in order to use UDF in Excel user-interface , UDF must be written in Standard module or Custom module not in Worksheet or Thisworkboook module.

Always save you macro or VBA code containing workbook in binary (.xlsb) or macro enabled workbook (.xlsm). Not doing so will lead to losing all you VBA code and hard work, after closing and reopening your file.

Correlation between inbuilt function like Sum or UDF function pseudocode.
=SUM(B2:B10) output type is long (number)

Function MyUDF(Rng As Range) As String
‘code
MyUDF=stringOutput
End Function

🔰 Click the below link to download the file.

****Code Used****

Function Square(rng As Variant) As Long
'' since square is number hence given long (large range)
''output data type
Dim sqNm As Long ''data type must be same as output data type
If IsNumeric(rng) Then
sqNm = rng * rng
Else
sqNm = rng.Value * rng.Value
End If
Square = sqNm
End Function
'******************************
Function CircleArea(val As Variant) As Double
Dim CArea As Double
If IsNumeric(CArea) Then
CArea = (22 / 7) * val * val '' if input is hard value
Else
CArea = (22 / 7) * val.Value * val.Value '' if input is range or cell refer.
End If
CircleArea = CArea
End Function
=====================================

📌📌You can watch and learn from the below videos also.📌📌

✅ UDF formula to get cell comment text.

✅ Vlookup to return duplicate matched values or return all matched lookup values using UDF

✅ Get value from the first visible cell in filtered range UDF function

✅ UDF for getting Quarter number in Excel

✅ Indirect function (dynamic ranging ) in excel.

#learnyouandme
#vba#udf
#excel#custom#function

[PiyushMedicalKnowledge]

Must watch this video
This is very important for all of you.
This video is about 46 USER DEFINED FUNCTION which will encourage you.