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

[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\Col	C	D	E
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\Col	A	B	C	D	E
1			P
2	2		P	You did 2 Ps in column C
3

Rick’s example
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\Col	B	C	D
2
3		=DoCool(B3)
4

Row\Col	B	C	D	E	F	G	H	I	J	K
2
3		Number in B3 is greater than, or equal to, zero.							The square of (in B3) is 0.
4

Row\Col	B	C	D	E	F	G	H	I	J	K
2
3	-2	Number in B3 is less than zero.							The square of -2 (in B3) is 4.
4