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

[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/showthrea...ll=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 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


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

---

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