Excel Pragma

There have been a few articles on this topic but I haven’t found any that took into consideration currencies other than US Dollars and with different smaller denominations (for example, there are 1000 fils to the Kuwaity Dinar but only 100 cents to the US Dollar).

The most recent post I saw about Converting Numbers to Words was on pdbook.com (with a link to the Microsoft KnowledgeBase Article).

So here’s my version of this UDF. It’s based on the code from pdbook.com but is recursive and accepts parameters to define (a) the name of the currency, (b) the name of the smaller denomination and (c) the decimal precision.

Function SpellNumber(ByVal strAmount As String, strCur As String, strDec As String, iPrec As Integer)
    Dim BigDenom As String, SmallDenom As String, Temp As String
    Dim iDecimalPlace As Integer
    Dim Count As Integer
    
    ReDim Place(9) As String
    Place(2) = ” Thousand “
    Place(3) = ” Million “
    Place(4) = ” Billion “
    Place(5) = ” Trillion “
    
    ‘ String representation of amount.
    strAmount = Trim(Str(strAmount))
    
    ‘ Position of decimal place 0 if none.
    iDecimalPlace = InStr(strAmount, “.”)
    
    ‘ Separate the Integer part from the decimals.
    If iDecimalPlace > 0 Then
        SmallDenom = Left(Right(strAmount, Len(strAmount) - iDecimalPlace) & “0000000000″, iPrec)
        SmallDenom = SpellNumber(SmallDenom, strDec, “”, 0)
        BigDenom = Left(strAmount, iDecimalPlace - 1)
        BigDenom = SpellNumber(BigDenom, strCur, “”, 0)
        SpellNumber = BigDenom & ” and ” & SmallDenom
        Exit Function
    End If
    If iDecimalPlace = 0 Then
        Count = 1
        Do While strAmount <> “”
            Temp = GetHundreds(Right(strAmount, 3))
            If Temp <> “” Then BigDenom = Temp & Place(Count) & BigDenom
            If Len(strAmount) > 3 Then
                strAmount = Left(strAmount, Len(strAmount) - 3)
            Else
                strAmount = “”
            End If
            Count = Count + 1
        Loop
        Select Case BigDenom
            Case “”
                BigDenom = “No ” & strCur
            Case “One”
                BigDenom = “One ” & strCur
             Case Else
                BigDenom = BigDenom & ” ” & strCur
        End Select
        SpellNumber = BigDenom
    End If
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

The formula : “=spellnumber(”1234.5678″,”Dinar”,”Fils”,3)” would therefore result in “One Thousand Two Hundred Thirty Four Dinar and Five Hundred Sixty Seven Fils”

<UPDATE> Bob Phillips at XLDynamic also has a different version here.

A small revision to the ConcSep function where

• the opening quote can be different from the closing quote
• some values can be excluded from the quotes (used when the cell value is a function like to_date)

So here goes ConcSepQuote_Excl :

Public Function ConcSepQuote_Excl(InCells As Range, _
                Quot_St As String, Quot_End As String, _
                Sep As String, Excl As String) As String
    Dim OutStr As String
    Dim Cell As Variant
    
    OutStr = “”
    If InCells Is Nothing Then Exit Function
    On Error Resume Next
    For Each Cell In InCells
        If InStr(1, Cell.Value, Excl, vbTextCompare) > 0 Then
            OutStr = OutStr & Cell.Value & Sep
        Else
            OutStr = OutStr & Quot_St & Cell.Value & Quot_End & Sep
        End If
    Next Cell
    OutStr = Left(OutStr, Len(OutStr) - Len(Sep))
    ConcSepQuote_Excl = OutStr

and the revised example :

The objective is to create a spreadsheet that could automatically generate an Oracle SQL script to “INSERT” data into a table. A typical INSERT statement looks like : INSERT INTO <table>(<field string names>) VALUES (<values string>);

The problem when trying to generate the strings from a range of cells is to

1- put the values in the cells between quotes.

2- exclude certain types of cells from being put between quotes (for example, date values).

3- concatenate the quoted and non-quoted cells into one string.

The ConcSep function was very useful in generating the final string but the function needed more tweaking to put the values between quotes (while allowing the user to specify the quote character/string). So here’s the function :

Public Function ConcSepQuote(InCells As Range, Quot As String, Sep As String) As String
    Dim OutStr As String
    Dim Cell As Variant
    
    OutStr = “”
    If InCells Is Nothing Then Exit Function
    On Error Resume Next
    For Each Cell In InCells
        OutStr = OutStr & Quot & Cell.Value & Quot & Sep
    Next Cell
    OutStr = Left(OutStr, Len(OutStr) - Len(Sep))
    ConcSepQuote = OutStr
End Function

and a small example :

Ever needed the values from a range of cells as a string separated by commas ? I used do this in a two step concatenate : the first step is for every cell in the range and then another concatenate for all the “concatenated cells”.

So today, I decided to create my own UDF to generate a string from a selection of cells and any “separator”.

Here’s the ConcSep function :

And here’s the output :

Obviously, this now goes into the add-in you created . and will also be part of my Utilities project.

Last week I posted about a user-defined function to get the unique values in a range. I’ve now extended this function to return unique or duplicate values based on a parameter passed through the function call. so the function definition now changes from :

to

If you want the function to return Unique values only, then set GetUnique to 1 in your function call. Setting GetUnique to 0 will return Duplicate values.

The function basically imports all the values in the range to the array Entries with a corresponding array EntCount that counts the number of occurrences of every value. The rest is easy to figure out.

Obviously, it would be a good idea to create your own add-in where you can have such functions at close range for your every-day use.

Here’s the code (remember to correct the comments).

screen-shot :

You’ve started creating your functions and you’re very proud of the results. Now you want to have them as standard functions in Excel. I recommend to create an Excel Add-In where you can store all your functions and procedures, and have them accessible from the main application window or from your code. Be careful though if you’re redistributing your work.

The following example will guide  you through creating the Pragma Add-In and calling the Test procedure from Excel.

1– Create a new workbook.

2– Open the Visual Basic Editor (Alt + F11)

3– Add a module to your workbook  : in the Project Explorer, right-click on any of the objects in the workbook and select Insert –> Module

4– Add the following procedure in the Visual Basic Editor :

5– From the main application window (Excel), save your workbook as

     FileName = “Pragma” unless you really don’t like the name. really.

     Save As Type = Microsoft Excel Add-In (*.xla)

6– Still from the main application window, go to Tools —> Add-Ins and then browse to the folder where you saved the Add-In and select it.

7– Technically you’re done but if you want to test the Add-In, press Alt+F8. The procedure you’ve just created will not appear in the list so you need to type “test” (Macro Name) and Run.

A while back, j-walk posted an entry about Filling a ListBox with Unique Items.

Here’s my modified version to transform it into a UDF (User-Defined Function)that can be used in a spreadsheet:

Here’s how it looks on your sheet:

Remember to press Ctrl+Shift+Enter !