Using a UDF in Excel to mise à jour the worksheet

Microsoft 365 & Office Excel & VBA
1

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

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) but I don’t think my exact approach is among them.

See also: changing cell comments from a UDF

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.

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.