Ce site est optimisé pour être consulté depuis un navigateur moderne dans lequel JavaScript est activé.

Using a UDF dans Excel vers mise à jour the worksheet

ayi

Not really a question, but posting this for comments because I don’t recall seeing this approach before. I was responding to a comment on a previous answer, and tried something I’d not attempted before: the result was interesting so I though I’d post it as a stand-alone question, along with my own answer.
There have been many questions here on SO (and many other forums) along the lines of “what’s wrong with my user-defined function” where the answer has been “you can’t update a worksheet from a UDF” - this restriction outlined here:
<a href="http://support.microsoft.com/kb/170787" rel="noopener nofollow ugc">Description of limitations of custom functions in Excel</a>
There are a few methods which have been described to overcome this e.g. see here (<a href="https://sites.google.com/site/e90e50/excel-formula-to-change-the-value-of-another-cell" rel="noopener nofollow ugc">https://sites.google.com/site/e90e50/excel-formula-to-change-the-value-of-another-cell</a>) but I don’t think my exact approach is among them.
See also: <a href="http://spreadsheetpage.com/index.php/oddity/a_user_define_function_cant_change_the_worksheet_oh_yeah/" rel="noopener nofollow ugc">changing cell comments from a UDF</a>
<hr>
Source : <a href="http://support.microsoft.com/kb/170787" rel="noopener nofollow ugc">Stack Overflow</a>

ayi_2

Posting a response so I can mark my own “question” as having an answer.
I’ve seen other workarounds, but this seems simpler and I’m surprised it works at all.
<pre><code class="lang-auto">Sub ChangeIt(c1 As Range, c2 As Range)
c1.Value = c2.Value
c1.Interior.Color = IIf(c1.Value > 10, vbRed, vbYellow)
End Sub

'######## run as a UDF, this actually changes the sheet ##############
' changing value in c2 updates c1...
Function SetIt(src, dest)

dest.Parent.Evaluate "Changeit(" & dest.Address(False, False) & "," _
                    & src.Address(False, False) & ")"

SetIt = "Changed sheet!" 'or whatever return value is useful...

End Function

</code></pre>
Please post additional answers if you have interesting applications for this which you’d like to share.
Note : Untested in any kind of real “production” application.