This is a guest post by Yoav Ezer.
While Microsoft Excel is extremely feature-rich, there will inevitably come a time when you reach the limits of what the built-in functionality can do. The great news though is you can build upon the built-in abilities by writing your own Visual Basic code!Enable Visual Basic
Before we dive into the Visual Basic coding, we need to make sure you can get into that part of the application. You can switch on the developer tab by going into the Excel Options.
Make sure the "Show Developer Tab" option is ticked, then the Developer ribbon will appear.
Once you have the Developer ribbon you will see the Visual Basic button.
Your User Defined Function
Here is a simple User Defined Function. Type the code you see below right into the Visual Basic window.
You can switch back to Excel by hitting the Excel 'x' icon that is under the File menu.
How the Function Works
First the function checks the length of the number in terms of digits, it then it goes through each digit and adds the value to the total. At the end of the function this total is returned.
Using the User Defined Function
What we have created with this short piece of code is a new function that takes a number and sums the digits contained within it.
So if we supply it a cell reference, such as A3, it will give you a sum of the digits in whichever number A3 contains.
Summary
While the user defined function we create in this article might not be the most general purpose or indeed useful, it does show how easy it is to extend the functionality of Excel. Hopefully you will now be inspired to create your own User Defined Functions. Please do let us know how you get on!
About the author
Yoav Ezer co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to Excel conversion software.
For more Excel tips from Yoav, join him on Facebook or Twitter