How do I sort an Array in VBA

Topic

New Reply

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

Replies

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

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

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

Reply | Quote

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

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

[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

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

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