As you are aware Microsoft products always use Visual Basic that you can use in Microsoft Word, Access, and Excel. Although Microsoft Excel includes a huge number of built-in worksheet functions, it doesn't have a function for every type of calculation you perform. Microsoft Excel has incidentally the provision to create custom-made functions as well.
It will be appreciated that Excel designers could not possibly make provisions for every calculation in terms of different needs of individual users. This tutorial therefore aims at showing how one can make a custom-made function in Microsoft excel through the use of Visual Basic. Visual Basic fortunately is one the easiest program languages ever made. It allows you to stop a running program and make changes, then let it keep running. The purpose of user-made functions is to allow the user to create a custom-made function that is not already included in the list of functions coming with Microsoft Excel pack. A function can perform a calculation that yields either a value or text to the specified cell.
When you enter a simple formula, it obviously begins with an equal sign (=), followed by constants. For example: =5+2*3 in an Excel sheet multiplies the last two digits and adds the first one to the result, following the standard order of mathematical operations, i.e. -multiplication is performed before addition.
When you enter a simple formula, it obviously begins with an equal sign (=), followed by constants. For example: =5+2*3 in an Excel sheet multiplies the last two digits and adds the first one to the result, following the standard order of mathematical operations, i.e. -multiplication is performed before addition.
|
|
|
Steps: How to write a custom-made function in excel.
- First of all to use Visual Basic in Excel, you have to enable the Developer Menu. Simply go to the option under General Options>Customize Ribbon>Developer.
- Create an Excel file on desktop and if you want to use custom-made functions in the created Excel-sheet, you have to save the spreadsheet as an .xlsm file. If you do not save in such a format, you would get this warning image as shown below.
|
|
- Now, to create a custom-made Excel function, go to the Developers menu, and click on Visual Basic. When you click on the Visual Basic option the screen may look like the second image indicated below.
|
|
- Please right-click the Microsoft Excel Object, and select Insert Module.
- At this stage, you get an empty screen to write your functions on. You can learn the language if you are not much familiar with, but we would show you the basic format that are usually followed.
Function myFunction (arguments) return type
myFunction = some_calculation
End Function
There is no return statement as with other languages and there is no compile step either.
- Please type your logic here, and then save the spreadsheet as an .xlsm file. In the example mentioned below, the function is identified as Commission. We used number type double as that allows decimal points. You can also indicate these declarations as VB, because the visual basic programming language allows you to do that.
- Now you can start using your function in the spreadsheet. Just reference it like you would for any other function.
- The resulting calculation will be your Commission.
