CODEWITHSUNDEEP
vbaexcel
luka5z
luka5z

Reputation: 107

Why my array function does not work?

I tried to write a simple bubble sort function in VBA but it does not work. The code is following

Public Function BubbSort(arr As Variant) As Variant

Dim arrReturn As Variant
arrReturn = Array()
ReDim arrReturn(UBound(arr))
arrReturn = arr.Value

For i = 1 To UBound(arr) - 1
    For j = 1 To UBound(arr) - 1
        If arrReturn(j) > arrReturn(j + 1) Then
            temp = arrReturn(j)
            arrReturn(j) = arrReturn(j + 1)
            arrReturn(j + 1) = temp
        End If
    Next j
Next i

arr = arrReturn

End Function

In Excel I tried to select 4x1 range of cells and insert formula $=BubbSort(A1:A4)$ and press Ctrl+Shift+Enter so it works as array function, but it said "Function has an error". Any help please?

Upvotes: 1

Views: 1877

Answers (2)

user1016274
user1016274

Reputation: 4209

While John Coleman's code will technically work it is fundamentally flawed. You will notice that the outer loop over i is invariant - the loop value is never used.

Bubblesort is indeed inefficient (at least for long sequences) but not as much as the code enforces.
At the end of the inner loop the biggest element will have propagated to the very end of the sequence. So, there is no need to compare it with any previous elements during the next repetitions of the inner loop. If you shorten the inner loop every time by 1 (in total, by the number of outer loop completions so far, i) you reduce the number of loops by 50%: 

Public Function Bubble1(arr As Variant) As Variant
    Dim arrReturn As Variant
    Dim i As Long, j As Long, temp As Variant

    arrReturn = arr.Value
    For i = UBound(arrReturn, 1) To 2 Step -1
        For j = 1 To i - 1
            If arrReturn(j, 1) > arrReturn(j + 1, 1) Then
                temp = arrReturn(j, 1)
                arrReturn(j, 1) = arrReturn(j + 1, 1)
                arrReturn(j + 1, 1) = temp
            End If
        Next j
    Next i
    Bubble1 = arrReturn
End Function

While we're at it we can further reduce the effort by observing that if all elements are sorted (that is, no exchanges have happened in the inner loop) we are finished - further loops are redundant. We can implement this with a flag: 

Public Function Bubble2(arr As Variant) As Variant
    Dim arrReturn As Variant
    Dim i As Long, j As Long, temp As Variant
    Dim sorted As Boolean

    arrReturn = arr.Value
    For i = UBound(arrReturn, 1) To 2 Step -1
        sorted = True
        For j = 1 To i - 1
            If arrReturn(j, 1) > arrReturn(j + 1, 1) Then
                temp = arrReturn(j, 1)
                arrReturn(j, 1) = arrReturn(j + 1, 1)
                arrReturn(j + 1, 1) = temp
                sorted = False
            End If
        Next j
        If sorted Then Exit For
    Next i
    Bubble2 = arrReturn
End Function

Upvotes: 2

John Coleman
John Coleman

Reputation: 52008

If arr is a range then UBound(arr) will throw an error. The way around this is to use the line

arrReturn = arr.Value

instead of just 

arrReturn = arr

and then to use Ubound(arrReturn)

Also -- arrReturn would be a 2-d array rather than a 1-d array. All references to arrReturn should look like e.g. arrReturn(j,1)

Finally -- you aren't returning anything (which is assigning to the function name in VBA).

The following code seems to work (if Bubble sort can ever be described as "working"):

Public Function BubbSort(arr As Variant) As Variant

Dim arrReturn As Variant
Dim i As Long, j As Long, temp As Variant

arrReturn = arr.Value

For i = 1 To UBound(arrReturn) - 1
    For j = 1 To UBound(arrReturn) - 1
        If arrReturn(j, 1) > arrReturn(j + 1, 1) Then
            temp = arrReturn(j, 1)
            arrReturn(j, 1) = arrReturn(j + 1, 1)
            arrReturn(j + 1, 1) = temp
        End If
    Next j
Next i

BubbSort = arrReturn

End Function

The above will only work when passed a range contained in a column. It is possible to make it more flexible to be able to handle either a column range or a row range or a VBA array.

Upvotes: 2

Related Questions