Optimal way of altering multiple values with a function

Question

Functions in VBA at least can only ever return 1 thing. That thing could contain multiple values, but it's still 1 package.

I would like to create a Sub/Function which has an effect on the values of more than 1 variable. As far as I know, these are the 3 ways of doing that:

1. Declare the variables outside the Caller sub

Private val1 As Long, val2 As Long
Sub Caller()
    val1 = 1
    val2 = 2
    valueChanger
    Debug.Print val1 + val2         'Prints 5
End Sub

Sub valueChanger()                  'no need to pass any values, but could pass both byVal
    val1 = val1 + 1
    val2 = val2 + 1
End Sub

2. Pass byRef

Sub Caller()
    Dim val1 As Long, val2 As Long
    val1 = 1
    val2 = 2
    valueChanger val1, val2
    Debug.Print val1 + val2                      'Prints 5
End Sub

Sub valueChanger(ByRef value1 As Long, ByRef value2 As Long)
    value1 = value1 + 1
    value2 = value2 + 1
End Sub

3. Return both in a single "packet"

Sub Caller()
    Dim val1 As Long, val2 As Long, results() As Long
    val1 = 1
    val2 = 2
    results = valueChanger(val1, val2)
    Debug.Print results(1) + results(2)          'Prints 5
End Sub

Function valueChanger(ByVal value1 As Long, ByVal value2 As Long) As Long() 'return an array, could equally return a collection or even a string
    Dim resultVals(1 To 2) As Long
    resultVals(1) = value1 + 1
    resultVals(2) = value2 + 1
    valueChanger = resultVals
End Function

I can see option 3 is probably the most self-contained, option 1 probably the least. Option 3 is likely the most memory-taxing and also requires the result to be split fairly messily.

Personally I choose option 2 as a middle ground, as I think as long as you are aware of the risks of byRef as you are typing the routine, once that's finished you can essentially forget about it as the variables won't be visible anywhere else in your code - unlike in option 1.

But have I missed something? And is any of these approaches standard practice that I should probably follow?

John Coleman John Coleman · Accepted Answer · 2017-08-18T11:50:07

Your third method is conceptually simplest, and thus possibly easier to debug (and less likely to need debugging in the first place). You can simplify the mechanics:

1) Just use Variant to pass arrays to and from functions

2) Use Array() to package the information for return

3) Write a sub which is a sort of inverse to Array(), one that can unpack an array to a list of provided variables (which implicitly uses VBA's default byRef semantics:

Sub UnPack(A As Variant, ParamArray Vars() As Variant)
    'A is a 0-based array of the same length as Vars
    'The elements of A are assigned to the variables in Vars
    Dim i As Long
    For i = 0 To UBound(Vars)
        Vars(i) = A(i)
    Next i
End Sub

Then your valueChanger() could just be:

Function valueChanger(ByVal value1 As Long, ByVal value2 As Long) As Variant
    valueChanger = Array(value1 + 1, value2 + 1)
End Function

Used like:

Sub Caller()
    Dim val1 As Long, val2 As Long, results As Variant
    val1 = 1
    val2 = 2
    results = valueChanger(val1, val2)
    UnPack results, val1, val2
    Debug.Print val1 + val2          'Prints 5
End Sub

Or even just:

Sub Caller()
    Dim val1 As Long, val2 As Long
    val1 = 1
    val2 = 2
    UnPack valueChanger(val1, val2), val1, val2
    Debug.Print val1 + val2          'Prints 5
End Sub

Alternatively, you can implement method 3 by expanding what you mean by a "packet". It typically only makes sense to return multiple values from a function when those values in some sense go together. This suggests that it would be natural to wrap those values in a type or even a class:

'at top of module:
Type Pair
    val1 As Long
    val2 As Long
End Type

Then:

Function valueChanger(p As Pair) As Pair 'can't pass a user-defined type ByVal
    Dim q As Pair
    q.val1 = p.val1 + 1
    q.val2 = p.val2 + 1
    valueChanger = q
End Function