About User Defined Functions
Excel provides the user with a large collection of functions inbuilt, more than enough to satisfy the average user. Many more functions can be added by installing the various add-ins that are available on internet.
Most of the calculations can be easily done with the inbuilt functions provided by Microsoft, but it isn't long before you find yourself wishing that there was a function that did a particular job, and you can't find anything suitable in the list. You need a UDF.
A UDF (User Defined Function) is simply a function that you create yourself with VBA. UDFs are often called "Custom Functions". A UDF can remain in a code module attached to a workbook, in which case it will always be available when that workbook is open. Alternatively you can create your own add-in containing one or more functions that you can install into Excel just like a commercial add-in.
UDFs can be accessed by code modules too. Often UDFs are created by developers to work solely within the code of a VBA procedure and the user is never aware of their existence.
Like any function, the UDF can be as simple or as complex as you want. Let's start with an easy one...
A function that will calculate Simple interest.
Open a new workbook and then open the Visual Basic Editor (Tools > Macro > Visual Basic Editor or ALT+F11).
You will need a module in which to write your function so choose
Insert > Module. Into the empty module type:
Function SimpleInterest and press
ENTER.
The Visual Basic Editor completes the line for you and adds an End Function line as if you were creating a subroutine.
You will find it as follow:
Function SimpleInterest()
End Function
For calculation of simple interest we need 3 values from user i.e. Principal Amount, Rate of Interest, and Number of period. So, we will add 3 parameter to the function, and calculation of the Simple Interest.
Function SimpleInterest(pAmt As Double, rate As Integer, period as Integer)
Dim si as Double
si = (pAMT * rate * period)/ 100 ' (PRN/100)
SimpleInterest = si ' return value
End Function
Now we can test our function right away. Switch to the Excel window and enter figures for principal, rate of interest and period in separate cells. In a fourth cell enter your function as if it were one of the built-in ones. In this example cell A2 contains the Principal Amount (1000), cell B2 has Rate of Interest (12%) and cell C2 has period(24). In D2 I typed =SimpleInterest(A2,B2,C2) and the new function calculated the simple interest (28.8)...
A function that will give Name of Weekday
There is a function in Excel that return me the number of Week, but I want name of the week not a number!!! So, let's create function that will show us name of week(like "Monday").
We can do this by doing manipulation the inbuilt function. But I don't it to do every time so I decided to create a Custom user define function, that will full fill my requirement.
So, Let's Start...
Function WeekDayName(InputDate As Date)
Dim wDay As Integer Dim wDayName As String
wDay = Weekday(InputDate, vbSunday) ' vbSunday is set as defaule start date
Select Case wDay
Case 1
WeekDayName = "Sunday"
Case 2
WeekDayName = "Monday"
Case 3
WeekDayName = "Tuesday"
Case 4
WeekDayName = "Wednesday"
Case 5
WeekDayName = "Thursday"
Case 6
WeekDayName = "Friday"
Case 7
WeekDayName = "Saturday"
End Select
End Function
I've called my function "WeekDayName" and it takes a single argument, which I call "InputDate" which (of course) has to be a date. Here's how it works...
- The first line of the function declares a variable that I have called "WeekDayNumber" which will be an Integer (i.e. a whole number).
- The next line of the function assigns a value to that variable using Excel's WEEKDAY function. The value will be a number between 1 and 7. Although the default is 1=Sunday, I've included it anyway for clarity.
- Finally a Case Statement examines the value of the variable and returns the appropriate piece of text.
Were do we find our custom function in Excel?
You can also find the functions listed in the Function Wizard (sometimes called the Paste Function tool). Use the wizard to insert a function in the normal way (Insert > Function).
Scroll down the list of function categories to find User Defined and select it to see a list of available UDFs...
