[VBA] "Call"ing functions and subroutines

[AiRiFiEd]

I have always thought that the "Call" reserved word is optional when it comes to calling functions and subroutines.

However, I was attempting to implement my insertion sort function and, somehow, when i tried to pass the array into the function, not using the "Call" reserved word threw an error (if parameter is defined as an array - e.g. ByRef vData() as Double) or failed to update the array (if parameter is defined as variant - e.g. ByRef vData as Variant).

Any idea/explanation for such behaviour? Thank you!

Public Function fnc_SortInsertion(ByRef vData As Variant) As Boolean

    Dim i As Long
    Dim x As Long
    Dim vTemp As Variant
    
    fnc_SortInsertion = False
    
    For i = LBound(vData, 1) + 1 To UBound(vData, 1)
        vTemp = vData(i)
        x = i - 1
        While x > -1 And vData(x) > vTemp
            vData(x + 1) = vData(x)
            
            x = x - 1
        Wend
        vData(x + 1) = vTemp
    Next i

    If Err = 0 Then fnc_SortInsertion = True

End Function

Public Sub sub_testtest()
    Dim vArray(1 To 3) As Double
    
    vArray(1) = 3
    vArray(2) = 10
    vArray(3) = 5
    
    Call fnc_SortInsertion(vArray)
    Debug.Print vArray(3)

End Sub

 

[Yongle]

Your function returns TRUE/FALSE
VBA needs to be told what to do with that value

This works

Debug.Print fnc_SortInsertion(vArray), vArray(3)

and this

If fnc_SortInsertion(vArray) = True Then MsgBox "All OK"

as does this

x = fnc_SortInsertion(vArray)
Debug.Print x, vArray(3)

 

[AiRiFiEd]

hey Yongle thanks for your reply!

but it seems changing the function to a subroutine does not help either if "Call" is not invoked.

further, how does invoking "Call" help to tell excel what to do with the return?

Thanks again!

 

[Yongle]

When Call is used to initiate a function the function's return value is discarded by VBA

 

[RoryA]

You don't need Call but, when you don't use it, don't use brackets when you aren't using the return value. So either:

Call fnc_SortInsertion(vArray)

or:

fnc_SortInsertion vArray

not:

fnc_SortInsertion (vArray)