Sorting Arrays or Dictionary Object in VBA

[orangehairedboy]

Hi all,

I've looked all through help and searched this board about sorting arrays and Dictionary objects, but I turned up nothing. Does anyone know how to do this?

Here are samples of the 2 types of things I need to sort:

Letters=array("B","U","E","P","H")

to get B,E,H,P,U

and I need to sort this by key:

Dim d
Set d = CreateObject("Scripting.Dictionary")
d.Add "B", 50
d.Add "A", 100
d.Add "D", 75
d.Add "C", 85

to get A,B,C,D

Thanks!

 

[justletmein]

sorting dictionary

im amazed no one answered your question.
it torques me to no end when you ask 'how do i do this' and someone tries to answer a completely different question simply because they have no clue how to answer the one you posed and then try to pretend like their solution has any merit. instead of just saying 'i dont know' or sitting down and trying to work on the problem you pose, they try to act like theres something WRONG with the way you posed it or the approach youre taking. a dictionary is much faster than an array, seeing how it's a hashed table underneath. if you don't know how to answer the question posed, then don't waste peoples time with your non-solution.

 

[jindon]

Sub Sample()
Dim d As Object, x, i As Long

Set d = CreateObject("Scripting.Dictionary")
d.Add "B", 50
d.Add "A", 100
d.Add "D", 75
d.Add "C", 85

x = d.keys
SortA x, 0, UBound(x)
For i = 0 To UBound(x)
     MsgBox "key = " & x(i) & vbLf & "item = " & d(x(i))
Next
End Sub

Sub SortA(ary, LB, UB)
Dim M As VAriant, temp, i As Long, ii As Long
i = UB : ii = LB
M = ary(Int((LB + UB) / 2))
Do While ii <= i
     Do While ary(ii) < M
          ii = ii + 1
     Loop
     Do While ary(i) > M
          i = i - 1
     Loop
     If ii <= i Then
           temp = ary(ii) : ary(ii) = ary(i) : ary(i) = temp
           ii = ii + 1 : i = i - 1
     End If
Loop
If LB < i Then SortA ary, Lb, i
If ii < LB Then SortA ary, ii, UB
End Sub

 

[Legacy 98055]

im amazed no one answered your question.
it torques me to no end when you ask 'how do i do this' and someone tries to answer a completely different question simply because they have no clue how to answer the one you posed and then try to pretend like their solution has any merit. instead of just saying 'i dont know' or sitting down and trying to work on the problem you pose, they try to act like theres something WRONG with the way you posed it or the approach youre taking. a dictionary is much faster than an array, seeing how it's a hashed table underneath. if you don't know how to answer the question posed, then don't waste peoples time with your non-solution.

If you can provide any merit at all for sorting a collection by key then, by all means, have at it!

I would also love to see an example of sorting a collection by key being faster than sorting an array. You are nuts! Hashtable or not.

This is a message board, not a vending machine. The regulars here tend to enjoy "thinking". In many cases this results in a "better" solution. The OP is obviously using keys as values. He should be using a separate array instead.

 

[jindon]

If you can provide any merit at all for sorting a collection by key then, by all means, have at it!

I would also love to see an example of sorting a collection by key being faster than sorting an array. You are nuts! Hashtable or not.

This is a message board, not a vending machine. The regulars here tend to enjoy "thinking". In many cases this results in a "better" solution. The OP is obviously using keys as values. He should be using a separate array instead.

Absolutely same idea.

What is the point of sorting dictionary keys?
dictionary is the object that we can retrieve item from the key....

I've just posted one example for sorting ARRAY..

 

[fat-tony]

Hi,

I am in a similar position as the Original Poster. I tried this code however it returns:
A
B
D
C

I know its an old thread, but threads never die, they just live on in silicon heaven.

Sub Sample()
Dim d As Object, x, i As Long

Set d = CreateObject("Scripting.Dictionary")
d.Add "B", 50
d.Add "A", 100
d.Add "D", 75
d.Add "C", 85

x = d.keys
SortA x, 0, UBound(x)
For i = 0 To UBound(x)
     MsgBox "key = " & x(i) & vbLf & "item = " & d(x(i))
Next
End Sub

Sub SortA(ary, LB, UB)
Dim M As VAriant, temp, i As Long, ii As Long
i = UB : ii = LB
M = ary(Int((LB + UB) / 2))
Do While ii <= i
     Do While ary(ii) < M
          ii = ii + 1
     Loop
     Do While ary(i) > M
          i = i - 1
     Loop
     If ii <= i Then
           temp = ary(ii) : ary(ii) = ary(i) : ary(i) = temp
           ii = ii + 1 : i = i - 1
     End If
Loop
If LB < i Then SortA ary, Lb, i
If ii < LB Then SortA ary, ii, UB
End Sub

 

[Boisgontier]

Hello,

Sort with array a(0 to n) is not exact if n>55

Sub essaiTri()
  n = 56
  Dim a(): ReDim a(0 To n - 1)
  For i = LBound(a) To UBound(a):  a(i) = "Nom" & Format(n - i, "00000"): Next i
  tt = Timer()
  ShellSort a
  MsgBox Timer() - tt
  [A2].Resize(n) = Application.Transpose(a)
End Sub

Sub ShellSort(list)
    'Dave Braden's code
   Dim i As Long, j As Long, inc As Long
   Dim var As Variant, LowIndex As Integer, HiIndex As Long
   LowIndex = LBound(list):   HiIndex = UBound(list)
   inc = 1
   Do While inc <= HiIndex - LowIndex: inc = 3 * inc + 1: Loop
   Do
      inc = inc \ 3
      For i = LowIndex + inc To HiIndex
         var = list(i)
         j = i
         Do While list(j - inc) > var
            list(j) = list(j - inc)
            j = j - inc
            If j <= inc Then Exit Do
         Loop
         list(j) = var
      Next
   Loop While inc > 1
End Sub

JB