Why my array function does not work?

Question

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?

have you put a break line in your function and tried to step through the code? — Scott Holtzman
– Scott Holtzman, Commented Nov 21, 2015 at 16:18

John Coleman · Accepted Answer · 2015-11-21 16:23:05Z

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.

user1016274 · Accepted Answer · 2015-11-22 21:26:07Z

2

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

edited Nov 22, 2015 at 21:26

answered Nov 22, 2015 at 11:48

user1016274

4,2992 gold badges25 silver badges20 bronze badges

2 Comments

Patrick Lepelletier Over a year ago

i like this last method better. But still have some questions : why work with a new array (arrReturn) when you could do it on Arr directly by adding byval on the function parameter; and wouldn't the Ubound be written = UBound(arrReturn,1) , 1 being the 1rst dimension?

user1016274 Over a year ago

1. you need a second array anyway - ByVal is a copy of the original array, just implicitely. "Explicit is better than implicit." 2. with UBound(array, dimension) , dimension is optional and defaults to 1. So all is good. But I'll edit that anyway.

Collectives™ on Stack Overflow

Why my array function does not work?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related