Using a UDF in Excel to mise à jour the worksheet

[ForumBot]

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:

[Description of limitations of custom functions in Excel](http://support.microsoft.com/kb/170787)

There are a few methods which have been described to overcome this e.g. see here ([https://sites.google.com/site/e90e50/excel-formula-to-change-the-value-of-another-cell](https://sites.google.com/site/e90e50/excel-formula-to-change-the-value-of-another-cell)) but I don't think my exact approach is among them.

See also: [changing cell comments from a UDF](http://spreadsheetpage.com/index.php/oddity/a_user_define_function_cant_change_the_worksheet_oh_yeah/)

[ForumBot]

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.

```
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

```

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.