Excel is undoubtedly the most popular spreadsheet software with a ton of intuitive features and functions. Microsoft is upgrading its features and functions day by day. But it is next to impossible for anyone to build a superset of functions for every possible problem. Therefore, Excel kept the place for programmers who can build their own function as per their requirement. Yes, I am talking about the Custom Function in Excel.
It is, of course, the biggest advantage of Excel that you can build your own functions, all is desired a little bit knowledge of Visual Basic. Basically, Visual Basic is a simple object-based programming language having English like syntax which isolates it from the other programming languages like C, C++, JAVA etc.
Excel is largely used for various kinds of data manipulation. But sometimes it becomes too intricate to meet the required goal with the built-in functions of Excel. To eradicate all the complications, we can use Custom Functions. Just walk through the example below and you will know why and how we create Custom Function in Excel.
Say, you are working on a data sheet and you have to sort out the prime numbers of a given data range, what will you do then? There are no such built-in functions in Excel by using which you can determine the prime number, so you have to use other functions together to form a complex function which will serve your purpose but, you need to hold a good command over various functions to use the appropriate combination. Don’t you think that would be a total mess?
Now, if you do have the slightest idea on programming, you would know that this problem is far from a simple algorithm. To eradicate this complexity, Custom Function can be a great approach. Now the question arrives, how can we create a Custom Function in Excel?
In Excel, Custom Functions can be written only in Visual Basic language so we need to open the Visual Basic editor first. Press ‘Alt+F11’ keys together to open the Visual Basic Editor. If you desired to place a Visual Basic Editor’s shortcut on quick access toolbar then click on the ‘Office Button’ and navigate to the bottom of the window and open ‘Excel Option’. Now, click on the ‘Customize’ option on the left pane. Now, you will see two boxes containing various commands. Select ‘All command’ from the drop-down list of the left box and scroll down to find ‘Visual Basic’ option. To add the new toolbar, click on the ‘Add’ button and then ‘OK’ button.
Inside ‘Visual Basic Editor’ head on to ‘Project Window’, at the left side of the editor’s window. Now, right click on the ‘Microsoft Excel Object’ and then ‘Insert’ and then ‘Module’. A small window will appear at the middle of the screen where you have to write the function in Visual Basic language. When you finish writing the function, save the file with ‘Excel-Macro enabled Workbook (.xlsm)’ format and reopen the excel file and you will see your newly made function is lying with the others. You can use it like the other function you use.
Here I have supplied the code snippet I have written for determining a number is Prime or not. Just take a look.
I have discussed the execution of a task which can’t be done with any of Excel’s built-in function alone. I have found Custom Function helpful in these cases. Now, it is up to you whether you prefer to use the nested Excel function or use your Custom Function to meet your desired goal. Let us know what you think.