在Excel中使用UDF来更新工作表 [英] Using a UDF in Excel to update the worksheet

问题描述

不是一个问题，而是发表评论，因为我不记得以前看过这个方法。我曾回答过一个对以前答案的评论，并尝试过我以前没有尝试过的内容：结果很有趣，所以我尽可能把它作为一个独立的问题，以及我自己的答案。

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.

在这里有很多问题（和许多其他论坛），我的用户定义函数有什么问题，答案是你无法更新来自UDF的工作表 - 这里规定的限制：

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:

限制说明Excel中的自定义函数

有几种方法已经被描述来克服这个例子请参阅这里（ https://sites.google.com / site / e90e50 / excel-formula-to-change-the-value-of-another-cell ），但我不认为我的确切方法是其中之一。

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.

另请参见：更改UDF中的单元格评论

推荐答案

发布回复，所以我可以将自己的问题标记为有答案。

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.

这篇关于在Excel中使用UDF来更新工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！