___Function MainUDF(__) ' UDF to be used in a worksheet cell like__ = MainUDF(ArgX)
____ ' any conventional UDF coding
____ '
_______ Evaluate__ "SomeOtherProcedureToDoAnythingAnywhere(ArgY, __)"
____ '
____ ' any conventional UDF coding
____ '
___End Function

___Sub SomeOtherProcedureToDoAnythingAnywhere (__) ' ____ ' Any conventional procedure
____ ' Any conventional procedure coding
____ ' coding to change other cell values
____ '
____ ' Any conventional procedure coding
___End Sub





What’s going on? Here is my take on it, - how I got the slightly modified version of Rick’s discovery
Back to the start of Rick’s Post : Rick reported a report, which was a report of the discovery that hovering over a hyperlink in a cell ( where the first argument of the hyperlink function was a Function ) caused the firing of the Function
Like this: Put this in a normal module
Code:
Function SayHello()  '  '  In any cell type something of this form  ( You will need to change the path to suit where you have yopur workbook )    =HYPERLINK('F:\Excel0202015Jan2016\ExcelFox\BlogsBugs\BugsNovelShortTips\UDFinCellChangeOtherCells\UDF to modify other cells.xls'!Module1.SayHello(),"Hover over this cell to say Hello")
' Stop '     This  Stop  will only work if you navigate to the cell using arrow keys, and then hit  Enter
 MsgBox prompt:="Hello"
End Function
Now type _ =HYPERLINK(SayHello()) _ in a cell, and then (after hitting Enter) hover over the cell with the mouse
http://i.imgur.com/1zydn98.jpg
http://i.imgur.com/5hlZEbU.jpg
That message box should pop up


Two things struck me as strange about that
_ (i) That its happening, that is to say that hovering over the cell causes the firing of the function
_ (ii) The hyperlink function is accepting a procedure name rather than the string reference to where the hyperlink "goes"

The second, _ (ii) , exposed a few memory locations in my brain releasing some thoughts and previous observations of mine…
I have frequently seen that “exposing an interface”, ( “exposing an interface” : a technical term I have learnt is often applied to physically writing, or using, a full string reference to some object or function ), makes it do the main thing it is designed to "do". “Exposing an interface" in Visual Basic triggering functions allowing us to write some beautiful flowing single line codes. I think this is somehow fundamental to how excel works. There is something very fundamental to the workings of Excel whereby a full reference to a procedure or function will somehow be like "Calling" it into action. I get the feeling that the most work and fundamental writing of Excel was done at the beginning, and apparent advancements like the move from the original Excel 4 macros to VBA is somewhat of a cover up, or wrap up. People a lot smarter than me in computing tell me that Excel VBA is not really Object Oriented Programming Visual Basic, but rather an attempt after Excel version 4 to make it look and work as if it was.
The original Excel 4 macros worked by listing the commands. We can still do that, listing them in a Macro 4 worksheet ( For example from a new excel version worksheet hit Ctrl+F11 to see what happens ! )
We also have the availability of them Excel 4 macro commands via fiddling the using of the named range dialogue box such as to put the macro 4 command in place of where the “applies to” range would be. Then when we reference such a named range from a worksheet, the Excel 4 macro is fired.
Its my belief that functions and procedures, ( which could loosely all be called commands , where the actual command is the function or procedure name ), get “put somewhere” and can be got at in different ways…. This leads me on to the next couple of observations

Some of the other observations I made
_a) Fully referencing.
This was less of a surprise to me and is a direct consequence of my last few ramblings.
I can replace the simple procedure reference to a full macro Run syntax like
=HYPERLINK('F:\AFolderOfMine\AnotherFolderOfMine\TheFolderWit hTheFileInIt\UDF to modify other cells.xls'!Module1.SayHello())
( Don’t be surprised if you try that, and Excel reduces it to something of the form
=HYPERLINK('UDF to modify other cells.xls'!Module1.SayHello())
- Excel has a general habit of reducing the full reference to a workbook when that reference is used in the opened workbook of that which the reference is used in )
( You can check the tricky syntax of such procedure run calls, including the argument versions here : https://stackoverflow.com/questions/...12342#59812342 )
_b) Strange non Stoping : Dependency tree and decoupling from the worksheet of a run macro.
You will find that you can fire our simple macro both by the passing over the cell with the mouse, as already discussed, but in addition by using the arrow keys to navigate to the cell, then clicking in the formula bar and hitting Enter. That in itself is no surprise. This latter way, using the Enter key triggers the worksheet re calculation, according to various processes governed by things that come under the Heading of Dependency trees.
What might come as a surprise is the following…. Modify the simple macro to include the Stop and/ or click in the margin to make some brown circle stop points
http://i.imgur.com/xc1QiW4.jpg
The latter way, using the Enter , will get you Stoped in the VB Editor. That is still no surprise.
But hover over the cell with the mouse, .. and you won’t get Stopped!
That was less of a surprise for me, but did help me think that the consideration of the Hyperlink function directly is slightly off course, at least to my way of thinking ….._

