Sort any type of collection in VBA 2

[1DMF]

Hi,

I seem unable to find any VBA collection or even a scripting dictionary that has an in-built sort functionality.

Is this correct?

I'm currently using this bubble sort code...

Public Function sortCol(ByVal cCol As Collection) As Collection

   Dim i As Long
   Dim x As Long
   Dim vOld As Variant

   For i = 1 To cCol.Count - 1
        For x = i + 1 To cCol.Count
            If cCol(i) > cCol(x) Then
               vOld = cCol(x)
               cCol.Remove x                              
               cCol.Add vOld, vOld, i
            End If
        Next x
    Next i
    
   Set sortCol = cCol
End Function

Which I am having to use to populate a list box on a from with the content of a directory like so...

Private Sub Form_Open(Cancel As Integer)

    Dim sPath As String
    Dim sFile As String
    Dim vCode As Variant
    Dim cCol1 As New Collection
    Dim cCol2 As New Collection
    
    ' remove current list items
    Do While Me.Pre_Approved.ListCount > 0
        Me.Pre_Approved.RemoveItem (0)
    Loop
    
    ' get files in directory
    sPath = cDrive & "Members Database\AccessTemplates\Financial_Promotions\"
    sFile = Dir(sPath & "*.*", vbDirectory)
    
    ' loop and add to collection
    Do While sFile <> ""
        If sFile <> "." And sFile <> ".." Then
            vCode = Split(sFile, "-")
            cCol1.Add left(vCode(UBound(vCode)), 7), left(vCode(UBound(vCode)), 7)
            cCol2.Add (sPath & sFile), left(vCode(UBound(vCode)), 7)
        End If
        sFile = Dir
    Loop

    ' sort collection
    Set cCol1 = sortCol(cCol1)

    ' add to listbox and remove from collection
    Do While cCol1.Count > 0
        Me.Pre_Approved.addItem Item:=Chr(34) & cCol1.Item(1) & Chr(34) & Chr(59) & Chr(34) & cCol2.Item(cCol1(1)) & Chr(34)
        cCol1.Remove 1
    Loop
    
    ' clear collections
    Set cCol1 = Nothing
    Set cCol2 = Nothing
    
End Sub

This seems ridiculously convoluted just to be able to have a sorted collection... so is there a better way?

Thanks,
1DMF.



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[strongm]

>so is there a better way?

Of course ...

 

[1DMF]

...?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[Andrzejek]

You may get a better luck with the help here if you would show what files you have and how you would like to show them in your list box, instead of relying on people here ‘deciphering’ your code.

Have fun.

---- Andy

 

[strongm]

Well, it depends ...

Firstly, you could use a disassociated recordset
Secondly, you can use .NET ...

Do you always want to end up with a sorted collection?

 

[1DMF]

Mike -> Well what I'm trying to do is dynamically generate a listbox with items from a folder directory , but are in alpha order (ASC).

As DIR gives you the content in a random order according to the VBA help docs, I thought I would use a collection with a sort method, only I can't find one.

Searching threw up a load of threads basically of the gist "in VBA you can't and you have to write a sort function".

I think I even came across some MSDN page that basically says not until .NET 4.5 is there a collection with a sort method? Are you suggesting I plug into my MS Access VBA code some .NET 4.5 collection? - How would I go about doing that and is that advisable adding this additional dependency?

Not sure how you would build a recordset from the contents of a directory and if that's just as convoluted?

Andy -> What?



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[PHV]

Have a look here:
faq329-3362

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

No there is not native vba sorted collection. IMO bang for the buck, sort your own collection like you are doing. You could build a little more generic function or build a little more generic custom class. Yes .net has native sorted lists, but that is a lot of overhead to build .net libraries if you never done that before. You could use disconnected recordsets, but that does not save a whole lot either in my opinion.

 

[MajP]

For this specific issue you could also use a ListView control. It is nice for showing directory information and it has native sort capability. Also a tree view control (also in the MSCOMCTL library) has a native sort feature and another nice way to show directory information. Both are ActiveX controls so there is that issue when distributing.

 

[1DMF]

Thanks PHV...however...

I'm with you MajP, that disconnected recordset looks even more convoluted than what I'm already doing!

Wouldn't know where to start building .net libraries or how I would then bring them in (as a DLL I'm guessing?) and does that then require everyone to have that particular .NET framework installed and what's the overhead just to enable a sortable collection?

