by ·0 Comments

Creating user-defined Excel functions (UDFs) – Temperature Conversion and BMI Calculation functions from scratch.

Excel UDF, custom function to convert temperatures and BMI calculator


         Our last two posts focused on using, or working with somebody else’s VBA code. This article will focus on creating UDF functions from scratch. We will start with a simple temperature conversion from Celsius to Fahreheit, and vice versa. We will then calculate metric Body Mass Index (BMI), graduating to an imperial BMI calculation with 3 variables.

         Since we’ve already worked with VBA code, we know some basic syntax rules for our future functions:

                  1. Start out with Option Explicit statement.
         Note : Option Explicit statement forces explicit declaration of our variables, and gives us an error message if we have a typo instead of performing incorrect calculations.
                  2. Define our function name and its parameter(s)
                   Function FunctionName(Parameter DataType) As DataType.
         Note : VBA makes definining data types optional, but it is a good habit to have.
                  3. Perform our calculations: Your code goes here.
                  4. Exit function and return it’s value: End Function.

Continue reading

by ·0 Comments

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

Excel UDF, custom function to spell a number.


         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.)

Continue reading

by ·0 Comments

AT&T iPhone6 Upgrade: 2-Year contract vs. Next plans

iPhone6Upgrade Options with AT&T

         

         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.

Continue reading

by ·0 Comments

Top 10 Free resources to learn Microsoft Excel

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.

Continue reading