vba what is the difference between calling a function with 'Call' and without? (array sort function - different results)

Question

This is more of a 'understanding a concept' question than a 'fix code issues' type question...

I have an array containing a list of strings, and a function which will sort these alphabetically (using BubbleSort method).

If I use

Call AlphSortArray(arr)

or

AlphSortArray arr

it works perfectly. But

AlphSortArray(arr)

leaves the items unsorted, and chops off the first in the list...

I've been reading around for a reason and have seen mentioned that it might be something to do with calling byVal vs byRef, but haven't been able to find a clear explanation.

While the code works fine, it would be a huge help if anyone can point me in the right direction so that I understand for future reference!

Many thanks in advance

Context: I've been setting up an excel userform which contains a combobox, where the source is taken from a list of names on a worksheet (the names and number of names are constantly changing based on user inputs). The code currently finds the right range of cells, puts it into an array, alphabetically sorts it and then sets it as the source.

Some of the code:

In the main initialize userform code:

Call AlphSortArray(arrMRange)   'replace with lines above for error/no error
ComboBox1.List = arrMRange      'arrMRange is declared as Variant previously

Then the function:

Dim x As Long, y As Long
Dim strTemp1 As String
For x = LBound(arr) To UBound(arr) - 1
    For y = x + 1 To UBound(arr)
        If UCase(arr(x)) > UCase(arr(y)) Then
            strTemp1 = arr(x)
            arr(x) = arr(y)
            arr(y) = strTemp1
        End If
    Next y
Next x

(bubblesort code adapted from comment in: https://social.msdn.microsoft.com/Forums/en-US/830b42cf-8c97-4aaf-b34b-d860773281f7/sorting-an-array-in-vba-without-excel-function?forum=isvvba)

I don't think using `CALL` affects the `ByVal` vs `ByRef`. IMO, you have a bug in the bubble sort code. — Raj More, Jun 22 '16 at 13:21
Whoa.. I was wrong.. there is a `ByRef` issue at play https://blogs.msdn.microsoft.com/ericlippert/2003/09/15/what-do-you-mean-cannot-use-parentheses/ — Raj More, Jun 22 '16 at 13:23
see this post: http://stackoverflow.com/questions/479891/what-does-the-call-keyword-do-in-vb6 — kttii, Jun 22 '16 at 13:31
I doubt that things are as you describe (though it is hard to know without seeing the actual code). — John Coleman, Jun 22 '16 at 13:31
@JohnColeman good point, I will post some of the code up now - was just trying to keep it short in case I was asking an obvious question :) — Marisa, Jun 22 '16 at 13:32
Another issue -- *bubble sort*??? Why use one of the worst sort algorithms there is? — John Coleman, Jun 22 '16 at 14:45
@JohnColeman yes I had heard it was rather clunky/slow! using as placeholder only while testing some other things, fixing properly now - still pretty new to vba, so many things I don't know :/ — Marisa, Jun 22 '16 at 16:31
@RajMore thanks, that was a really interesting link that went a long way to answering my question — Marisa, Jun 22 '16 at 16:33

score 1 · Answer 1 · answered Jun 22 '16 at 13:34

If you are dealing with a function, then avoid using Call. This works for me:

Sub MAIN2()
    Dim ary(1 To 3) As String, bry
    Dim i As Long

    ary(1) = "zip"
    ary(2) = "AAA"
    ary(3) = "bbb"

    bry = fSort(ary)

    For i = 1 To 3
        MsgBox bry(i)
    Next i
End Sub

Public Function fSort(ByVal InOut) As Variant

    Dim i As Long, J As Long, Low As Long
    Dim Hi As Long, Temp As Variant

    Low = LBound(InOut)
    Hi = UBound(InOut)

    J = (Hi - Low + 1) \ 2
    Do While J > 0
        For i = Low To Hi - J
          If InOut(i) > InOut(i + J) Then
            Temp = InOut(i)
            InOut(i) = InOut(i + J)
            InOut(i + J) = Temp
          End If
        Next i
        For i = Hi - J To Low Step -1
          If InOut(i) > InOut(i + J) Then
            Temp = InOut(i)
            InOut(i) = InOut(i + J)
            InOut(i + J) = Temp
          End If
        Next i
        J = J \ 2
    Loop
    fSort = InOut
End Function

Note:

The array ary() in MAIN2() is not changed by the UDF.

Ah brilliant, yes having the function write into a variable fixed the problem perfectly (I've set to rewrite into same variable: `arrMRange = AlphSortArray(arrMRange)`). And a better looking sort algorithm too! — Marisa, Jun 22 '16 at 16:41
Why exactly should you avoid using call when dealing with functions? — Albin, Jul 13 '23 at 13:55

vba what is the difference between calling a function with 'Call' and without? (array sort function - different results)

1 Answers1