• How do I sort an Array in VBA

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » How do I sort an Array in VBA

    Author
    Topic
    WSjp2558
    AskWoody Lounger
    March 7, 2001 at 12:28 am #353598

    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

    Reply | Quote
    Viewing 0 reply threads
    Author
    Replies
    • WSkmedgyes
      AskWoody Lounger
      March 7, 2001 at 5:45 am #517647

      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.

      Reply | Quote
      • WScharlotte
        AskWoody Lounger
        March 7, 2001 at 5:56 am #517649

        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.

        Reply | Quote
        • WSAndyAinscow
          AskWoody Lounger
          March 7, 2001 at 6:13 am #517650

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

          Reply | Quote
          • WScharlotte
            AskWoody Lounger
            March 7, 2001 at 7:57 am #517674

            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.

            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

            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.

            Reply | Quote
            • WSAndyAinscow
              AskWoody Lounger
              March 7, 2001 at 9:08 am #517687

              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’).

              Reply | Quote
            • WScharlotte
              AskWoody Lounger
              March 7, 2001 at 2:30 pm #517721

              [indent]

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

              [/indent]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).

              Reply | Quote
            • WSAndyAinscow
              AskWoody Lounger
              March 7, 2001 at 4:05 pm #517734

              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).

              Reply | Quote
            • WScharlotte
              AskWoody Lounger
              March 8, 2001 at 1:59 am #517823

              No, I understood and was answering in kind. 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.

              Reply | Quote
    Viewing 0 reply threads
    Reply To: How do I sort an Array in VBA

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    June 2025
    S M T W T F S
    1234567
    891011121314
    15161718192021
    22232425262728
    2930  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.