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

[DocAElstein]

Spare post for later


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg.9hI1CQJMLLo9hWn2pGBe SS (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg.9hI1CQJMLLo9hWn2pGBe SS)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzkRujoMw9PblmXDQ14AaABAg.9hJRnEjxQrd9hJoCjomN I2 (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzkRujoMw9PblmXDQ14AaABAg.9hJRnEjxQrd9hJoCjomN I2)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzPZbG7OvUkh35nXDd4AaABAg.9hJOZEEZa6p9hJqLC7El-w (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzPZbG7OvUkh35nXDd4AaABAg.9hJOZEEZa6p9hJqLC7El-w)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwUcEpm8u6ZW3uOHXx4AaABAg.9hIlxxGY7t49hJsB2PWx C4 (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwUcEpm8u6ZW3uOHXx4AaABAg.9hIlxxGY7t49hJsB2PWx C4)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyvDj6NWT1Gxyy2JyR4AaABAg.9hIKlNPeqDn9hJskm92n p6 (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyvDj6NWT1Gxyy2JyR4AaABAg.9hIKlNPeqDn9hJskm92n p6)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugwy7qx_kG9iUmMVO_F4AaABAg.9hI2IGUdmTW9hJuyaQaw qx (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugwy7qx_kG9iUmMVO_F4AaABAg.9hI2IGUdmTW9hJuyaQaw qx)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg.9hI1CQJMLLo9hJwTB9Jl ob (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg.9hI1CQJMLLo9hJwTB9Jl ob)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyyQWYVP1OnCqavb-x4AaABAg (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyyQWYVP1OnCqavb-x4AaABAg)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwJKKmExZ1FdZVDJf54AaABAg (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwJKKmExZ1FdZVDJf54AaABAg)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugz_p0kVGrLntPtYzCt4AaABAg (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugz_p0kVGrLntPtYzCt4AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

UDF that can change values in cells other than the cell in which the UDF is used
This Tips Thread was inspired by this one posted by Rick Rothstein (https://excelfox.com/forum/showthread.php/1519-Breaking-the-rules-for-UDF-s-(User-Defined-Functions))
I have referred to that posting from Rick quite a few times when showing people how to use a UDF to do things with a UDF in cells other than that in which the UDF is, and mostly pretty impressed they have been too. ( Those that weren’t impressed were those that did not believe it and never tried…. )

I finally got around to trying to get my head around this, and here is my take on it. If you are only interested in seeing the working examples, then jump to post 3 (https://excelfox.com/forum/showthread.php/2707-UDF-that-can-change-values-in-cells-other-than-the-cell-in-which-the-UDF-is-used?p=15263&viewfull=1#post15263)

If you are familiar with Ricks ways of doing this, then basically I am using his solutions but just not using the hyperlink. There’s not much more to it then that. I came there from a long winded way which I am discussing in the next post, but the key to it working is based on, or using, the way Rick used the Evaluate within the coding for the UDF that is in the worksheet

_______ Evaluate__ "SomeOtherProcedureToDoAnythingAnywhere(ArgY, __)"


Here again below, the solution a bit more fully: Below is the coding as you would write it in a normal code module , assuming that the Function MainUDF( ) is the UDF which you would use in the usual formula type way in a worksheet cell like __ = MainUDF( )
http://i.imgur.com/58IFQoQ.jpg

___Function MainUDF(__) ' UDF to be used in a worksheet cell like__ = MainUDF(ArgX)
____ ' any coding to do anything not related to changing things in the worksheet in which the UDF is used
____ '
_______ Evaluate__ "SomeOtherProcedureToDoAnythingAnywhere(ArgY, __)"
____ '
____ ' any coding to do anything not related to changing things in the worksheet in which the UDF is used
____ '
___End Function





Simplest Working Example:
The simplest example is something like the following:
Copy both procedures to a normal code module , …_

' Paste both procedures in a normal code module, then type =ChangeNextCell() in any cell followed by Enter
Function ChangeNextCell()
' Call NextCell ' This wont work. We will be on the same dependancy in the called procedure and cell dependancies are already calculated so attempts to access cells will be screwed up
Evaluate "NextCell()" ' The dependance tree is recalculated for the procedure NextCell() which is excecuted from a copy of the procedure not dependant on the spreadsheet update cycle in progress for the excecution of the function ChangeNextCell()
End Function
Sub NextCell()
ActiveCell.Offset(0, 1).Value = "Next cell." ' Excel has a memory of the last Active Cell and does not rely on dependance to the spreadsheet for this macro to run
End Sub
_... then type in any cell the function _ =ChangeNextCell() _ then _ Enter

ChangeNextCell.JPG : http://i.imgur.com/nqHHEfb.jpg https://imgur.com/nqHHEfb
https://i.imgur.com/nqHHEfb.jpg

ChangeNext Cell.JPG : http://i.imgur.com/iAQFUrj.jpg https://imgur.com/iAQFUrj
https://i.imgur.com/iAQFUrj.jpg

Change Next Cell.JPG : http://i.imgur.com/V7Lowxp.jpg https://imgur.com/V7Lowxp
https://i.imgur.com/V7Lowxp.jpg



In the next post some attempt to examine what’s going on. It is not complete; I may need to come back again on this one

[DocAElstein]

___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 (https://excelfox.com/forum/showthread.php/1519-Breaking-the-rules-for-UDF-s-(User-Defined-Functions)) : 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

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\BlogsB ugs\BugsNovelShortTips\UDFinCellChangeOtherCells\U DF 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\T heFolderWithTheFileInIt\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/31439866/multiple-variable-arguments-to-application-ontime/59812342#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/20190816162718/http://excelmatters.com/2017/04/07/passing-arguments-byref-using-run/#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 (https://excelfox.com/forum/showthread.php/1519-Breaking-the-rules-for-UDF-s-(User-Defined-Functions)) 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 (https://excelfox.com/forum/showthread.php/1519-Breaking-the-rules-for-UDF-s-(User-Defined-Functions)) 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/excel-4-macro-functions
https://docs.microsoft.com/en-us/office/client-developer/excel/excel-recalculation
https://www.excelforum.com/excel-programming-vba-macros/1303563-function-sub-exiting-early-except-when-called-from-developer-tab.html#post5265903
https://www.excelforum.com/excel-charting-and-pivots/1131728-trigger-a-macro-or-worksheet-event-with-a-udf.html#post4343285
https://excelfox.com/forum/showthread.php/1519-Breaking-the-rules-for-UDF-s-(User-Defined-Functions)




https://superuser.com/questions/602216/how-do-you-write-an-excel-formula-that-will-paste-a-specific-value-in-a-differen
https://stackoverflow.com/questions/41624463/excel-calculation-in-one-cell-result-in-another

[DocAElstein]

Post 3 of 3 ... 4 ... 5 etc

Here are some simple working examples. I will probably edit and add to them as time goes on. So far I have tried all these macros and get similar results in Excel 2002 2003 2007 2010 2013 in operating systems of XP Vista Windows 7 Windows 10
I will come back to this Thread probably and either edit or post more replies as time goes on.
Any feedback in particular for results in newer Excel versions would be very welcome

(Note: In general it is a good idea to remove or ' comment out any test procedures before moving on to test other procedures, as occasionally when debugging and testing functions, UDFs can sometimes be fired off erratically or by accident )

If you are familiar with Ricks ways of doing this, then basically I am using his solutions but just not using the hyperlink. There’s not much more to it then that. I came there from a long winded way, explained in post #1, but the key to it working is based on, or using, the way Rick used the Evaluate within the coding for the UDF that is in the worksheet

___________ Evaluate "SomeOtherProcedureToDoAnythingAnywhere(Arg _ , _ )"

[DocAElstein]

Simplest Examples: using a UDF to change the values of other cells
Doing a P in a few cells, the number determined by some cell value.
Consider that a number in some arbitrary cell , say A2 , should determine how many cells in column C get a P in them.
In addition , cell D2 should be a message telling you all about it. This latter requirement would be a normal simple use of either a simple formula or a very simple UDF formula in the cell D2
But the requirement of putting a certain number of Ps down column C would normally be regarded as one requiring
_ a complicated array formula, and that formula would need to extend as far down as the likely maximum wanted number of Ps
or
_ the use of VBA, possibly event coding,
or
_ human interaction to manually do a P in some cells.
_
But we can do it with the simple UDF formula which we would use in the cell D2 , if that UDF also uses an
_ Evaluate "ProcedureToPutThePsIn"
code line in it which will set off the procedure with the name _ ProcedureToPutThePsIn
ProcedureToPutThePsIn can be any normal procedure, ( with a few restrictions. )
_
Solution:
Paste these two codes into a normal code module.

' Paste these two codes into a normal code module. The type in cell D2 =PInCells(A2) then hit Enter. If you now type a number into cell A2 then both the procedures above will excecute
Function PInCells(ByVal Rng As Range) As String ' The function can be thought of as a "variable" that gets filled with a string ( below in the last line** ) , so it needs to be of string type
Dim Nmbr As Long: Let Nmbr = Rng.Value ' A typical bit of coding in the UDF
Evaluate "PutInCells(" & Nmbr & ")" ' This is the special Evaluate "ProcedureToPutThePsIn" piece which helps us contradict the falsly held belief thatz
Let PInCells = "You did " & Nmbr & " Ps in column C" ' A typical piece of coding often towards the end in a UDF giving the function "variable" the return values **
End Function
Sub PutInCells(ByVal Nbr As Long) ' This is a simple normal procedure. It takes in a number which it then uses to determing the size of the range to apply a single value to
Stop ' This won't work. it will be ignored
ActiveSheet.Range("C1:C20").ClearContents ' this wont work. it will be ignored
Let ActiveSheet.Range("C1:C20").Value = "" ' this is a workaround to achieve what the last line would normally do
Let ActiveSheet.Range("C1:C" & Nbr & "").Value = "P"
End Sub

Now type in cell D2 =PInCells(A2) and then hit Enter.
Row\ColCDE
1

2=PInCells(A2)

3

If you now type a number into cell A2 then both the procedures above will execute. For example typing 2 in cell A2 you will get
Row\ColABCDE
1P

2
2PYou did 2 Ps in column C

3



Rick’s example (https://excelfox.com/forum/showthread.php/1519-Breaking-the-rules-for-UDF-s-(User-Defined-Functions))
The main difference, ( and the only significant difference in my version of Rick’s coding ) is that here there is no use of the Excel Hyperlink function
Put these macros in a normal code module

' Put these macros in a normal code module. Now put this UDF calling formula, =DoCool(B3) , in a cell, say, C3… The procedure, TooCool(arg , ) , is the one used in the Evaluate "Procedure to do stuff to other cells" code line. That procedure gives us the output in , that is to say changes the cell value of , J3
' The second part of the function below is conventional UDF stuff. The first part is the bit what lets us change values in cells other than that in which our UDF is placed in the worksheet
Function DoCool(ByVal Rng As Range) As String
Rem 1 ' The next code line(s) is the Evaluate "SomeOtherProcedureToDoAnythingAnywhere(Arg _ , _ )" code line
' Evaluate "'" & ThisWorkbook.Path & "\UDF to modify other cells.xls'!Module1.TooCool(" & Rng.Address & ",J3)
'' or
' Evaluate "'UDF to modify other cells.xls'!Module1.TooCool(" & Rng.Address & ",J3)"
'' or
' Evaluate "Module1.TooCool(" & Rng.Address & ",J3)"
'' or
Evaluate "TooCool(" & Rng.Address & ",J3)"
'_---------------------------------------------------------------------
Rem 2 ' Conventional UDF coding below
If Rng.Value < 0 Then
Let DoCool = "Number in " & Rng.Address(RowAbsolute:=False, ColumnAbsolute:=False) & " is less than zero."
Else
Let DoCool = "Number in " & Rng.Address(RowAbsolute:=False, ColumnAbsolute:=False) & " is greater than, or equal to, zero."
End If
End[ Function
'
' The procedure below is a conventional procedure to put something in a cell.
Sub TooCool(ByVal InCell As Range, ByVal PushTo As Range)
Let PushTo.Value = "The square of " & InCell.Value & " (in " & InCell.Address(RowAbsolute:=False, ColumnAbsolute:=False) & ") is " & InCell.Value ^ 2 & "."
End Sub

Now put this UDF calling formula, =DoCool(B3) , in a cell, say, C3 …
The procedure, TooCool(arg , _ ) , is the one used in the
_ Evaluate "Procedure to do stuff to other cells"
code line. That procedure gives us the output in , that is to say changes the cell value of , J3

Row\ColBCD
2

3=DoCool(B3)

4

Row\ColBCDEFGHIJK
2

3Number in B3 is greater than, or equal to, zero.The square of (in B3) is 0.

4

Row\ColBCDEFGHIJK
2

3
-2Number in B3 is less than zero.The square of -2 (in B3) is 4.

4

[DocAElstein]

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/excel-forum/vba-macros/pass-named-range-to-vba-function#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/excel-4-macro-functions
https://docs.microsoft.com/en-us/office/client-developer/excel/excel-recalculation
https://www.excelforum.com/excel-programming-vba-macros/1303563-function-sub-exiting-early-except-when-called-from-developer-tab.html#post5265903
https://www.excelforum.com/excel-charting-and-pivots/1131728-trigger-a-macro-or-worksheet-event-with-a-udf.html#post4343285

_._____________________________-

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

[DocAElstein]

I forgot about this Blog tip thread for a while. …

The reason for that was that I answered a few threads, where someone asked for help because something was not doing what they wanted, and the reason for that was that they were trying to get a UDF to change the value of another cell, or something very similar.
My solution seemed to work. Sometimes the OP seemed happy. But more often than not, I got a lot of hate from some senior member, “expert Guru”, Moderator, or similar, insisting that a UDF cannot change the value of any cell other than the one it is in.



It’s about 2 years later now. Maybe it’s safe to post something about it again.
In the meantime, I still don’t have any newer versions of Office/ Excel, so maybe I will post a very simple example, and ask people to test it for me on newer versions. I won’t present it as "a UDF that can change the value of another cell, other than the one the UDF is in".

See how it goes.


I made a much simplified sample file, as enclosed …… This is what I would like you to try for me please:
Download and open the uploaded file, then
enable macros and then
type anything in cell A1, then tell me what happens.

_.________________________________________________ ______________________________
EDIT: Or, alternatively, if you prefer not to risk downloading the file ;)
Please do this
In a new virgin File, insert a normal code module and put this coding in it


Option Explicit
Function WotsThereWhere(ByVal Rng As Range) As String
Evaluate "='" & ThisWorkbook.Path & "\TryThisPlease.xls'!Modul1.YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)" ' Evaluate "=YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)" ' : Debug.Print "=YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)" ' gives YouNameIt($A$1, "Sheet1")
End Function
Sub YouNameIt(ByVal Rng As Range, ByVal Sht As String)
Stop ' It wont
Let Rng.Offset(0, 2).Value = ""
Let Rng.Offset(0, 2).Value = "You wrote " & Rng.Value & " in cell " & Rng.Address(0, 0) & ", in worksheet " & Sht
End Sub

The first function can be simplified if relying on defaults

Function WotsThereWhere(ByVal Rng As Range) As String
' Evaluate "='" & ThisWorkbook.Path & "\TryThisPlease.xls'!Modul1.YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)" ' Evaluate "=YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)" ' : Debug.Print "=YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)" ' gives YouNameIt($A$1, "Sheet1")
Evaluate "YouNameIt($A$1, ""Sheet1"")" ' Shortened version relying on default
End Function






Feedback so far

http://www.eileenslounge.com/viewtopic.php?p=280303&sid=fc2daaad9994cb916e0ff601b8ff4995#p280303








Any Other Threads doing something similar

https://stackoverflow.com/questions/54753141/excel-vba-user-defined-function-that-counts-cells-with-conditional-formatting
https://www.mrexcel.com/board/threads/using-displayformat-in-a-udf.1154593/
https://www.eileenslounge.com/viewtopic.php?p=300091#p300091

[DocAElstein]

Another example, possibly, indirectly
http://www.eileenslounge.com/viewtopic.php?f=30&t=38798
That is a different problem. Indeed we want the UDF to give a result in the cell in which it is, as more typically using a UDF.
But a couple of things reminded me of discussions here…
_1) Something is not working from inside a UDF, something that otherwise works.
_2) A sudden abrupt termination, without an error, in the step ( F8 ) debug mode code execution.
_3) The thing not working is generally “wired” to interact with things in cells in a spreadsheet

So I investigated.

The first problem I had was that the thing ( DisplayFormat ) not working at all in my earlier Office/Excel versions.
But I got to check this later in 2013, https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=18404&viewfull=1#post18404 , and confirmed the issue. DisplayFormat is working to return a value in a normal sub routine and a function, and the snag is just it working in a spreadsheet ( applying the function as available in a spreadsheet formula, what we typically call a UDF )

I actually wont close to the opposite of what I was doing here.
Lets not talk about what I was doing. ( That seems controversial anyway )
Lets approach it a bit laterally thinking

What is/ was the goings on:

We got two things:

' First thing
Function WotsThereWhere(ByVal Rng As Range) As String
Evaluate "='" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'!UDFchangeotherCells.YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)" ' Evaluate "=YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)" ' : Debug.Print "=YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)" ' gives YouNameIt($A$1, "Sheet1")
' Evaluate "YouNameIt($A$1, ""Sheet1"")" ' Shortened version relying on default
End Function


' Second thing
Sub YouNameIt(ByVal Rng As Range, ByVal Sht As String)
Stop ' It wont
Let Rng.Offset(0, 2).Value = ""
Let Rng.Offset(0, 2).Value = "You wrote " & Rng.Value & " in cell " & Rng.Address(0, 0) & ", in worksheet " & Sht
End Sub
The first thing, Function ( used as a spreadsheet UDF) , WotsThereWhere , is on the signature line, normal looking, ( and in this case it takes an argument, a range object. )
( The only reason in this case why it is As String declared , is that it is not wanted to return anything as I was using it, at least nothing to us visible. If , for example, it was alternatively As Long declared, then I would see a zero )
The coding in that function is just one line. It Evaluates the string name of a sub routine, that sub routine is our second thing

That single code line somehow sets off the second thing , a sub routine which does what did not work directly inside the function.

That’s it

What do we conclude relevant to the current problem (https://eileenslounge.com/viewtopic.php?p=300075&sid=4fd3626428d1c8bafd17c5911ac23fcf#p300075) It seems like the thing to try is to get the second thing to
_(i) do something with DisplayFormat, and
_(ii) put the result of that ) do something with DisplayFormat in the cell where we have the UDF using the first thing function


So here we go!

Lets duplicate the first and second thing, put them in a new code module, , and give things different names more appropriate to what we are talking about, the current issues: requirements and problems
So in normal code module I name , DisplayFormatUDF, I have

Option Explicit
' First thing
Function DoSubDoDisplayFormat(ByVal Rng As Range) As String
Evaluate "='" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'!DisplayFormatUDF.DoDisplayFormat(" & Rng.Address & ", """ & ActiveSheet.Name & """)" ' Evaluate "=YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)" ' : Debug.Print "=YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)" ' gives YouNameIt($A$1, "Sheet1")
' Evaluate "DoDisplayFormat ($A$1, ""Sheet1"")" ' Shortened version relying on default
End Function


' Second thing
Sub DoDisplayFormat(ByVal Rng As Range, ByVal Sht As String)
Stop ' It wont
Let Rng.Offset(0, 2).Value = ""
Let Rng.Offset(0, 2).Value = "You wrote " & Rng.Value & " in cell " & Rng.Address(0, 0) & ", in worksheet " & Sht
End Sub

I remove Adeel’s formula in his cell D17, ( {=sum_color(D6:G15;C17)} ) and I replace it with =DoSubDoDisplayFormat(C17)
It seems to then give similar results to what I expect. All is well: the formula in D17 writes something in cell E17 if you edit cell C17 https://i.postimg.cc/bG3yh6J6/You-Wrote11in-Cell-C17.jpg (https://postimg.cc/bG3yh6J6)
https://i.postimg.cc/gJPn3QPP/You-Wrote11in-Cell-C17.jpg (https://postimages.org/)

So lets start in the next post with the “AdeelSolution1” ( )





Share ‘DisplayFormatInUDF.xlsm’ https://app.box.com/s/e4307kqrwx6zqk9uwswlpfziz6air9gy

[DocAElstein]

“AdeelSolution1” ( )

Simple modifications to the second thing , for now, just to see if we can get a result from DisplayFormat
I will forget the sheet referring, as that is not of interest to the final solution,
and
I will do some simple use of DisplayFormat, for example, the DisplayFormat.Interior.ColorIndex of cell C17

Example, change

Sub DoDisplayFormat(ByVal Rng As Range, ByVal Sht As String)
Stop ' It wont
Let Rng.Offset(0, 2).Value = ""
Let Rng.Offset(0, 2).Value = "You wrote " & Rng.Value & " in cell " & Rng.Address(0, 0) & ", in worksheet " & Sht
End Sub
to

' Second thing
Sub DoDisplayFormat(ByVal Rng As Range, ByVal Sht As String)
Stop ' It wont
Let Rng.Offset(0, 2).Value = ""
Let Rng.Offset(0, 2).Value = "For cell " & Rng.Address(0, 0) & ", you have a DisplayFormat.Interior.ColorIndex of " & Rng.DisplayFormat.Interior.ColorIndex
End Sub
This appears to be giving me sensible results in Office Excel 2013:
https://i.postimg.cc/9r2Gj8rj/Working-Disply-Format.jpg (https://postimg.cc/9r2Gj8rj)
https://i.postimg.cc/rF0gx7Tz/Working-Disply-Format.jpg (https://postimages.org/)




Towards a final solution for Adeel
We want a replacement for a formula of this form =sum_color(D6:G15,C17)
So we need
a second range argument in the first thing,
Function DoSomeColor(ByVal RngA As Range, ByVal RngB As Range) As String
and
in the second thing we no longer need to be taking in a sheet name, but do need to take in a second range argument.
Sub SomeColor(ByVal RgA As Range, RgB As Range)

The rest of the modification is just doing something similar in the second thing to Adeel’s original function.

' ' From Adeel spreadsheet ' =sum_color(D6:G15;C17) ' http://www.eileenslounge.com/viewtopic.php?p=300075#p300075
' First thing
Function DoSomeColor(ByVal RngA As Range, ByVal RngB As Range) As String
Evaluate "='" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'!DisplayFormatUDF.SomeColor(" & RngA.Address & ", " & RngB.Address & ")" '
' Evaluate "SomeColor(" & RngA.Address & ", " & RngB.Address & ")" ' Shortened version relying on default
End Function


' Second thing
Sub SomeColor(ByVal RgA As Range, RgB As Range)
Stop ' It wont
Dim Vee As Long, Sea As Range
For Each Sea In RgA
If Sea.DisplayFormat.Interior.ColorIndex = RgB.DisplayFormat.Interior.ColorIndex Then
Let Vee = Vee + Sea.Value
Else
End If
Next Sea
Let RgB.Offset(1, 2).Value = ""
Let RgB.Offset(1, 2).Value = Vee
End Sub

That seems to give the correct result of 54https://i.postimg.cc/PNYm9sf4/Working-Adeel-Solution1.jpg (https://postimg.cc/PNYm9sf4)
https://i.postimg.cc/BZwM84dd/Working-Adeel-Solution1.jpg (https://postimages.org/)



( For comparison, see the results of 54 here:
http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=18404&viewfull=1#post18404
https://i.postimg.cc/VSZJZjv3/Correct-Answers-by-call-in-coding.jpg (https://postimg.cc/VSZJZjv3) )






Share ‘DisplayFormatInUDFAdeel1.xlsm’ https://app.box.com/s/5nvqh5r8pggc11ulz4lti3yhk39b9wda

[DocAElstein]

“AdeelSolution2” ( ) (Some investigations)
Me giving a solution to that thread (https://eileenslounge.com/viewtopic.php?f=30&t=38798&sid=397caa2db349aa4dc60cb818268a002c) with the ideas from my thread here, (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) is something of strange ironic/ contradictory nature, because the whole point of my thread here (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) 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:
https://i.postimg.cc/MHgmGyd9/Evaluate-Sub-Return-Double0.jpg (https://postimages.org/)

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.

https://i.postimg.cc/prmBx0Y9/Evaluate-Function-Return-Double0.jpg (https://postimages.org/)

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

https://i.postimg.cc/4dL1DCtk/Evaluate-Function-As-String-Return-Empty-String.jpg (https://postimages.org/)

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

https://i.postimg.cc/SsNr1Qnf/Evaluate-Function-As-String-Equal-Vee-Return54.jpg (https://postimages.org/)

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.

[DocAElstein]

“AdeelSolution2” ( ) (A Final Simplified Solution)

There is not much to do.

I am not interested in the code lines to put the result in a different cell , ( although they are still working : https://i.postimg.cc/HJq791ZF/Working-as-normla-function-and-also-to-put-value-in-other-cell.jpg (https://postimg.cc/HJq791ZF) )

So this looks like our final solution.


' ' From Adeel spreadsheet ' =sum_color(D6:G15;C17) ' http://www.eileenslounge.com/viewtopic.php?p=300075#p300075
' First thing
Function DoSomeColor(ByVal RngA As Range, ByVal RngB As Range) As String
Dim vTemp As Variant
Let vTemp = Evaluate("='" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'!DisplayFormatUDF.SomeColor(" & RngA.Address & ", " & RngB.Address & ")") '
' Let vTemp = Evaluate("SomeColor(" & RngA.Address & ", " & RngB.Address & ")") ' Shortened version relying on default
Let DoSomeColor = vTemp
End Function


' Second thing
Function SomeColor(ByVal RgA As Range, RgB As Range) As String
Stop ' It wont
Dim Vee As Long, Sea As Range
For Each Sea In RgA
If Sea.DisplayFormat.Interior.ColorIndex = RgB.DisplayFormat.Interior.ColorIndex Then
Let Vee = Vee + Sea.Value
Else
End If
Next Sea
' Let RgB.Offset(1, 2).Value = ""
' Let RgB.Offset(1, 2).Value = Vee
Let SomeColor = Vee
End Function


That’s it

Here is a simplified version for clarity to post in a forum Thread

' ' https://www.excelfox.com/forum/showthread.php/2831-UDF-that-can-change-values-in-cells-other-than-the-cell-in-which-the-UDF-is-used?p=18456&viewfull=1#post18456 https://www.excelfox.com/forum/showthread.php/2831-UDF-that-can-change-values-in-cells-other-than-the-cell-in-which-the-UDF-is-used/page2#post18456
' First thing
Function DoSum_Colour(ByVal RngA As Range, ByVal RngB As Range) As String ' From Adeel spreadsheet ' =sum_color(D6:G15;C17) ' http://www.eileenslounge.com/viewtopic.php?p=300075#p300075
Let DoSum_Colour = Evaluate("Sum_Colour(" & RngA.Address & ", " & RngB.Address & ")")
End Function
' Second thing
Function Sum_Colour(ByVal RgA As Range, RgB As Range) As String
Dim Vee As Long, Sea As Range
For Each Sea In RgA
If Sea.DisplayFormat.Interior.ColorIndex = RgB.DisplayFormat.Interior.ColorIndex Then
Let Vee = Vee + Sea.Value
Else
End If
Next Sea
Let Sum_Colour = Vee
End Function