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.

I have been to The Xcel Files before but never got to really explore it as deep as it deserves. I did this morning. And suddenly my to-read list doubled in size !! especially those API calls and libraries that can be called and used from Excel.
I think the time has come for me to go down the same route as Jwalk and Andrew and start writing my own set of utilities. It IS the best way to learn and master VBA. I will try to come up with content different from PUP 6 (Jwalk) and Andrew’s utilities… but I realize that my creativity is going to face a very though time so if you have any ideas for utilities that you would like to have but don’t, send me a comment.

By the way, if you don’t mind spending a few dollars, buy PUP 6 with the VBA source code. It’s definitely one of the best learning experience I have had on Excel and VBA.

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 :

Andrew at Andrew’s Excel Tips had a post a few days back about a very nice set of utilities that he created. I’ve tried it and am very impressed with the results. However, what tickled my curiosity the most is the ability to add a shortcut to the right-click menu. So obviously, I started snooping around his code (sorry Andrew !) and discovered that the menu structure is stored in the add-in worksheet with complete information on the menu names, sub-menu names, icons and references. Mmm… there’s a lot more to explore there. So there goes a new task on my to-do list…. but just when I was about to try coding something similar comes a post from Colo with a beautiful Menu Generator:

My “Menu Generator” is a program which allows you to make your own menu from an Excel add-in. Not only is it easy to create your own menu, you can also use it to update your add-in with new code. All you have to do is write your menu in the worksheet of the file and follow the Menu Wizard’s instructions. No programming for the command bar is necessary.

If you need the functionality, I strongly suggest you use this tool.
If you’re anything like me and your to-do list included learning how to create something similar, then you now have a benchmark to mark your progress to.

Good luck !

I’m trying to think of why someone would want to export their contacts to Excel… this then got me to think of the reason that makes me do it, and couldn’t find an answer except for the obvious one : “because I can do it”. so whatever your reason is, here goes :

(1) Exposing the Outlook model : in any automation job, your primary task is to make sure that the ‘automated’ application is exposed to Excel, which means you can control the other application’s objects from Excel. In this example, you will need to expose Outlook’s library as follows. In the Visual Basic Editor (Alt+F11 from Excel), go to Tools —> References. Locate the “Microsoft Outlook 9.0 Object Library“ (or other versions i.e. Microsoft Outlook x.x Object Library), check the box, then click on OK.

(2) Import the contacts:

Dick over at Dick’s Clicks is a must read on Automating Outlook with Excel.

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 !