UDF that can change values in cells other than the cell in which the UDF is used

[DocAElstein]

“AdeelSolution2” ( )

(Some investigations)

Me giving a solution to that thread with the ideas from my thread here, is something of strange ironic/ contradictory nature, because the whole point of my thread here is to do the unconventional thing of getting a UDF to change the value in a cell other than the one that it is in, ( as Solution 1 does ) . But Adeel wants the UDF to work in the conventional way, giving a single result in the cell that it is in – it should work as a normal Excel formula in a cell – you type in the formula, which you always see up top in the formula bar, but Excel displays for you the .Value that it gives. (VBA lets you see either, or rather a lot more, in coding, the .Value , a few .Formula ____formats and a few other things )
So…
How about…
Lets try to turn that last solution into working such that the thing we type in a cell, the first thing, returns a value.
( The problem of making Solution 1 write the result in the cell that the formula is in, is that it would overwrite the formula – the formula would vanish – just the final value would be there. So it would work just once )

Currently, the very initial solutions I came up with in url=http://www.excelfox.com/forum/showthread.php/2831-UDF-that-can-change-values-in-cells-other-than-the-cell-in-which-the-UDF-is-used] this main thread of mine here, [/url] were using the Evaluate in a less common form, like this
__ Evaluate __" Do something "
There is no interest or means of getting any result retuned from Evaluate there.

We want to be investigating stuff in the more conventional way, perhaps starting form something like this
Dim vTemp As Variant
_ Let vTemp =__ Evaluate("Do something")

I am not sure what generally will be returned from the second thing to the first thing, in my general arrangement: For all I know at this stage, things might get lost in some virtual space and I will get nothing!! – after all one of the main things behind the general solution is to somehow separate things which are normally together, but for that reason cause conflict problems.

So
Initially, just out of interest, I will make just that one modification , and see what happens. All I am doing there is investigating if Evaluate is giving me anything back at all. In the current situation the Evaluate seems to be kicking off the second thing which is a simple sub routine which does take arguments, but is not intended to give anything back. So the first guess is that vTemp wont get changed, … but I was wrong:
I set a watch on vTemp , and clicked a Stop in the left margin so as to then step debug F8 mode through the first thing, and had a look:


So it seems to be returning a Double of value 0 into . I don’t know what the significance of that is. As I already said, I am not sure what generally will be returned from the second thing to the first thing, in my general arrangement.

_.________________________

Next step is just to change the second thing from a sub routine to a function. The result I see in vTemp is the same as the last time.



Once again, I don’t know what the significance of that is, but one thing to note is that by default, because I specified no function type, I am effectively doing the second thing like
Function _____________ As Variant

_.____________________________________________


Next experiment is to give the first thing function a type, As String



That seems to change the returned thing to an empty string. Once again I am not sure what is going on here, somehow a nothing or empty or zero value is wanted to be returned.
But what is slightly encouraging is that I am influencing what does come back.

_._______________________

It is rather a shot in the dark, this next step: a first attempt now, to include the code line that assigns the final wanted result to the function, which in conventional function use, is always needed to make the function return something.
If I am lucky, this will get caught somehow by the Evaluate in the first thing



Bingo! It seems to have worked. A lucky break I guess, Lol.

I am interested in thinking a bit more about what is going on here, so I will leave this post for now. Possibly I will come back later.


In the next post I will finalise the solution which luckily seemed to come out quicker than I expected.