Notes tests. ByVal ByRef Application.Run.OnTime Multiple Variable Arguments ByRef ByVal

[DocAElstein]

Use ByRef instead of function return value

Example coding:
In Rem 1 we make a call to two simple conventional Function coding examples . One returns the sum of two given numbers, and the other the difference of the same two numbers.
In Rem 2, we make a call to a function that once again takes in the same two nunbers, but makes no return itself. It additionally takes in ByRef two variables . Effectively then we fill those variables in the function, a coinsequence of effectivley filling the variables passed to the function.

Sub MainRoutine_MakeRoutineWorkLikeAFunctionReturningMultipleValues()
Dim NSum As Long, NDiff As Long  '    https://www.excelfox.com/forum/showthread.php/2404-Notes-tests-ByVal-ByRef-Application-Run-OnTime-Multiple-Variable-Arguments-ByRef-ByVal?p=11881&viewfull=1#post11881

Rem 1  Classic Function use in VBA
 Let NSum = SumNumbers(2, 1)
Debug.Print NSum
 Let NDiff = NumbersDifference(2, 1)
Debug.Print NDiff
Debug.Print

Let NSum = 0: NDiff = 0 '  Empty variables

Rem 2  Unconventional way in VBA to return more than one value from a function or Sub routine
 Call NumbersSumAndDifference(2, 1, NSum, NDiff)
Debug.Print NSum; NDiff

End Sub

Function SumNumbers(ByVal N1 As Long, ByVal N2 As Long) As Long
 Let SumNumbers = N1 + N2
End Function
Function NumbersDifference(ByVal N1 As Long, ByVal N2 As Long) As Long
 Let NumbersDifference = N1 - N2
End Function

Function NumbersSumAndDifference(ByVal N1 As Long, ByVal N2 As Long, ByRef TheSum As Long, ByRef TheDiff As Long)
 Let TheSum = N1 + N2: TheDiff = N1 - N2  ' Effectively the variable  TheSum  is the variable  NSum  from the main  calling routine, and similarly the variable  TheDiff  is  effectively the variable  NDiff  from the main calling routine 
End Function

, output should be

3
1

3 1


Notes:

_(i) ) In these sort of coding arrangements, we may refer to the main routine as the Calling routine, and the others as the Called routines or Called functions

_(ii)a) In this example, Function MainRoutine_MakeRoutineWorkLikeAFunctionReturningM ultipleValues ( ..….. , as it is written, can be a Function , (as it indeed is), but it can also be a Sub, by simply replacing Function with Sub

_(ii)b) For the function version only, you could also give another return in the conventional way by
_ adding a ..… ) As ..…. at the end of the function signature line in the conventional way
, and
_assigning the function name to something within the function, in the conventional way : Let MainRoutine_MakeRoutineWorkLikeAFunctionReturningM ultipleValues = …… )
Just to clarify this last point, the single called function in the following example is used both in the conventional and unconventional way to return in total 3 values: The same two values as the last coding, and an extra string value,
The two results are: The sum, 3 , and the difference, 1
That extra string value is returned in the conventional way.

Sub MainCallingRoutine()
Dim NSum As Long, NDiff As Long

Debug.Print NumbersSumAndDifference(2, 1, NSum, NDiff)
Debug.Print
Debug.Print NSum; NDiff

End Sub

Function NumbersSumAndDifference(ByVal N1 As Long, ByVal N2 As Long, ByRef TheSum As Long, ByRef TheDiff As Long) As String
 Let TheSum = N1 + N2: TheDiff = N1 - N2 ' Effectively the variable  TheSum  is the variable  NSum  from the main  calling routine, and similarly the variable  TheDiff  is  effectively the variable  NDiff  from the main calling routine
 Let NumbersSumAndDifference = "The two results are: The sum, " & TheSum & " , and the difference, " & TheDiff
End Function

The results are

The two results are: The sum, 3 , and the difference, 1

3 1

3 1 is obtained as in the previous example, and the string of text
, The two results are: The sum, 3 , and the difference, 1
, is returned from the called function in the more conventional way



Some performance characteristics of Using ByRef instead of function return value, (with strings)
As discussed, returning a value, such as a string, as the function return value, is the conventional practice. However, using the unconventional ByRef way to return a string in a ByRef parameter is faster.

It's often considered bad programming practice to return values in parameters. Normally procedures should not cause side-effects by modifying their ByRef parameters. But if performance is very important the ByRef trick is worth considering. If using ByRef for that reason I would perhaps write a ' comment to say so.

There is one case where ByRef is slower than ByVal. This happens when passing ByRef to an out-of-process server. The variable has to be marshalled twice, once going into the method and once returning. The implication is to use ByVal for your public server interfaces.*


(* https://www.aivosto.com/articles/stringopt2.html


In the next post we start again from basics , but assuming you understand this and the last post