Is there a way to display a function call with all parameters resolved?

[JenniferMurphy]

I have some function calls that have rather complicated parameters. Some are the result of calculations. Others are references to table cells that are, themselves, the result of calculations.

Here's one example:

=BINOM.DIST([@X1]-1,[@N1]-1,ProbWin,[@Cum1])*ProbWin

When debugging, it would be helpful to be able to see the actual values that are being passed. I have a UDF that will display in a cell the text of the expression exactly like it is above. Is there a way that I can display the actual parameters that the function sees? In the above case, that might look like this:

=BINOM.DIST(2,5,0.5,False)*0.5

 

[BSALV]

if the question is concerning a excel formula in your sheet and not in VBA, you can use the Formula>evaluate on the ribbon.
With that one, you see step by step your formula calculating and the values, you asked for.

 

[JenniferMurphy]

if the question is concerning a excel formula in your sheet and not in VBA, you can use the Formula>evaluate on the ribbon.
With that one, you see step by step your formula calculating and the values, you asked for."

I should have mentioned that I do know about Evaluate. It's very helpful for a one-off evaluation. What I was looking for was text string in a nearby cell that showed the actual expression with all variables resolved.

I guess I'll have to "assemble" it manually, like:

="BINOM.DIST(" & [@X1]-1 & ", " & [@N1]-1 & ", " & ProbWin & ", " & [@Cum1] & ")*" & ProbWin

That works, but it does not update if I edit the source expression.

 

[Jaafar Tribak]

If that's the only function you have, why not encapsulate it in a variant UDF and have an optional boolean flag argument as the last parameter of the UDF which will indicate whether you want to return the actual function return value or the individual resolved parameters.

Something like this :

Function BINOM_DIST( _
        number_s As Double, _
        trials As Double, _
        probability_s As Double, _
        cumulative As Boolean, _
        probwin As Double, _
        Optional ByVal ResolvedArguments As Boolean _
    ) As Variant

    BINOM_DIST = CDbl(WorksheetFunction.BINOM_DIST(number_s, trials, probability_s, cumulative) * probwin)
 
    If ResolvedArguments Then
        'Resolved arg values by position:
        'BINOM_DIST = "=BINOM_DIST(" & number_s & "," & trials & "," & probability_s & "," & cumulative & "," & probwin & ")"
 
        'Or full named arguents:
        BINOM_DIST = "=BINOM_DIST(" & _
                                    "number_s:=" & number_s & ", " & _
                                    "trials:=" & trials & ", " & _
                                    "probability_s:=" & probability_s & ", " & _
                                    "cumulative:=" & cumulative & ", " & _
                                    "probwin:=" & probwin & "," & ")"                           
                       
    End If

End Function

So, when the UDF is used in a worksheet cell, and depending on the last optional Boolean argument (Default value = FALSE)

This:
=BINOM_DIST(2,5,0.5,FALSE,0.5)
Will return:
0.15625

An this:
=BINOM_DIST(1, 3, 0.5, FALSE, 0.5,TRUE)
Will return the text string:
=BINOM_DIST(number_s:=1, trials:=3, probability_s:=0.5, cumulative:=False, probwin:=0.5,)

 

[JenniferMurphy]

If that's the only function you have, why not encapsulate it in a variant UDF and have an optional boolean flag argument as the last parameter of the UDF which will indicate whether you want to return the actual function return value or the individual resolved parameters.

Something like this: [actual code above]

So, when the UDF is used in a worksheet cell, and depending on the last optional Boolean argument (Default value = FALSE)

This:
=BINOM_DIST(2,5,0.5,FALSE,0.5)
Will return:
0.15625

An this:
=BINOM_DIST(1, 3, 0.5, FALSE, 0.5,TRUE)
Will return the text string:
=BINOM_DIST(number_s:=1, trials:=3, probability_s:=0.5, cumulative:=False, probwin:=0.5,)

I actually thought about something like this, but was too lazy to work out all the details. You are right that I don't need this facility very often, so taking the trouble to set up a UDF like this might just be worth it.

Thanks for taking the time to work it out. ??