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

**DocAElstein** · 01-09-2019, 09:11 PM

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
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 4 , or post a question in the main Excel Help forum question sub forum
https://www.excelfox.com/forum/forum...p/2-Excel-Help

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 , …_

Code:

'   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

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

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

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