在 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.

在 SO(和许多其他论坛)上有很多问题，如我的用户定义函数有什么问题"，答案是您无法从 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屋！