Programming Excel with VBA and .NET

Arguments and Results

I’ve heard some people say "Sub procedures don’t return a value; Function procedures do.” I may have said that myself once or twice, but it’s not exactly true. Actually Sub and Function procedures can both return values through their arguments. Only Function procedures return a value as their result. In other words, only Function procedures can be used on the righthand side of the equals sign (=).

For example, the CubeRoot procedure in Figure 2-5 can return a result and store that result in a variable as shown here:

    x = CubeRoot(42)

You couldn’t do that if it were a Sub procedure. But what if it were? Here’s what CubeRoot might look like if it were rewritten as a Sub (changes are in bold):

    Public Sub CubeRoot2(x As Double, result As Double)
        result = x ^ (1 / 3)
    End Sub

This Sub just returns the result as an argument rather than through the function name. Using the CubeRoot2 procedure is a lot more awkward than using CubeRoot, however:

    ' Use the CubeRoot2 Sub
    Sub TestCubeRoot2( )
        Dim res As Double
        CubeRoot2 42, res
        Debug.Print res
    End Sub

One problem is that it isn’t always clear which argument you are passing in and which argument returns the result—I named the second argument result to make that clearer. It’s more common to use Subs to change arguments when you want the input argument to change to the result, like this:

    ' Change the passed-in argument
    Public Sub GetCubeRoot(x As Double)
        x = x ^ (1 / 3)
    End Sub

Now the Sub changes the value of whatever argument ...