CODEWITHSUNDEEP
excelvbafunctionparameter-passingsubroutine
XCELLGUY
XCELLGUY

Reputation: 319

Using Subroutine for output instead of a function

I thought at this point that the role of functions and subroutines was very clear to me. But now I am not so sure... I see it written all the time

"Functions can return values / subroutines cannot return a value."

and

"a function can only return a single value" (I realize they can return arrays and such too).

But it seems as though I can effectively "return a value from a subroutine" if I pass the "result" variable into the subroutine... Is this considered a "poor practice?" or am I missing some other key concept here...

Method # 1 (Using a Function):

Sub test1()

    Dim x As Integer
    Dim y As Integer
    Dim z As Integer

    x = 2
    y = 3
    z = test2(x, y)

End Sub

Function test2(var1 As Integer, var2 As Integer) As Integer

    test2 = var1 + var2

End Function

Method # 2 (Using a Subroutine):

Sub test3()

    Dim x As Integer
    Dim y As Integer
    Dim z As Integer

    Call test4(x, y, z)

End Sub

Sub test4(var1 As Integer, var2 As Integer, var3 As Integer)

    var1 = 2
    var2 = 3
    var3 = var1 + var2

End Sub

Upvotes: 0

Views: 387

Answers (1)

FunThomas
FunThomas

Reputation: 29592

Usually, it is bad practice to change the value of a parameter. Just look at you examples - it is obvious that your function does something with the 2 parameters and returns a value (which you write to z). In the second example, you don't see what will happen unless you look to the subroutine - and not only to the function definition, you need to read the complete code so that you can tell what parameter will manipulated and what not.

In software development, when you call a subroutine, you don't want to look at this subroutine - and often it is even not available for you. Let the subroutine do it's job, but without any side effects.

Use a function whenever possible, it keeps your code much more readable. Trust me...

There are (very few) cases when you want to receive more than one result from a subroutine. In that case, I would advice to put explicitly the keyword ByRef in front of the parameter (even if in VBA this is technically not necessary because it's the default). And put a comment that tells why it is the case. You will thank yourself when you look at your code weeks, months or years later.

Upvotes: 1

Related Questions