Creating user-defined Excel functions (UDFs) – Part 2. Improved VBA code to spell a number in a text form.
In the previous post, we created Excel UDF, which translates a number into a text form, using Dollar/Cents as default currency type. We strictly relied on VBA code snippet, published on Microsoft’s knowledge base. While working with this code, we now realize that several enhancements to this UDF would be desirable.
First of all, when dealing with even amounts, we really don’t see a point of quite redundant“No Cents” message. Secondly, it appears that this code simply formats the amount specified, instead of rounding it. As an example $ 1,256.8789 will be spelled out as One Thousand Two Hundred Fifty Six Dollars and Eighty Seven Cents, but it really should be: One Thousand Two Hundred Fifty Six Dollars and Eighty Eight Cents. Next, we probably want to change our function name to reflect the fact it only translates dollar amounts, not any other currencies, nor non-currency amounts. This is definitely a matter of personal taste, but let’s call this function SpellNumberDollar and while at it, let’s change it’s input variable from MyNumber to Amount. Let’s then create a new universal UDF (SpellNumberCurrency), which could accept any currency type, instead of limiting ourselves to dollars. After all, we don’t have anything against Euros, do we? Finally, let’s write a true SpellNumber function, to spell out a non-currency number up to the third decimal point (thousandths), instead of current precision of two decimal points (hundredths.)