Runtime Error on a 2D Bubblesort in Excel VBA array

Question

I have been banging my head (and a few other heads as well on other Excel programming sites) to get a Combobox in a Userform to sort the rows (coming from two columns in the source spreadsheet) in alpha order.

Ideally, I want a 2 dimensional sort, but at this point, will settle for ONE that works.

Currently, the Combobox, when dropped down, reads in part (minus the bullet points, which do NOT appear and are not needed):

• Zoom MRKPayoutPlan
• Chuck PSERSFuture
• Chuck PSERSCurrent

What I want is:

• Chuck PSERSCurrent
• Chuck PSERSFuture
• Zoom MRKPayoutPlan

The first order is derived from the order in which the rows appear in the source worksheet.

At this point, I am getting a Runtime Error '13', Type Mismatch error. Both fields are text fields (one is last name, the other is a classification code- I want to sort first by name).

Below are the two relevant sections of the VBA code. If someone can help me sort this out, I'll buy at least a virtual round of beers. Excel VBA is not my most comfortable area- I can accomplish this in other apps, but the client spec is that this all must run in Excel alone. Thanks in advance.

Private Sub UserForm_Initialize()
   fPath = ThisWorkbook.Path & "\"
   currentRow = 4

   sheetName = Sheet5.Name
   lastRow = Sheets(sheetName).Range("C" & Rows.Count).End(xlUp).Row


    Dim rngUID As Range
    Dim vList

    Set rngUID = Range("vUID")

    With rngUID
        vList = Application.Index(.Cells, .Parent.Evaluate("ROW(" & .Address & ")"), Array(7, 1))
    End With
   vList = BubbleSort2D(vList, 2, 1)

    With ComboBox1
        .ColumnCount = 2
        .ColumnWidths = "100;100"
        .List = vList
    End With

   PopulateControls
End Sub

Public Function BubbleSort2D(Strings, ParamArray SortColumns())
    Dim tempItem
    Dim a                     As Long
    Dim e                     As Long
    Dim f                     As Long
    Dim g                     As Long
    Dim i                     As String
    Dim j                     As String
    Dim m()                   As String
    Dim n
    Dim x As Long
    Dim y As Long
    Dim lngColumn As Long


    e = 1
    n = Strings
    Do While e <> -1

        For a = LBound(Strings) To UBound(Strings) - 1
            For y = LBound(SortColumns) To UBound(SortColumns)
                lngColumn = SortColumns(y)
                i = n(a, lngColumn)
                j = n(a + 1, lngColumn)
                f = StrComp(i, j)
                If f < 0 Then
                    Exit For
                ElseIf f > 0 Then
                    For x = LBound(Strings, 2) To UBound(Strings, 2)
                        tempItem = n(a, x)
                        n(a, x) = n(a + 1, x)
                        n(a + 1, x) = tempItem
                    Next x
                    g = 1
                    Exit For
                End If
            Next y
        Next a
        If g = 1 Then
            e = 1
        Else
            e = -1
        End If

        g = 0
    Loop
    BubbleSort2D = n
End Function

Answer 1

Here is a bubble sort in VBA source.

Public Sub BubbleSort(ByRef sequence As Variant, _
        ByVal lower As Long, ByVal upper As Long)

    Dim upperIt As Long
    For upperIt = upper To lower + 1 Step -1

        Dim hasSwapped As Boolean
        hasSwapped = False

        Dim bubble As Long
        For bubble = lower To upperIt - 1

            If sequence(bubble) > sequence(bubble + 1) Then

                Dim t as Variant
                t = sequence(bubble)

                sequence(bubble) = sequence(bubble + 1)
                sequence(bubble + 1) = t
                hasSwapped = True

            End If

        Next bubble

        If Not hasSwapped Then Exit Sub

    Next upperIt

End Sub

Note that using variable names that specify what they are and do instead of single letters makes it easier to read.

As for the 2D sort. Don't. Sort each array individually then sort the array of arrays using the same method. You will need to provide an abstraction to compare the columns. Do not try to sort them both at the same time. I can't think of a scenario where that is a good idea. If for some reason elements can change their sub array in the 2D array, then flatten it into 1 array, sort that and split it back into a 2D array.

Honestly from what I am understanding of you specific problem. You are going from 1D sequence to a 1D sequence so I think 2D arrays are and unnecessary complication.

Instead use a modified bubble sort routine with the comparison statement,

 If sequence(bubble) > sequence(bubble +1) Then '...

replaced with a custom comparison function

ComboBoxItemCompare(sequence(bubble), sequence(bubble + 1))

that will return True if the first argument should be swapped with the second.