Some other notes for a Thread post , I forgot where, ignore for now....

Some other notes for a Thread post , I forgot where


Put this coding in a normal code module

Now type the following in any cell , ( other than cell (A2) ) , and hit enter

If this works for you, then in cell A2 you will get the text, Changed by UDF in cell A2


The following information may be difficult for you to understand, but possibly it is useful to include in this Thread for others…
What you are wanting to do is often regarded as impossible. For example, in post #2 , https://www.myonlinetraininghub.com/...unction#p18256 , Veloria, she did say…. you can't select another cell in a UDF called from a cell ….
More commonly we hear it said…. a UDF can only change values in the cell that it is in ….
That is not true, mostly.
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 ).
We know that **exposing an interface in Visual Basic 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
By examining dependency trees we can find way 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.
A few people, Rick Rothstein, for example, have noticed that we can achieve the same very simply by exposing the UDF interface within a simple Hyperlink
=Hyperlink(MyUDF())
It really is as simple as that.

Exploring the working of that in more detail
**Calling macros is linked into range names , and names in general, as we see by one method used to run Excel 4 Macros: Named Ranges and string references path links are synonymous.
Although there is no documentation on this, it is a direct consequence that a function which requires a string reference will also take a name and result in exposing an interface. That’s what’s going on here.
**This is so fundamental to the working of Excel , and cannot be so easily changed, and explains for example, why we still have many novel solutions available to us via Excel 4 Macros. Its unlikely therefore to not work in newer versions, but as a last disclaimer, I should say I have not tested in newer versions.
But I can do, and have done , this consistently in my Excel 2002 to 2013 working on a number of different computers with operating systems from XP to Windows 10
This means that in general something of the following works, and we have no restrictions on our UDF changing values of any cells anywhere
=Hyperlink('C:Mypath\MyWorkbook.xls'!Module1. MyUDF())
Excel is good at guessing what we mean when we miss things out, and so we can , in the practice, reduce that to
=Hyperlink(MyUDF())

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

_._____________________________-

Let me bring that all back to your specific example.
First you create this UDF