Would seem I was on the right track as VBA doesn't have them and the simplest solution is a basic bubble sort as I have done.

Why doesn't VBA have a sortable collection?




"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[1DMF]

For this specific issue you could also use a ListView control..Both are ActiveX controls so there is that issue when distributing.

Well as you can see from my code

cCol1.Add left(vCode(UBound(vCode)), 7), left(vCode(UBound(vCode)), 7)
cCol2.Add (sPath & sFile), left(vCode(UBound(vCode)), 7)

I don't really want access to / view of the directory as it is.

I strip out the special 7 char code from the file-name and it is that that is the collection 'key' (viewable in the listbox), I use the actual folder path / file-name for the 'value' which is used for the email later when I add the attachments of the selected items from the listbox. (the list box has two columns 1st visible, 2nd not but value bound to 2nd column).

The user was complaining that they weren't in alpha order and the solution I thought would be as easy as setting a sort order on the list box... oh if only!

This simple request became more of a challenge than I expected and was bemused there didn't seem to be a native collection with a sort!


"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[MajP]

A listview can be formatted to look like any listbox. There are several different views available. You can make a simple detail view that you describe.

http://msdn.microsoft.com/en-us/library/windows/desktop/aa511485.aspx

 

[strongm]

>Yes .net has native sorted lists, but that is a lot of overhead to build .net libraries if you never done that before

You are starting from an incorrect premise. You do not have to compile any .NET code at all. There are a couple of ways of directly using .NET's system collections directly from VB(A). For example (derived from my code in a longish thread about sorting thread222-1695412):

[blue]Option Explicit

[green]' Requires a reference to mscorlib.dll
' which is actually a tlb[/green]
Public Sub example()
    Dim Sorter As New ArrayList
    Dim Item As Variant

    [green]' Add a few items[/green]
    Sorter.Add "c"
    Sorter.Add "f"
    Sorter.Add "deep"
    Sorter.Add "e"

    [green]' Alpha sort ascending[/green]
    Call Sorter.Sort

    For Each Item In Sorter
        Debug.Print Item
    Next

End Sub[/blue]

 

[MajP]

Class SortedCollection

'Kind of primitive, but this class will allow you to create a sorted collection. You could add other features to this. Right now when you add it add is sorted ascending. You could provide feature to allow to add ascending or descending. Probably want to add a feature to allow you to pass in a complete collection and return it sorted. Problem want some better sorting options and maybe some faster sorting algorithms.

Option Compare Database
Option Explicit

Private mCollection As New Collection

Public Enum SortDirection
  Asc = 0
  Desc = 1
End Enum
Public Property Get count() As Long
  count = mCollection.count
End Property
Public Function Add(Item As Variant, Optional Key As String = "") As Variant
   Dim i As Long
   Dim CollectionCount As Long
   If Not TypeOf Item Is Object  Then
     Add = Item
   Else
     MsgBox "Only supports value types"
     Exit Function
   End If
   CollectionCount = mCollection.count
   If CollectionCount = 0 Then
     'Single Item
     If Key <> "" Then
      mCollection.Add Item, Key
     Else
      mCollection.Add Item
     End If
   ElseIf mCollection.Item(CollectionCount) <= Item Then
     'Greater Than last item
      If Key <> "" Then
         mCollection.Add Item, Key
       Else
         mCollection.Add Item
       End If
   Else
     For i = 1 To CollectionCount
     'could add a binary search here can do that later
        If mCollection.Item(i) > Item Then
          If Key <> "" Then
            mCollection.Add Item, Key, i
          Else
            mCollection.Add Item, , i
          End If
          Exit For
        End If
      Next i
   End If
End Function
Public Function ToString() As String
  Dim i As Long
  For i = 1 To mCollection.count
    If i = 1 Then
       ToString = mCollection.Item(i)
    Else
      ToString = ToString & vbCrLf & mCollection.Item(i)
    End If
  Next i
End Function
Public Function Item(index As Variant) As Variant
  Item = mCollection.Item(index)
End Function
Public Sub Delete(index As Variant)
   mCollection.Remove index
