MS Excel Tutorial UDF Function

Excel UDF (User Defined Function)

 In This tutorial we learn that how we create User define function in Excel with very simple example. For example if you have student exam record and you want to calculate the Percentage of the students then in excel there is no proper pre define function . you can calculate the percentage of student using manual formula like that

=marks obtained * 100 / total marks

but you can also create your own function for calculating percentage and you can use it as pre define function.

Note:- In this tutorial basically I want to describe the Process of creation of UDF function and how we use it. For this purpose I am going to create a new UDF function with the Name Percentage and then I want to use this function. These are the basic steps. You can create more function according to your needs.

So how we create a UDF function (user define function) ?

First of all add the Developer Manu tab in Excel ribbon. For this purpose click on office button and click on Excel Option button.

Make active the check box "Show the developer " Tab in ribbon".

Now in excel environment you can see an extra tab "Developer" in excel ribbon.

Now click on This Developer Tab. A new window is open in Visual Basic Environment.

Click on Insert menu.

Select Module.

Now write your function in module window see the image below:

learninghints

Make shore that General option is selected as shown above image with red circle.

Note: you just copy the following code for time being to learn the process:

Function percentage(tm As Double, mo As Double) As Double
percentage = (mo * 100) / tm
End Function

In this function percentage two variable are pass as an argument. one is tm which store the total marks and mo store the Marks obtained.

Now save this file as Excel add-in file . for this purpose click on save button and select the file type as Excel add-in file. Give the any name and click on save button.

Next add this Add-in sport in MS excel. So that you can use this Function any workbook on you system.

 For this click on office button and click on Excel Option button.

Select Add-in from right menu in Appearing window.

Click on Go button in The bottom.

Now click on Brows button and select the Add-in file in which you have your own UDF (User define function).

Ok The first phase of Creation of UDf is complete. Now Below we discus

How we use the User Define Function

Open new excel workbook and create a simple table which have four field such that Name Total marks , Marks Obtained and Percentage.

Now Select the cell E4 ( where you want to calculate the Percentage) Click on Formula Tab and Select select Insert Function.

Select User Define function from the category list and then select your Function in my case function is percentage.

Now give the TM as C4 which is total marks and MO as D4 which is Marks Obtained.

Press enter or click on ok button.

Don.