PDA

View Full Version : Function does not output on Cells



MamboKing
07-07-2008, 09:18 AM
This function

=input2Solver(H3, H4)

is into an Excel cell and is meant to simply calculate a value.

I'd need to monitor its evolution by displaying Public variables in other cells, hence I add in its body something like Range("Q2") = myPublicVar.

Results:
the function input2Solver() aborts as soon as it encounters such instructions. And it aborts also when it encounters a Call Sub if that Sub contains such instructions.

Ths point is that I have other functions that are just OK with such display instruction and execute it properly. The difference is that these other functions are not called in Excel cells but are called in VBA modules!

Bottom line:
How can I display public variables on cells during the execution of a function called from a cell?

Bob Phillips
07-07-2008, 09:25 AM
Bottome line: you can't.

Functions are designed to return a result, and if they are called from a worksheet, they cannot manipulate the worksheet environment, no writing to cells, not setting fonts/colours in other cells, etc.

Try Debug.Print

RonMcK
07-07-2008, 09:26 AM
<deleted by author>

MamboKing
07-07-2008, 09:50 AM
XLD,
I do not understand why
- when a function is called from inside the code, it can put data on cells
- while a function called from a cell, cannot do that.

Thanks for the Debug.Print idea but, before trying, the Public var I'm monitoring is a huge numeric matrix that I'm used to put on an excel sheet through a For loop inside functions or subs and then look at it in charts, where I see whether it's good or not.

What could you suggest?

Bob Phillips
07-07-2008, 10:26 AM
You may not understand why, but trust me, it is so.

Bob Phillips
07-07-2008, 10:27 AM
You can always copy-paste the debug.print stuff to a worksheet.

MamboKing
07-07-2008, 11:04 AM
You can always copy-paste the debug.print stuff to a worksheet.

Tried. The Debug.Print is in a For loop and should output ~70,000 numbers.
The Immediate Window, where the Debug.Print puts its data, shows only the last ~200 numbers only.

Any change to work around this and see all ~70,000 numbers?

Bob Phillips
07-07-2008, 11:09 AM
Using 2007? This is just thinking out aloud, but I suppose you could create an array UDF and spit out those results in an array as the UDF result.