Bubble sort Collection of arrays

Question

DataBase is a Collection each element of which is an array of five String-type elements describing some object. I'm trying to sort the collection by a particular element of the array.

At this fragment of code I get

Run-time error 13. Type mismatch

Dim DataBase As New Collection

For i = 1 To DataBase.Count - 1
    For j = i + 1 To DataBase.Count
        If DataBase.Item(i)(1) > DataBase.Item(j)(1) Then
            temp = DataBase(j)
            DataBase.Remove (j)
            DataBase.Add temp, temp, i
        End If
    Next j
Next i

Tried accessing elements of the collection using Collection.Item (Index) and Collection(Index) but cannot get the sorted collection.

Since you are getting a *Type mismatch* error, it might me beneficial to give us more information about the [types](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary "VBA Data type summary - Microsoft Docs") of the variables you are using. I recommend you watch [this video](https://youtu.be/Um2JwZfwoFI "YouTube - Master VBA Debugging in 20 Minutes - Excel Macro Mastery (Paul Kelly)") on VBA debugging, I'm sure it'll help you a lot! — GWD, Jan 28 '23 at 13:23
Since bubble sort is known to be one of the more inefficient sorting methods, why try to implement it at all? — John Coleman, Jan 28 '23 at 13:32
Why not use `ArrayList`? [It has a `Sort` method](https://stackoverflow.com/questions/11504418/excel-vba-quickest-way-to-sort-an-array-of-numbers-in-descending-order/34077228#34077228) — trincot, Jan 28 '23 at 14:54
Why are there 3 arguments supplied to the Collection.Add method? The key should be a string IIRC. — Mathieu Guindon, Jan 28 '23 at 14:55
Also the parentheses around (j) in the remove instruction, are forcing the evaluation of j as an expression, and passing the result of that evaluation to the method; these parentheses should probably be dropped, they're not delimiting an argument list. — Mathieu Guindon, Jan 28 '23 at 14:57

VBasic2008 · Answer 1 · 2023-01-29T01:43:45.920

Sort Collection of Same-Sized Arrays By First Element of Each Array

A Quick Fix

            If DataBase(i)(1) > DataBase(j)(1) Then
                Temp = DataBase(j)
                DataBase.Remove j ' redundant parentheses
                DataBase.Add Temp, , i ' the cause of the type mismatch
            End If

In Detail (A Working Example)

Option Explicit

Sub CollSort()

    Const collCount As Long = 10
    Dim Strs(): Strs = Array("G", "C", "D", "F", "H", "B", "E", "I", "J", "A")
    
    ' Populate the collection with the arrays, and each array's first element
    ' using the strings from the 'Strs' array.
    
    Dim coll As Collection: Set coll = New Collection
    Dim arr() As String: ReDim arr(1 To 5)
    
    Dim n As Long
    
    For n = 1 To collCount
        arr(1) = Strs(n - 1)
        coll.Add arr
    Next n
    
    ' Print populated data (only the first element of each array).
    
    Dim Item As Variant
    
    Debug.Print "Initial"
    For Each Item In coll
        Debug.Print Item(1)
    Next Item

    ' Bubble sort by the first element of each array.
    ' Note that the arrays are being swapped, not their first elements.

    Dim Temp, i As Long, j As Long

    For i = 1 To coll.Count - 1
        For j = i + 1 To coll.Count
            If coll(i)(1) > coll(j)(1) Then
                Temp = coll(j)
                coll.Remove j
                coll.Add Temp, , i
            End If
        Next j
    Next i

    ' Print sorted data (only the first element of each array).
    
    Debug.Print "Sorted"
    For Each Item In coll
        Debug.Print Item(1)
    Next Item

End Sub

Bubble sort Collection of arrays

1 Answers1

Sort Collection of Same-Sized Arrays By First Element of Each Array