L
Legacy 310940
Guest
Here's a remarkable problem concerning function behaviour of functions when used between worksheets. I've not been able to explain or solve it up to now, despite looking in several books and consulting this and other Excel forums.
It is easy enough to define a macro sub on worksheet S (source) to send calculated data from S to another worksheet D (destination) without destroying or confusing any data which are already on D. Let's call this solution to calculated data transfer between sheets PUSHING. It has a cost, though: you have to tell Excel VBA exactly where to put the collected data, either hard-coded ranges in the code (bad programming habit) or through defined names on the receiving wksht D. However, if you have already ca. 250 names in your application you think quadruply before adding more this stock of names ...
What's the more elegant solution? Right: PULLING the required data FROM the source S TO the destination D. You don't have to hard-code ranges or define range names, so you are flexible in case you want to re-design the destination worksheet D. The downside is, that this solution doesn't work if destination D collects data from several sources S, when the required data aren't from constant fields on S but are themselves calculated on the fly through Excel user functions (i.e. cascaded calls).
Now I couldn't figure out why this is happening, but it seems to have to do with Excel's constant recalculation business behind the scenes. You won't notice the problem if there is only one data pulling function on the destination worksheet D. As soon as there are more, and if you are lucky, one data set will be transferred correctly, the other ones will show a VALUE! error message, because some of the sources couldn't finish their recalculation business. Now I write about it, putting off "automatic calculation" might be a principled solution, but for some reason I don't like this (for me this sounds like cheating, because you never know if the data on D will be the actual ones or not).
Any further ideas or experiences with this issue? Thanks in advance.
Regards
Paulus
It is easy enough to define a macro sub on worksheet S (source) to send calculated data from S to another worksheet D (destination) without destroying or confusing any data which are already on D. Let's call this solution to calculated data transfer between sheets PUSHING. It has a cost, though: you have to tell Excel VBA exactly where to put the collected data, either hard-coded ranges in the code (bad programming habit) or through defined names on the receiving wksht D. However, if you have already ca. 250 names in your application you think quadruply before adding more this stock of names ...
What's the more elegant solution? Right: PULLING the required data FROM the source S TO the destination D. You don't have to hard-code ranges or define range names, so you are flexible in case you want to re-design the destination worksheet D. The downside is, that this solution doesn't work if destination D collects data from several sources S, when the required data aren't from constant fields on S but are themselves calculated on the fly through Excel user functions (i.e. cascaded calls).
Now I couldn't figure out why this is happening, but it seems to have to do with Excel's constant recalculation business behind the scenes. You won't notice the problem if there is only one data pulling function on the destination worksheet D. As soon as there are more, and if you are lucky, one data set will be transferred correctly, the other ones will show a VALUE! error message, because some of the sources couldn't finish their recalculation business. Now I write about it, putting off "automatic calculation" might be a principled solution, but for some reason I don't like this (for me this sounds like cheating, because you never know if the data on D will be the actual ones or not).
Any further ideas or experiences with this issue? Thanks in advance.
Regards
Paulus
