Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
displaying intermediate results from a function procedure
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
displaying intermediate results from a function procedure
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
displaying intermediate results from a function procedure
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
displaying intermediate results from a function procedure
<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" wrote in message ... 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
displaying intermediate results from a function procedure
On Apr 21, 2:26*am, "Niek Otten" 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" wrote in message ... 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 ) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
displaying intermediate results from a function procedure
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 . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
displaying intermediate results from a function procedure
On 21 Apr., 09:31, Charles Williams
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 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 . Another alternative is to write to a file. A nice example is given he http://log4vba.everage.ca/ I use it quite often to provide an audit trail. Regards, Bernd |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
displaying intermediate results from a function procedure
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
displaying intermediate results from a function procedure
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting intermediate results from Solver Functions... Possible? | Excel Programming | |||
eval of concatenate function not displaying results | Excel Discussion (Misc queries) | |||
Using intermediate results in Worksheet functions | Excel Worksheet Functions | |||
Match function and displaying results | Excel Programming | |||
Programming Array Formulas in VBA - Can they be intermediate results in RAM? | Excel Programming |