How to Add Custom Function in Excel File - Small Tutorial

In Excel forum/group many items experts give very useful functions. But normally seen that many user is coming back telling that they do not know how to use that. In this tutorial I shall try to explain that.

Before following below steps it is better that you properly close all excel files and start.

Step 1 

Suppose the expert has given you below function. This is just sample. You can use actual function given by experts.

'******************************************************
Function TellMyAge(lYear As Long, lMonth As Long, lDay As Long)

    'Just for Example Purpose
    'Created by Asadulla Javed
    'This function shows your age in fraction

    TellMyAge = WorksheetFunction.YearFrac(DateSerial(lYear, _
        lMonth, lDay), Date)

End Function
'******************************************************

Step 2

a) Open excel. Create a Blank Excel workbook by pressing Ctrl+N or by clicking "New" from office menu.

You can use any old workbook also. But always try to keep all functions in single file if possible.

b) In the blank workbook press Alt+F11. This will show you VB Editor. Click Insert in Menu bar then click Module. Refer below pic.




c) Copy the Function code from browser. You have to copy from "Function Tell....." to "End Funct...."

Then paste in code window where cursor is blinking. See the right circle in pic.

 After pasting close the VB editor window.

Step 3

Return to Excel. Go to any blank cell. Either Press Shift+F3 or click Insert function icon at left of formula bar. The "Insert Function" dialog box will open. In the "or select a category" box, select "User Defined" . From next box "Select a Function"  select your function . Here it is "TellMyAge".

Now depending on function you have to give input. In this sample you have to give Year, Month and Day of your DOB one by one separating with comma.

If you are familiar enough with the function you can directly type in cell and give inputs.If you know all inputs and their type then go to blank cell, type =Tellmyage(2000,1,1) 

Step 4

Up to this point you can use function in only one excel workbook where you have pasted the code. Pls note that like worksheet, VB Codes are also attached with single workbook.

Normally if a function is very useful, you need to use in every file. But it is not possible to paste the code in all excel files.

For this step 4 is required.

a)  Click "Save As" from office menu or press F12. Excel "Save As" dialog will appear. Give a useful name to the file in "File name" box. Suppose you give name "Myfunction". In Bottom of dialog box you will find a box called "Save as type". Select "Excel Add-in" from that box. Press OK.

b) Click office menu. Click Options at bottom . Select "Add-In" . In bottom side you will find one List Box called "Manage". "Excel Add-in " is selected by default there. If it it is not selected then you select from list box. Then click "Go".

c) The "Add-ins" dialog will appear. Find the name you given to your file where formula pasted. Select it. Press OK. Exit excel.

Now as per step 3 you can check the function from any excel file.


Hope it helps and best wishes.....