Create custom functions in Excel
|If you have to perform a custom calculation on your
wish to reuse the function frequently in Excel, you don't have to
repeatedly enter a long, complex worksheet formula. Instead, you can
create your own custom function in Excel to perform the calculations.
You can then use the function to create formulas that are easier to
enter and maintain.
To create your own custom worksheet functions, you work in Microsoft Visual Basic for Applications (VBA) and create a function macro.
For example, say you have a complex formula for figuring a pay package, where the pay package changes based on several factors such as a house rent allowance or a leave travel allowance. Instead of entering the lengthy formula that takes all of these factors into account every time you want to calculate the pay package, you can create a custom pay package function.
If your company changes the rules for the pay package calculations, you don't have to find and make tedious changes to all of your complex formulas. You only have to make changes in one place, the custom function, to update all the formulas in your workbook.
The following steps show how to create and use a custom function.
To keep the example simple, this function calculates the pay package based on the basic salary.
'House Rent Allowance (HRA)
'HRA=60% of basic salary,br> 'Leave Travel Allowance (LTA)
'LTA=8.33% of basic salary
paypackage = salary + salary * 0.6 + salary * 0.0833