Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Toledo, Ohio, USA
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How do I sort an Array in VBA

    I've created a list box of values which I'd like sorted for display in the list box in ascending order. Any ideas would be appreciated. Thanks

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Winnipeg, Canada
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I sort an Array in VBA

    Hi jp,

    There is probably some sort routine in VBA, but I'll be darned if I can find it. So, make your own sorting routine,
    by coding a 'bubblesort'.

    This consists of two nested loops, i as the outer loop and j as the inner loop.
    For each iteration of the outer loop, the inner loop j goes through all the elements of the array BELOW the current i position. Each time it compares the two items, and if i is greater than j, it reverses the two values.

    You do this by temporarily storing the i value to a temp variable, let i = j and then let j = tempvar.

    I have NOT tested this on anything, but I wrote it up to give you an idea:

    Private Sub SortList()
    'Bubble sort
    Dim i, j
    Dim Tempdata

    For i = 0 To lstList.ListCount - 1
    For j = i + 1 To lstList.ListCount
    If lstList.ItemData(i) > lstList.ItemData(j) Then
    Tempdata = lstList.ItemData(i)
    lstList.ItemData(i) = lstList.ItemData(j)
    lstList.ItemData(j) = Tempdata
    End If
    Next j
    Next i
    End Sub

    As I say I didn't test it (it's 12:35 am FCOL) but you get the idea. Play with it a bit till you get it right. It's quite inefficient, but if your list is not overly long, it should work quite well.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How do I sort an Array in VBA

    Nope, there's no sort function for an array. A bubble sort is the usual way to do it, although the other possibility is to create a recordset instead of an array, sort the recordset, and then create your values list from the recordset instead of from an array. With ADO, you can create recordsets on the fly by using code similar to that used to create a table in code. The difference here is that you don't need a table and you don't need to save the recordset. You can just use it and throw it away.
    Charlotte

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I sort an Array in VBA

    Charlotte,
    Creating the ADO recordset 'on the fly' sounds very interesting. Could you post some sample code please.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How do I sort an Array in VBA

    Ken Getz has dicussed this in various articles and in the Office 2000 Programmer's Guide. Here's some slightly tweaked code that demonstrates building a recordset "on the fly". It's slower than using an array, but it gives you functionality that arrays lack, so there's a trade off. One big advantage is that you can persist the recordset if you want, so you can open it up again later and use it without having to recreate it.

    <pre>Public Function RecordsetArray()
    'based on Access 2000 Developer's Handbook sample code
    Dim rst As ADODB.Recordset
    Dim intElement As Integer

    'instantiate the recordset
    Set rst = New ADODB.Recordset

    With rst
    'append two fields/"dimensions"
    .Fields.Append "ColorID", adSmallInt
    .Fields.Append "ColorName", adVarChar, 10

    'put data in the recordset
    .Open
    .AddNew Array("ColorID", "ColorName"), _
    Array(1, "Red")
    .AddNew Array("ColorID", "ColorName"), _
    Array(2, "Orange")
    .AddNew Array("ColorID", "ColorName"), _
    Array(3, "Yellow")
    .AddNew Array("ColorID", "ColorName"), _
    Array(4, "Green")
    .AddNew Array("ColorID", "ColorName"), _
    Array(5, "Blue")
    .AddNew Array("ColorID", "ColorName"), _
    Array(6, "Indigo")
    .AddNew Array("ColorID", "ColorName"), _
    Array(7, "Violet")
    'write all pending changes - no save required
    .UpdateBatch

    'Dump the recordset to the Immediate Window
    .MoveFirst
    Do Until .EOF
    Debug.Print !ColorID, !ColorName
    .MoveNext
    Loop
    End With
    rst.Close
    Set rst = Nothing
    End Function</pre>

    Actually, I'm going to attach a zipped database that demonstates the use of this kind of recordset and includes an unbound form based on it so that you can edit, add and remove items from the persisted recordset.
    Attached Files Attached Files
    Charlotte

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I sort an Array in VBA

    Thanks Charlotte, now to convert it to C++ and then pass onto a report developed with Crystal reports.


    ps. One doesn't see the trees for the woods (re 'Toothy').[img]/w3timages/icons/blush.gif[/img]

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How do I sort an Array in VBA

    <hr>now to convert it to C++ and then pass onto a report developed with Crystal reports<hr>
    Not on your life! I don't do C++ and avoid Crystal Reports. However, the ADO code itself will be virtually identical in whatever language you use it in. Only the language-specific constructs will change (and the names, to protect the innocent[img]/S/grin.gif[/img]).
    Charlotte

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I sort an Array in VBA

    You misunderstood me Charlotte. It was meant as a throw away comment. The conversion is my task with your sample code.
    I've also looked at your sample DB. It's pretty neat what it does. I wish everyone would put plenty of comments in code (especially if other people will end up with it).

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How do I sort an Array in VBA

    No, I understood and was answering in kind.[img]/S/grin.gif[/img] As far as the sample DB goes, I try to comment stuff like that to excess because I know it will fall into the hands of those who are still learning. Since I learned originally by picking apart someone else's programs, I try to be as kind and helpful as possible with mine.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •