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!

 

[NateO]

Howdy Orange,

I'm relatively certain that I don't follow your second example, and not sure why someone would incorrectly hard code an array as such, but David Braden has an array posted out there to help deal with this:

Sub ShellSort()
    'Dave Braden's code
   Dim i As Long, j As Long, inc As Long, list As Variant
   Dim var As Variant, LowIndex As Integer, HiIndex As Long
   list = [{"B","U","E","P","H"}]
   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
   MsgBox list(1) 'Show 1st Item in array
End Sub

In the thread I was reviewing David was commenting on the removal of some his comments, I have no clue as to what they are, and I can't replace them. Sorry Mr. Braden.

Hope this helps.

 

[ravin_gupta]

Orange,

doyou want to sort the elements as they're added to the object, or will an object be created and then you simply want a sorting function that takes the argument as an argument?

Ravin

 

[orangehairedboy]

Preferably a sorting function...but in all reality it doesn't make a difference in the long run. As long as it can sort long strings like names.

 

[NateO]

Am I in a parallel universe here? The code posted above works for the first example, try testing it, the resulting array list is a sorted array. More examples,

Sub ShellSort()
    'Dave Braden's code
   Dim i As Long, j As Long, inc As Long, list As Variant
   Dim var As Variant, LowIndex As Integer, HiIndex As Long
   list = [{"Nate","Colo","Orange","Ivan"}]
   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
   MsgBox list(4)      'Show 4th Item in array
End Sub

Here's the conversion to a function:

Dim y

Sub test()
ShellSort Array("B", "U", "E", "P", "H")
MsgBox y(0)
End Sub


Function ShellSort(list As Variant)
    '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
   y = list
End Function

It works for numbers too.

 

[orangehairedboy]

Am I in a parallel universe here? The code posted above works for the first example, try testing it, the resulting array list is a sorted array.

Never said it didn't work did I? Or am I in a "parallel universe"?

I was just answering ravin_gupta's question.

 

[NateO]

Never said it didn't work did I? Or am I in a "parallel universe"?

I was just answering ravin_gupta's question.

Well, seeing as you've asked.... By answering that question without saying hey, one way that works can already be found in this thread... Then I suspect you are operating in one parallel from the one I'm in. Why get Ravin spinning his wheels? I don't deal in ESP, tarrot card reading or astrology.

You're welcome.

 

[orangehairedboy]

Simple answer: There is always more than one way.

There was a chance that your way wasn't the best. There was also an equal chance that your way was the best. It is always advantageous in any field for any situation to explore all the options. A simple lesson everyone should learn in childhood. :D

However, I do appreciate your help and I will be using your code as it works great.

Lewis

 

[NateO]

Hi Lewis,

There was a chance that your way wasn't the best.

Agreed. I could chill my tone out a little. I was joking/exaggerating, but I could see it being abrassive, you don't have ESP either.

Actually, I'm all for a better answer/solution! I certainly wasn't going to lock the thread in the event that one could be posted.

Glad to be of help. Have a good one.

 

[orangehairedboy]

Agreed. I could chill my tone out a little. I was joking/exaggerating, but I could see it being abrassive, you don't have ESP either.

Actually, I'm all for a better answer/solution! I certainly wasn't going to lock the thread in the event that one could be posted.

I'm glad to hear that...no hard feelings either way then! Have a good night and thanks again!