The Needs to Create User-Defined Functions in MS-Excel
You can create your own functions to supplement the built-in functions in Microsoft Excel spreadsheet which are quite limited and these functions could be very useful and powerful if you know how to program them properly.To enable us to customize our spreadsheet environment for individual needs,we need to create user defined functions.
A D V E R T I S E M E N T
For example, we might need a function that could calculate commissions payment based on the sales volume and it is quite difficult if not impossible by using the built-in function alone.
Lets look at the table which is given below:
Sales Volume($)
Commissons
<500
3%
<1000
6%
<2000
9%
<5000
12%
>5000
15%
In the above table, if a saleman attain a sale volume of $6000 and he will be paid $6000x12%=$720.00. A visual basic function to calculate the commissions could be written as
follows:
Function Comm(Sales_V As Variant) as Variant
If Sales_V <500 Then
Comm=Sales_V*0.03
Elseif Sales_V>=500 and Sales_V<1000 Then
Comm=Sales_V*0.06
Elseif Sales_V>=1000 and Sales_V<2000 Then
Comm=Sales_V*0.09
Elseif Sales_V>=200 and Sales_V<5000 Then
Comm=Sales_V*0.12
Elseif Sales_V>=5000 Then
Comm=Sales_V*0.15
End If
End Function
Using Microsoft Excel Visual Basic Editor
To create User Defined functions in MS Excel, you can click on tools and then select macro and then click on Visual Basic Editor as shown in the following Figure
Upon clicking the Visual Basic Editor, the VB Editor windows will appear as shown in the following figure and to create a function, type in the function as illustrated above After typing, save the
file and then return to the Excel windows.
In the Excel windows, type in the titles Sales Volume and Commissions in any two cells and by referring to following figure , key-in the Comm function at cell C4 and by referencing the value in cell B4 and using the format Comm(B4). Any value appear in cell B4 will pass the value to the Comm function in cell C4. For the rest of the rows, just copy the formula by dragging the bottom right corner of cell C4 to the required cells, and a nice and neat table that show the commissions will automatically appear. It can also be updated at anytime
