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.)
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.
AT&T iPhone6 Upgrade: 2-Year contract vs. Next plans
If you are getting ready to upgrade to iPhone6 through AT&T, you might be in for a surprise or two. The first surprise is that back in July of last year, AT&T changed its phone upgrade eligibility date from 20 months to 24 months.
Using a 2-year contract option, this change most likely means that you did not qualify for an iPhone6 pre-order last Friday, and will probably have to wait till after September 19th’s official release to place your upgrade order. If you are not willing to wait, you can pay AT&T an upgrade surcharge ($40 if your upgrade date is later this month) to upgrade immediately.
Alternatively, you can take a completely different route, and go with a Next 12 or Next 18 installment plans and forget about service contracts and early termination fees altogether. At first glance, it’s pretty hard to follow AT&T’s case for the Next plans.
In this post I will present my own calculations and conclusions pertaining to different scenarios when either a 2-year contract or one of the two Next plans works better. Since Samsung Galaxy S5 is offered at the same price as iPhone6, these calculations will work for all of the Android lovers out there as well.
Top 10 Free resources to learn Microsoft Excel
Students often ask me to recommend best online Excel resources for further reference. After fighting the urge to LMGTFY them 🙂 , I suggest to utilize the largest search engine in the World, and trust Google to present the most relevant results for their query. (Searching for specific terms within relevant time frame would improve your search results dramatically.)
In addition, the second largest search engine in the World can stream very helpful How-To videos on magnitude of topics. This means that one can search YouTube to learn not only how to unclog a toilet, but also how to create a PivotChart.
If you are in a hurry, and don’t feel like watching a 10 minute video;SlideShare comes to the rescue, and offers a plethora of presentations, including Excel tutorials. Heck, you can browse through the SlideShare version of this post below.
That being said, some of us feel safer when using credible and trusted sources for all of our Excel needs. I started compiling the list of useful resources before YouTube and Slideshare came to existence, and added others over time. I hope that this post will satisfy your thirst for Excel knowledge. Many of these sites include tutorials, blog entries, discussion forums and YouTube videos.