Results 1 to 6 of 6

Thread: Bubble Sort Function

  1. #1
    Senior Member
    Join Date
    Oct 2011
    Posts
    135
    Rep Power
    13

    Bubble Sort Function

    Hi,

    Now in a module insert the following code:
    Code:
    Sub BubbleSort(arr As Variant, Optional numEls As Variant, _
    Optional descending As Boolean)
    
    Dim value As Variant
    Dim index As Long
    Dim firstItem As Long
    Dim indexLimit As Long, lastSwap As Long
    
    ' account for optional arguments
    If IsMissing(numEls) Then numEls = UBound(arr)
    firstItem = LBound(arr)
    lastSwap = numEls
    
    Do
    indexLimit = lastSwap - 1
    lastSwap = 0
    For index = firstItem To indexLimit
    value = arr(index)
    If (value > arr(index + 1)) Xor descending Then
    ' if the items are not in order, swap them
    arr(index) = arr(index + 1)
    arr(index + 1) = value
    lastSwap = index
    End If
    Next
    Loop While lastSwap
    End Sub
    How can I get an order of an array

    Code:
    arr = ZonaSenzadoppi(Foglio3.Range("A3:A" & EndRow))
    I'm missing something in the sense I have just created an array, here I have a list (OK)

    In the next line (as Userform)
    Code:
    BubbleSort arr
    Here is blocked ...
    Code:
    value = arr(index)

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Hi

    Try

    Code:
    BubbleSort Application.Transpose(arr)
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Senior Member
    Join Date
    Oct 2011
    Posts
    135
    Rep Power
    13
    Hi,

    Now the error disappears but the list is not ordered

    Code:
        EndRow = Foglio2.Cells(Rows.Count, 22).End(xlUp).Row
    '---Qui creo un array con i valori univoci dei titoli estratti sul foglio: Dati
        arr = ZonaSenzadoppi(Foglio2.Range("V" & RigaFiltro + 11 & ":V" & EndRow))
        Range("a10:A100").value = arr
    '    BubbleSort arr
        BubbleSort Application.Transpose(arr)
        Range("b10:b100").value = arr
    A10 B10
    72 72
    75 75
    47 47
    46 46
    15 15
    65 65
    95 95
    85 85
    68 68
    64 64
    5 5
    98 98
    90 90
    86 86

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Hi,

    Make it a function

    Code:
    Function BubbleSort(arr As Variant, Optional numEls As Variant, _
                                    Optional descending As Boolean)
    
    Dim value As Variant
    Dim index As Long
    Dim firstItem As Long
    Dim indexLimit As Long, lastSwap As Long
    
    ' account for optional arguments
    If IsMissing(numEls) Then numEls = UBound(arr)
    firstItem = LBound(arr)
    lastSwap = numEls
    
    Do
        indexLimit = lastSwap - 1
        lastSwap = 0
        For index = firstItem To indexLimit
            value = arr(index)
            If (value > arr(index + 1)) Xor descending Then
                ' if the items are not in order, swap them
                arr(index) = arr(index + 1)
                arr(index + 1) = value
                lastSwap = index
        End If
        Next
    Loop While lastSwap
    BubbleSort = arr
    End Function
    and call like

    Code:
    Sub test()
        
        Dim a, b
        
        a = [a10:a23]
        
        b = BubbleSort(Application.Transpose(a))
        
        [b10:b23] = Application.Transpose(b)
    
    End Sub
    HTH
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Senior Member
    Join Date
    Oct 2011
    Posts
    135
    Rep Power
    13
    Hi,

    Great now I have completed the cycle and successfully loaded the data list

    thank aid

  6. #6
    Junior Member sa.1985's Avatar
    Join Date
    Nov 2011
    Posts
    3
    Rep Power
    0

Similar Threads

  1. UDF (user defined function) replacement for Excel's DATEDIF function
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 21
    Last Post: 03-07-2015, 09:47 PM
  2. Bubble Chart
    By Rajan_Verma in forum Rajan Verma's Corner
    Replies: 1
    Last Post: 09-04-2012, 09:01 AM
  3. Create Bubble Chart Automatically
    By Excel Fox in forum Download Center
    Replies: 7
    Last Post: 08-12-2012, 10:39 PM
  4. Sort Data When a Header Is Clicked
    By Rasm in forum Excel Help
    Replies: 9
    Last Post: 08-01-2012, 06:46 AM
  5. Sort Worksheet by Color VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 10-25-2011, 02:25 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •