PDA

View Full Version : A Difficult thing indeed



Nitesh
01-08-2007, 12:27 PM
Hi!,

I had posted a question on returning large arrays from functions. Thanks for all the replies. You guys are great indeed and I really appreciate the help I received.

I have another difficult problem, currently looking impossible.

Is it possible to change the current selection using a VBA function or enter values in other cells using VBA function?

Thanks beforehand because I know that some genious present will definitely solve this.

Bob Phillips
01-08-2007, 12:44 PM
Not if you want to call that function from within the worksheet, such functions cannot change the worksheet or other cells, only return a value to the cell from which it is called.

Nitesh
01-08-2007, 12:53 PM
:( thanks, though i feel sad.....

tstom
01-10-2007, 03:16 AM
Is it possible to change the current selection using a VBA function or enter values in other cells using VBA function?


Yes, it's possible though I have rarely found a use for it. I am curious as to why you need a function to perform editing? The reason I am asking you before taking the time to provide an example is that there may be a better approach...

You can usually get the results you are looking for using the calculation event.

Nitesh
01-10-2007, 05:39 AM
Actually, i am doing some statistical function which should return values in more cells than the one in which formula is initially entered. The problem is that it is not known initially as to how many cells would contain the final values. This will be determined during run-time. Else one could select as many cells and enter the function as an array.

regarding calculation event i am suffering from another problem. everytime anything changes in the file, all the functions are re-calculated, and they are sometimes painfully slow. i tried using application.volatile but it does not help.

mdmackillop
01-10-2007, 09:04 AM
You can prevent recalculation from code with
Application.Calculation = xlManual (remembering to reset it to Automatic)
or via Tool/Options/Calculation

Bob Phillips
01-10-2007, 09:48 AM
Actually, i am doing some statistical function which should return values in more cells than the one in which formula is initially entered. The problem is that it is not known initially as to how many cells would contain the final values. This will be determined during run-time. Else one could select as many cells and enter the function as an array.

regarding calculation event i am suffering from another problem. everytime anything changes in the file, all the functions are re-calculated, and they are sometimes painfully slow. i tried using application.volatile but it does not help.

Application.Volatile will not help, it will make it worse. Those cells using that function will be recalculated every change, regardless of whether they are in any of the changed cells chains.

As to the array, you could always select more cells than you know will be used. The function can caount the calling cells, and any not filled by the main code can then bet set to blanks.

Nitesh
01-10-2007, 11:42 AM
I used Application.Volatile(False) to stop the cells from recalculating. However, this did not work.

Regarding the point on changing selection during function call, finally I too did what you have mentioned. But I wanted something cleaner.

matthewspatrick
01-10-2007, 12:29 PM
As to the array, you could always select more cells than you know will be used. The function can caount the calling cells, and any not filled by the main code can then bet set to blanks.

Exactly. In situations for any array formula where I do not know in advance how many cells I will need, I will select many more than I need, and then use Conditional Formatting to "hide" cells with whatever error comes up in the "unneeded" cells ("hide" = set font color to white or some such thing).

tstom
01-13-2007, 12:12 AM
Nitesh.

http://home.fuse.net/tstom/mosdwt.zip

Here is an example of manipulating cells as a result of a UDF but that do not anchor a UDF. If you have more than a small handful of UDFs, this method may not work well for you.

Patrick's answer is the way to go with unkown returns. A cleaner option is to place your results in a dump sheet and then read selectively using native functions.



regarding calculation event i am suffering from another problem. everytime anything changes in the file, all the functions are re-calculated, and they are sometimes painfully slow.


Most people do not know how to use this event correctly. This event is fired for every calculation performed. There are ways to trap calcuation at differing levels. Formula level, sheet level, workbook level, and application level. Post your code and/or your workbook if possible...