My own Euler problem

Hi everyone,

With all these Euler posts I thought, why not post my own?
Suppose the following:
I have a products table set up like this:

CodeProdName Price
0001Product1 556.68
0002Product2977.41
0003Product3350.62
0004Product4509.16
0005Product5748.4
0006Product6802.96

(list goes on to as much as 5000 products)
And I want to show this table in a userform and give the user some filtering possibilities (lets say on the first two columns, which may be unsorted).
Currently I load the list into a variant and add the items one at the time into a three column listbox.
As the user enters a search string, I have an If statement inside the loop to detect if an item needs to be added to the listbox or not. This (as you can imagine) is a slow process.
My task for you: how would you solve this problem so the filtering is as fast as possible?

Regards,

Jan Karel pieterse
www.jkp-ads.com

Posted in Uncategorized

17 thoughts on “My own Euler problem

  1. I’d try to keep it as simple as possible. I’m not sure if this is powerful enough, but ordinary worksheet data filters allow you to set a variety of custom filters. Using this would result in a very quick response and no need to populate listboxes – just show the user the actual filtered data in the worksheet.

    The choice of custom filter options could be presented in an easy to use form, because Excel’s filter wizard isn’t that simple to use. A little VBA would implement the chosen filter.

  2. Hi Dermot,

    Good idea, but I’d prefer to use a form to show the data, as the user needs to pick a product from the table and then that product will be added to a list of products in a quotation table. But this does have potential. Thanks.

  3. Jan Karel, I think we need a bit more information. It only takes 1 second to load 5,000 items into a listbox, so is it the query that is slow? If so, how complex is it? If you were to post a working example, that might illustrate.

  4. JK,
    I am doing a VERY similar thing – but only one column in the listbox, and about 500 items. It is certainly not laggy.

    My tricks:

    1. I use a function to load a variant array, then set the listbox.List parameter = the function result (I expect you do something very similar)

    2. I use the LIKE operator to detect similar values

    3. I use a collection to store the valid matches, then write its values to an array, like in item 1 above.

    I will test performance with 5000 instead of 500.

  5. Woops – trying this with 7000 entries slows it right down – but the lag had little to do with loading the userform – and everything to do with the BubbleSort.

    Without the sort, <1 sec to load, with sort, more like 10 secs.
    Selection matching is still more or less instant.

    From this, I would suggest either a pre-sorted data set (if possible), or QuickSort.
    AlexJ

  6. Jan Karel..
    Fill a zero based array with the data needed. Then use ListBox1.List=myArray
    Don’t use clear method. Use ListBox1.List=Array()

    Tested with 100’000 items. Virtually instant.
    Have fun, Jurgen

  7. OK, I cracked it.

    I used to have a filtering routine (inside a loop which does all rows) with a test for validity that looked like this:

            If LCase(Table(lRow, 1)) Like “*” &amp; LCase(tbxSearchArticle1.Value) &amp; “*” _
            And LCase(Table(lRow, 2)) Like “*” &amp; LCase(tbxSearchArticle2.Value) &amp; “*” _
            And LCase(Table(lRow, 3)) Like “*” &amp; LCase(tbxSearchArticle3.Value) &amp; “*” Then

    I decided to move the calls to the controls out of the loop and use variables. Also I decided to use the module level variable mvTable, rather than the property calls to the Table property Get methods. Finally instead of using Like, I use Instr. So my test changed to:

                If (InStr(LCase(mvTable(lRow, 1)), sFilter1) * Len(sFilter1) + _
                    InStr(LCase(mvTable(lRow, 2)), sFilter2) * Len(sFilter2) + _
                    InStr(LCase(mvTable(lRow, 3)), sFilter3) * Len(sFilter3)) &gt; 0 Then

    Also I used the tip KeepItCool gave to place the array into the listbox directly, which saved me another 0.2 secs.

  8. pass the filter options to a worksheet, and then load the list box from the results, that’s how I’ve done it before.

    Alex,
    I believe that Bubble Sorts are quite slow in VBA.

  9. Reading this blog for some time now, but this is my first reply. I hope it’s useful.

    I had a similary problem. In my problem the data that needs to be put in the listbox also resides in a worksheet. I did the following:
    Put the range of the listbox items in a variant variable and passed them to the list property of the listbox all in once. (like keepITcool stated).

    To filter the list in the listbox, I used standard Excel filter options (autofilter) to filter the list on the worksheet. In a next step I copied the filtered rows (using specialcells(xlcelltypevisible)) to a new worksheet. This new sheet (or another range on the data sheet-if you want to skip a sheet) now holds the filtered items with no blank lines. This contiguous range can be put in a variant variable, and populate the listbox as described above very quickly.

    I’ m aware this method involves some extra steps, but the data is always addressed in block, and not on an item level.

  10. I use a Dynamic name(s) for the column(s) that I want to load in a Combobox/Listbox.
    On the Properties – Rowsource/ListFillRange – I type the name of the Range

  11. Thanks for all the tips guys, keep em coming!
    My current method is:

    Load table (on sheet) in Variant, pass variant to listbox. Then when filtering is needed, create extra variant array that holds the filtered items and pass that into the listbox. Very fast!

  12. “Load table (on sheet) in Variant, pass variant to listbox. Then when filtering is needed, create extra variant array that holds the filtered items and pass that into the listbox. Very fast!”

    I’ve tried this and can’t seem to get it to work….

    I think the problem is that the variant array represents a non-contiguous group of cells (ie the filtered list). So when filtering on ItemA (of which there are 4 in the list) only one appears in the list box.

    I’ve developed a work around which involves copying the filtered list (ie visible cells) to another sheet and then using this list (which is now contiguous) to populate the listbox.

    It works but doesn’t feel right…any views?

    D

  13. “I filter the array in code, not by using Excel, so no problems with discontinuous ranges.”

    How JKP?

    Do you loop over the array and check items?

  14. Hi Dan,

    I have three textboxes with filter criteria and a table in variant mvTable:
    The result is shown in listbox lbxTable.

    Sub SetFilter()
        Dim lRow As Long
        Dim lCol As Long
        Dim lFilterColumn As Long
        Dim sStr As String
        Dim vNewList() As Variant
        Dim lRowCt As Long
        Dim sFilter1 As String
        Dim sFilter2 As String
        Dim sFilter3 As String
        Application.Cursor = xlWait
        On Error Resume Next
        lbxTable.Clear
        ReDim vNewList(UBound(mvTable, 1), UBound(mvTable, 2))
        sFilter1 = LCase(tbxSearchArticle1.Value)
        sFilter2 = LCase(tbxSearchArticle2.Value)
        sFilter3 = LCase(tbxSearchArticle3.Value)
        If Len(sFilter1) + Len(sFilter2) + Len(sFilter3) = 0 Then
            lbxTable.List = mvTable
        Else
            For lRow = 1 To UBound(mvTable, 1)
                If (InStr(LCase(mvTable(lRow, 1)), sFilter1) * Len(sFilter1) + _
                    InStr(LCase(mvTable(lRow, 2)), sFilter2) * Len(sFilter2) + _
                    InStr(LCase(mvTable(lRow, 3)), sFilter3) * Len(sFilter3)) &gt; 0 Or lRow = 1 Then
                    For lCol = 0 To UBound(mvTable, 2)
                        vNewList(lRowCt, lCol) = mvTable(lRow, lCol + 1)
                    Next
                    lRowCt = lRowCt + 1
                End If
            Next
        lbxTable.List = vNewList
        End If
        Application.Cursor = xlDefault
    End Sub


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.