Reputation: 452
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 ?
Upvotes: 0
Views: 529
Reputation: 57743
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.
Upvotes: 2
Reputation: 2309
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.
Upvotes: 1
Reputation: 5917
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
Upvotes: 0