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

**DocAElstein** · 01-24-2021, 07:33 PM

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

Code:

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

Code:

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