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

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

>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

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.

"Charles Williams" wrote:

> 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
>
> >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
> .
>

<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

"Don McC" <(E-Mail Removed)> wrote in message
newsBC56209-FDA8-420B-AB5D-(E-Mail Removed)...
> 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.
>
> "Charles Williams" wrote:
>
>> 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
>>
>> >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
>> .
>>

On Apr 21, 2:26*am, "Niek Otten" <nicol...@xs4all.nl> wrote:
> <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
>
> "Don McC" <aikini...@hotmail.com> wrote in message
>
> newsBC56209-FDA8-420B-AB5D-(E-Mail Removed)...
>
>
>
> > 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.
>
> > "Charles Williams" wrote:
>
> >> 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
>
> >> >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
> >> .- Hide quoted text -
>
> - Show quoted text -

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

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

>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.
>
>"Charles Williams" wrote:
>
>> 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
>>
>> >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
>> .
>>

On 21 Apr., 09:31, Charles Williams <Char...@DecisionModels.com>
wrote:
> 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
>
> >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 supposethe
> >other possible option is to make each variable that is calculated in thecode
> >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.
>
> >"Charles Williams" wrote:
>
> >> 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
>
> >> >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'tseem 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 amcalling
> >> >the function procedure from,
>
> >> >Thanks
> >> .

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

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.

"Don McC" wrote:

> 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

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

Charles

>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.
>
>"Don McC" wrote:
>
>> 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