Another example, possibly, indirectly
http://www.eileenslounge.com/viewtopic.php?f=30&t=38798
This 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 )
I actually won’t 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:
Code:
' 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
Code:
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: 
So lets start in the next post with the “AdeelSolution1”
Share ‘DisplayFormatInUDF.xlsm’ https://app.box.com/s/e4307kqrwx6zqk9uwswlpfziz6air9gy
Reply With Quote
Bookmarks