displaying intermediate results from a function procedure

[Don McC]

Is there a way to write out to a worksheet intermediate (internal) variables
that are calculated in a function procedure. I have tried writing a sub
procedure that is called from the function procedure but that doesn't seem to
work. Makes the calling function not work and I get a #VALUE instead. In
that iteration, I was trying to print to the same worksheet that I am calling
the function procedure from,

Thanks

 

[Charles Williams]

A function that is called from a cell can only return a valaue to the
calling cell and is not allowed to modify any other cell.

If you want to see the intermediate values you could:
- add Debug.Print statements to write values to the Immediate Window
- or show the locals window and add a breakpoint to the the function
- add MsgBox statements to the function

regards
Charles

 

[Don McC]

There is NO way to get back those internal variables to a sheet even if its
not the sheet from which the FUNCTION is called?? If I made some of the
internal local variables Module level variables, wouldn't that make them
available to other procedures? In this case, displaying the internal
variables in a MSG box or in a programming window is insufficient. Even
though the object of the FUNCTION procedure is the ultimate goal variable,
many of the other internal variables are important to know. I suppose the
other possible option is to make each variable that is calculated in the code
its own FUNCTION and then call each Function from the spreadsheet. But that
really won't work in this case because the FUNCTION I have created is
iterative and I only want the converged internal results and they have to be
in the controlling iterative Function.

 

[Niek Otten]

<There is NO way to get back those internal variables to a sheet..?>

Indeed.
Charles gave you about all the options there are.

BTW, if the function is iterative, how did you intend to organize writing
results to a sheet?

Personally, I think setting a break-point and watching what happens to the
variables gives most insight to how the function evaluates to its result

 

[Javed]

<There is NO way to get back those internal variables to a sheet..?>

Indeed.
Charles gave you about all the options there are.

BTW, if the function is iterative, how did you intend to organize writing
results to a sheet?

Personally, I think setting a break-point and watching what happens to the
variables gives most insight to how the function evaluates to its result

--
Kind regards,

Niek Otten
Microsoft MVP - Excel







- Show quoted text -

You can call the function from another sub and then use F8 (Stepping )

 

[Charles Williams]

Another alternative is to make the function return an array of values.
Then you could array-enter (Control-shift-enter) the function into a
range of cells that is the same size and shape as the array.

Charles

 

[Bernd P]

Another alternative is to make the function return an array of values.
Then you could array-enter (Control-shift-enter) the function into a
range of cells that is the same size and shape as the array.

Charles

Another alternative is to write to a file. A nice example is given
here:
http://log4vba.everage.ca/

I use it quite often to provide an audit trail.

Regards,
Bernd

 

[Don McC]

and of course there is the brute force method (which I ended up using). This
calculation is 400 lines long and has three nested do loops and many
internally dimensioned variables. But since there is no other way to get the
individual variables out (there are 21 additional variables I want to display
on the worksheet), I just duplicated the FUNCTION procedure and renamed it so
I could suck that variable out. Slows the execution down a bit, but that's
really the only way I could see to get these additional vairables out. Not
elegant but it works.

 

[Charles Williams]

If you only want 21 values then its sounds like a good candidate for
the array function approach I suggested.

Charles