Function passed as an argument to get its value

Question

I recently discovered this way of doing in VBA, you populate a function value by passing it as an argument of another function, and then, when the parameter of this other function is filled, its link to the first function value.

EDIT FOR CLARIFICATION : This idea explained by the teacher was that sometimes, you have a complicated function and then we can extract several informations from the result. And those functions are meant to be used in excel, so instead of letting people calling the complicated one, you create "dummy" functions, like result1 et and result2, and it will be filled with the value we want.

Here is the code (edited, i forget things sorry):

Function f(* a lot of parameters *, Optional result1 As Variant, Optional result2 as Variant) as Boolean
    raw_result = * a lot of calculus *
    result1 = raw_result
    result2 = -raw_result
    f = True
End Function

Function result1(* same parameters than f *) as Double
    ' I will give result1
    Call f(* same parameters than f *, result1)
End Function

Function result2(* same parameters than f *) as Double
    'Two coma on purpose to access the second optional parameters
    Call f(* same parameters than f *,, result2)
End Function

Here, if i do result1(* same parameter than f*), it will give me raw_result value.

What is the theory behind this concept, how does it work and is it only a VBA thing ?

Answer 1

I changed it into an example that can be run:

Option Explicit

Sub Test()
    Debug.Print result1(1, 2, 3)
    Debug.Print result2(1, 2, 3)
End Sub


Function f(a, b, c, Optional result1 As Variant, Optional result2 As Variant) As Boolean
    Dim raw_result
    raw_result = a + b + c
    result1 = raw_result
    result2 = -raw_result
    f = True
End Function

Function result1(a, b, c) As Double
    ' I will give result1
     Call f(a, b, c, result1)
End Function

Function result2(a, b, c) As Double
    'Two coma on purpose to access the second optional parameters
    Call f(a, b, c, , result2)
End Function

In this case result1 and result2 are both submitted ByRef (which is the default in VBA) and not ByVal.

This line

Function f(a, b, c, Optional result1 As Variant, Optional result2 As Variant) As Boolean

is (by default) exactly the same like

Function f(a, b, c, Optional ByRef result1 As Variant, Optional ByRef result2 As Variant) As Boolean

if you change it to ByVal

Function f(a, b, c, Optional ByVal result1 As Variant, Optional ByVal result2 As Variant) As Boolean

then the result of Test() would be 0 because nothingt can be returned in result1 and result2 (no reference).

Here is another example

Sub TestIt()
    Dim VarA As Long
    VarA = 1

    Dim VarB As Long
    VarB = 2

    MyProcedure VarA, VarB '≙ 1, 2

    Debug.Print VarA, VarB 'output is 10, 2
End Sub

Sub MyProcedure(ByRef ParamA As Long, ByVal ParamB As Long)
    ParamA = 10 'will be returned to VarA because it is referenced (ByRef)
    ParamB = 20 'will NOT be returned to VarB because it is NOT referenced.
End Sub

The difference is that …

• if you submit a variable ByRef like ParamA then your procedure (or function) will return any changes of ParamA to your variable VarA.

• if you submit a variable ByVal you don't submit a variable but only its value. Therefore your procedure (or function) cannot return any changes.

Answer 2

I am not sure what you are meaning to do here, but the function is nothing but an unnecessarily nested function. The g in the second function is not used whatsoever in its execution, and neither is b as optional parameter.

For example what happens for g(3): With a replaced by "3"

Function g("3" as integer)
    g = f("3", "blank")
End Function

Function f("3" As Integer, Optional "Blank" As Variant)
    b = "3" * 2
    f = b (=6)
End Function

Meaning when returning to the first function, your answer is 6.

For clarification: g will hold no value unless everything after = is evaluated. Then after this is evaluated, g will hold a value, but it is not re-evaluated as the function will run only once.

Despite this, even if you were to pass g as 6 for example, it is discarded by the second function (where it is called b) as its first action is to assign a new value to b, overwriting whatever value it held.

Edit: As the question change, my answer will have to change as well. The issue with what you're doing is that whichever you call (result1 or result2) this doesn't give a different result. Whichever way you call function f, and whatever you do inside of it, the only thing it will return is what you set f as, in this case True. So you can do a plethora of calculations in both result1 and result2, the only return you will get from calling either of these will be "True".

This way of nesting functions is not the correct way to get different results from a function. It can be done however, by declaring result1 and result2 in your f function as public, and accessing them later on for example.

Answer 3

In VBA, within a function you can access/assign a value to the function name and no you cannot pass an actual function like you would do in C# Func<T1, T2, TResult>

i.e.

Function g(a as integer)
    'Here you are allowed do read or write values to the function name g.

    'g = 10 ' assigning a return value

    'debug.print g 'Now reading the value


    g = f(a,g) ' here you are not passing a function as parameter. You are passing the return value of g as a parameter. Which in your case g would be empty like. g = f(3, empty) 
End Function