Dependency tree and decoupling from the worksheet of a run macro.
_....One of my little theories, already touched on briefly, is that procedures generally are held somewhere and how and exactly , ( to the split second ), when they are run is not always the same. ( I used these thoughts before to discover that another apparently impossible thing could be done, - Passing arguments ByRef using Application Run- https://web.archive.org/web/20190816...comment-205853 )
We mentioned "exposing an interface" triggers functions allowing us to write some beautiful flowing single line codes. To prevent a possible chaos caused by this, Excel has a dependency tree which informs Excel about which cells depend on which others, or equivalently, which cells are precedents for which others. From this tree, Excel constructs a calculation chain. ( Array formulas use this extensively, or rather, the possibility to have them sets the simplest basic structure, and the so called CSE Entry is a way to exclude cells from processes that require a more detailed dependency tree. )..
By examining dependency trees we can find ways to force a reconstruction of the calculation chain, “after the event” , as it were. There are many ways to do this, probably most of them requiring a convoluted workaround like passing memory locations and copying memory blocks.
Simple ways can often be found. For Application Run , Late Binding is a simple workaround.
Although the Hyperlink considerations have slightly distracted me, Rick’s post has shown me something and partially reminded me that Evaluate has more than the ability to process the text string equivalent of a worksheet formula.
It’s rare that Microsoft documentation is on its own helpful, but occasionally there documentation may still contain some snippets, or clues. Form parts of Microsoft documentation on the Evaluate… .."Processes … using the naming convention of Microsoft Excel . " …….
Ricks interest in Evaluate infected me with it a bit, but I only ever used it to return something.
Rick’s post introduced me to the way of using it like
______ Evaluate__ "Process….. using the naming convention of Microsoft Excel "
Its another one of my little theories that using Evaluate will force a reconstruction of the calculation chain removing dependencies on a worksheet, or at least leading them to be in a form of later recalculation. I think this is what we are looking for:
The snag often regarded as leading to the claim that a UDF can only change values in the cell that it is in, is a direct consequence of the way an Excel spreadsheet is updated in the simplest case. In Layman’s terms, “you can’t lift up a rake when your standing on it….”: To aid efficiency a simple UDF is in the simplest calculation change: things are done in something which can approximately regarded as a simple sequential way , ( we may experience it as along columns then down rows ). That simple dependency chain screws us up in trying to change other cell values.
We have already discussed that evaluating a name is a fundamental part at low level of triggering a run. A good thing to try to "remove an awkward worksheet dependence" would be maybe …
__ Evaluate "YouNameIt"

I tried…
Bingo – It works!

Practical example in the next post.

( after those last "Hello" experiments, it might be best to remove or ' comment out that function, as it might interfere with some of the next experiments
Indeed, in general it is a good idea to remove or ' comment out any used test procedures before moving on to other procedures , as occasionally when debugging and testing functions, UDFs can sometimes be fired off erratically or by accident )

Conclusions. What’s going on
I am not sure yet. I only have constant and stable success with changing values. Values are a more fundamental things and are accessible also from closed workbooks using similar reference strings to some discussed here, but the significance of this I am not yet fully clear on. There are parallels to some of my findings with Running of macros and Excel 4 macro commands.
I think it needs a lot of detailed research, including cross referencing in different Excel versions to do this topic full justice.
I will come back to this Thread probably and either edit or post more replies as time goes on.

Ref
https://www.myonlinetraininghub.com/...acro-functions
https://docs.microsoft.com/en-us/off...-recalculation
https://www.excelforum.com/excel-pro...ml#post5265903
https://www.excelforum.com/excel-cha...ml#post4343285
https://excelfox.com/forum/showthrea...ned-Functions)




https://superuser.com/questions/6022...-in-a-differen
https://stackoverflow.com/questions/...ult-in-another