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
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.