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.


         While Excel does not have such a function, Microsoft makes a VBA code for it readily available. They named this function SpellNumber, and it in turn uses 3 other VBA functions to perform its work: conversion of numbers form 1 to 9, numbers from 10 to 99, and numbers from 100 to 999. While you won’t be able to spell out quadrillion dollar amounts, you should be okay to spell out current U.S. Foreign Debt at the time of writing this post : Seventeen Trillion Six Hundred Thirty Three Billion Dollars.

         It would be great, if you could understand how this code actually works, but the reality of the matter is that you really don’t have to be able to read code to use it. In fact, you can simply download Excel file I have prepared for you and start using this function right away. Make sure to call this function by its proper name: SpellNumber(Parameter). Please make sure to enable macros on opening and you are all set. Entering following formula =SpellNumber(175643298.25) will result in desired output of: One Hundred Seventy Five Million Six Hundred Forty Three Thousand Two Hundred Ninety Eight Dollars and Twenty Five Cents. Similarly, you can pass a particular cell as a parameter to be converted.

          Let’s follow some simple steps required to create this file:

1. Assuming you are using Excel version 2007 or later, open a blank Excel workbook and save it as a Excel Macro-Enabled Workbook (.xlsm), or Excel Binary Workbook (.xlsb) format. I prefer the latter, since binary files seem to take less space.

2. Hold the ALT key, and press the F11 key. Microsoft Visual Basic for Applications – VBA Editor window should now open.

3. Choose Insert menu, and navigate to Module option to create a new code module

4. Copy the code snippet presented at the bottom of this post and paste it into Excel’s code window:

5. Save your workbook and click F11 to return to the main Excel window.

         You have just created a brand new UDF that can help you convert number into text. Please stay tuned for part 2 of this post, where will customize this code to better meet our computing needs.

Option Explicit
'Main Function
Function SpellNumber(ByVal MyNumber)
    Dim Dollars, Cents, Temp
    Dim DecimalPlace, Count
    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Million "
    Place(4) = " Billion "
    Place(5) = " Trillion "
    ' String representation of amount.
    MyNumber = Trim(Str(MyNumber))
    ' Position of decimal place 0 if none.
    DecimalPlace = InStr(MyNumber, ".")
    ' Convert cents and set MyNumber to dollar amount.
    If DecimalPlace > 0 Then
        Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
                  "00", 2))
        MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
    End If
    Count = 1
    Do While MyNumber <> ""
        Temp = GetHundreds(Right(MyNumber, 3))
        If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
        If Len(MyNumber) > 3 Then
            MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Else
            MyNumber = ""
        End If
        Count = Count + 1
    Loop
    Select Case Dollars
        Case ""
            Dollars = "No Dollars"
        Case "One"
            Dollars = "One Dollar"
         Case Else
            Dollars = Dollars & " Dollars"
    End Select
    Select Case Cents
        Case ""
            Cents = " and No Cents"
        Case "One"
            Cents = " and One Cent"
              Case Else
            Cents = " and " & Cents & " Cents"
    End Select
    SpellNumber = Dollars & Cents
End Function
      
' Converts a number from 100-999 into text 
Function GetHundreds(ByVal MyNumber)
    Dim Result As String
    If Val(MyNumber) = 0 Then Exit Function
    MyNumber = Right("000" & MyNumber, 3)
    ' Convert the hundreds place.
    If Mid(MyNumber, 1, 1) <> "0" Then
        Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
    End If
    ' Convert the tens and ones place.
    If Mid(MyNumber, 2, 1) <> "0" Then
        Result = Result & GetTens(Mid(MyNumber, 2))
    Else
        Result = Result & GetDigit(Mid(MyNumber, 3))
    End If
    GetHundreds = Result
End Function
      
' Converts a number from 10 to 99 into text. 
Function GetTens(TensText)
    Dim Result As String
    Result = ""           ' Null out the temporary function value.
    If Val(Left(TensText, 1)) = 1 Then   ' If value between 10-19...
        Select Case Val(TensText)
            Case 10: Result = "Ten"
            Case 11: Result = "Eleven"
            Case 12: Result = "Twelve"
            Case 13: Result = "Thirteen"
            Case 14: Result = "Fourteen"
            Case 15: Result = "Fifteen"
            Case 16: Result = "Sixteen"
            Case 17: Result = "Seventeen"
            Case 18: Result = "Eighteen"
            Case 19: Result = "Nineteen"
            Case Else
        End Select
    Else                                 ' If value between 20-99...
        Select Case Val(Left(TensText, 1))
            Case 2: Result = "Twenty "
            Case 3: Result = "Thirty "
            Case 4: Result = "Forty "
            Case 5: Result = "Fifty "
            Case 6: Result = "Sixty "
            Case 7: Result = "Seventy "
            Case 8: Result = "Eighty "
            Case 9: Result = "Ninety "
            Case Else
        End Select
        Result = Result & GetDigit _
            (Right(TensText, 1))  ' Retrieve ones place.
    End If
    GetTens = Result
End Function
     
' Converts a number from 1 to 9 into text. 
Function GetDigit(Digit)
    Select Case Val(Digit)
        Case 1: GetDigit = "One"
        Case 2: GetDigit = "Two"
        Case 3: GetDigit = "Three"
        Case 4: GetDigit = "Four"
        Case 5: GetDigit = "Five"
        Case 6: GetDigit = "Six"
        Case 7: GetDigit = "Seven"
        Case 8: GetDigit = "Eight"
        Case 9: GetDigit = "Nine"
        Case Else: GetDigit = ""
    End Select
End Function