Collections and Bubble Sort (maybe)

[LEXCERM]

Hi all.

I have found the following piece of code which finds dupes in a comma delimited string and removes them. This works faultlesly:-

Dim sString As String
Dim MyAr As Variant
Dim Col As New Collection
Dim itm, i

sString = Me.txt_CodeString

MyAr = Split(sString, ",")

For i = LBound(MyAr) To UBound(MyAr)
    On Error Resume Next
    Col.Add Trim(MyAr(i)), CStr(Trim(MyAr(i)))
    On Error GoTo 0
Next i

sString = ""

For Each itm In Col
    sString = sString & "," & itm
Next

sString = Mid(sString, 2)

Me.txt_CodeString = sString

'BubbleSort sString
Call BubbleSort(sString)

I now want to sort each element within the string in ascending order and have this "bubble" sort code:-

  Dim strTemp As String
  Dim i As Long
  Dim j As Long
  Dim lngMin As Long
  Dim lngMax As Long
  lngMin = LBound(arr)
  lngMax = UBound(arr)
  For i = lngMin To lngMax - 1
    For j = i + 1 To lngMax
      If arr(i) > arr(j) Then
        strTemp = arr(i)
        arr(i) = arr(j)
        arr(j) = strTemp
      End If
    Next j
  Next i

I'm sure I have used this before with great effect, but I cannot adapt it here. Keeps erroring at lngMin = LBound(arr) - type mismatch.

Is this the right method to sort and why is my code failing?

Many thanks in advance.

Regards.

 

[arnelgp]

just after split, call first bubble sort before putting in collection
'BubbleSort sString Call BubbleSort(MyAr)

 

[LEXCERM]

Hi arnelgp,

Thanks for replying.

As suggested, I am calling the BubbleSort straight after the split, but still get the Type Mismatch error on this line:-

Dim strTemp As String
Dim i As Long
Dim j As Long
Dim lngMin As Long
Dim lngMax As Long
[SIZE="3"][B]lngMin = LBound(arr)[/B][/SIZE]
lngMax = UBound(arr)
For i = lngMin To lngMax - 1
  For j = i + 1 To lngMax
    If arr(i) > arr(j) Then
      strTemp = arr(i)
      arr(i) = arr(j)
      arr(j) = strTemp
    End If
  Next j
Next i

Code activated here:-

Dim sString As String
Dim MyAr As Variant
Dim Col As New Collection
Dim itm, i

sString = Me.txt_AirlineCodeString

MyAr = Split(sString, ",")

[B][SIZE="3"]Call BubbleSort(sString)[/SIZE][/B]

Kind regards.

 

[arnelgp]

should be:
call BubbleSort(MyAr) 'not sString


i believe you have this function:

sub BubbleSort(ArrayToSort() As Variant)

change it to:

sub BubbleSort(ArrayToSort As Variant)

 

[LEXCERM]

Thank you so much arnelgp, have now got it working.

Have a great day!

 

[gemma-the-husky]

lngMin = LBound(arr)

what is arr?

put option explicit at the top, and this will probably error as a non-declared variable

I would have though it should be declared as

dim MyAr() as string (or variant)

Your declaration doesn't declare it as an array.

 

[LEXCERM]

Hi gemma-the-husky,

Thanks for replying. Yeah, arnelgp said pretty much the same.

I chopped and changed the code so many times, I lost site of the declarations etc. Schoolboy error on my part.

Thanks again.