End Sub
Public Sub Sort(Optional SortDirection As SortDirection = Asc)
    Dim Sort1 As Long
    Dim Sort2 As Long
    Dim TempItem1 As Variant
    Dim TempItem2 As Variant
    Dim CollectionCount As Long
    Dim Swap As Boolean
    CollectionCount = mCollection.count
    On Error GoTo ErrFailed
    For Sort1 = 1 To CollectionCount - 1
        For Sort2 = Sort1 + 1 To CollectionCount
        If SortDirection = Asc Then
                If mCollection.Item(Sort1) > mCollection.Item(Sort2) Then
                    Swap = True
                Else
                    Swap = False
                End If
            Else
                If mCollection.Item(Sort1) < mCollection(Sort2) Then
                    Swap = True
                Else
                    Swap = False
                End If
            End If
            If Swap Then
                'Store the items
                TempItem1 = mCollection.Item(Sort1)
                TempItem2 = mCollection.Item(Sort2)
                'Swap the items over
                mCollection.Add TempItem1, , Sort2
                mCollection.Add TempItem2, , Sort1
                'Delete the original items
                mCollection.Remove Sort1 + 1
                mCollection.Remove Sort2 + 1
            End If
        Next
    Next
    Exit Sub

ErrFailed:
    Debug.Print "Error with CollectionSort: " & Err.Description
    On Error GoTo 0
End Sub

usage

Public Sub TestCollection()
  Dim SC As New SortedCollection
  Dim i As Long
  For i = 1 To 100
    SC.Add (GetRandomLetter & GetRandomLetter & GetRandomLetter)
  Next i
  Debug.Print SC.ToString
  SC.Sort Desc
  Debug.Print
  Debug.Print SC.ToString
End Sub

Public Function GetRandomLetter(Optional Seed As Long = 1) As String
  GetRandomLetter = Chr(Int(26 * Rnd(Seed) + 65))
End Function

Public Function GetRandomDigit(Optional Seed As Long = 1) As Integer
  GetRandomDigit = Int(10 * Rnd(Seed))
End Function

Strongm,
Do you have any other links on using the .net libraries? I would like to play with that some.

 

[strongm]

>Do you have any other links on using the .net libraries? I would like to play with that some

I've never really found much apart from my own posts ... it all comes from the fact that MS packaged a bunch of core .NET functionalty for what they call .net interop. You can find registry entries for most of them starting at HKEY_CLASSES_ROOT\System.AccessViolationException

There's another of my examples here which provides a decent format function for VBS, the main point of which is that it leverages .NETs stringbuilder: thread329-1570966

 

[1DMF]

Wow, thanks Majp, wasn't expecting an entire new class!

Have you had any time to play with the .NET corlib?

It seem the DLL reference is C:\Windows\Microsoft.NET\Framework\v4.0.30319\mscorlib.tlb (on my machine)

So as I suspected, it is relative to the .NET framework installed on everyone's machine, and not all have .NET or definitely not 4+ anyhow.

All things considered, is adding this heavyweight dependency a good idea for this tiny problem on one listbox?

Though I have found the late binding syntax...

Dim Sorter As Object
Set Sorter = CreateObject("System.Collections.ArrayList")

Do you know when the sortable arraylist was added to .NET and likely minimum version needed?



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[strongm]

It is not a heavyweight dependency, it is approx 6mb. The whole framework does not get loaded. The link I provided above covered this point.

The late binding syntax does not use mscorlib.dll, it uses system.dll and that does use the framework. One should note, however, that these days it is pretty likely that the framework will already be running, so not necessarily the issue you might think it is.

The arraylist has been a core part of the CLR since it first appeared, so exists in all frameworks since at least 1.1

 

[1DMF]

It is not a heavyweight dependency, it is approx 6mb.

Installing and updating .NET framework is a heavy weight dependency, it takes ages. I also have no control on rolling out updates as this is managed by an outsourced support company.

The whole framework does not get loaded. The link I provided above covered this point.

OK found that bit, thanks.

Can you point me to some docs that list what collections are available, or are all .net collections exposed?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[1DMF]

Well I finally managed to find the collection I needed :

http://www.robvanderwoude.com/vbstech_data_sortedlist.php

SortedList

So now I don't use the bubble sort function and only one collection.

Thanks Mike!

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[MajP]

I still think I would roll my own sortedcollection class like I proposed. You could mirror the vb.net sortedlist collection if you wanted. You may want to note that this sorts by key and not by item. I think you want to store value types and sort. That class I proposed does that pretty well and you can add other features if you want. You still would only use one collection and full intellisense.