Creating user-defined Excel functions (UDFs) – Part 1. VBA code to spell a number in a text form.

         One of the main reasons we use spreadsheets is to perform various computations on records present. As the most popular spreadsheet application, Excel now includes over 400 built-in functions. In fact, Excel 2013 alone brought us 53 new functions. These functions cover a wide range of various commonly used calculations as simple as SUM to add all values within your range, to much more involved PMT to calculate your monthly car loan payment.

         However, quite often, we still have a need for a custom function to perform our task at hand, that’s not covered by the program. Kind folks at Microsoft let us do just that, using their programming language of choice – Visual Basic for Applications (VBA.) With VBA, we can create a custom function to perform a special calculation, this custom function is officially called a user-defined functions (UDF.) You will find that UDFs are better-suited to meeting your data needs, than using a combination of multiple built-in Excel functions. They are also much easier to adopt for users, who are not particularly Excel-savvy.

         The most common misconception about Excel UDFs is that you have to learn to program in order to use them. This is not entirely true, since the Web is full of fully-functioning VBA macros that you are free to “borrow.” As an example, let’s say that we would like for Excel to spell out a number we specify. For some reason you decided that you don’t want to use your precious brain-power to perform this task. Perhaps, you need to write out a very important check to prepay your remaining mortgage principal amount. You simply cannot make any mistakes on that check, and want to rely on Excel to represent the amount of your payment in text form. A simple example would be to “translate” $ 1,357.09 into One Thousand Three Hundred Fifty Seven Dollars and Nine Cents.
Continue reading