在Excel中使用UDF更新工作表

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (16)

这并不是一个问题,但我把这篇文章作为评论,因为我不记得以前见过这种方法。我对之前的一个答案做出了回应,并尝试了一些我以前从未尝试过的东西:结果很有趣,所以我想把它作为一个独立的问题,连同我自己的答案一起发布。

有许多问题是关于“我的用户定义的函数出了什么问题”的,答案是“不能从UDF更新工作表”

提问于
用户回答回答于

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

用户回答回答于

MSDN KB是不正确的。

上面写着

由工作表单元格中的公式调用的用户定义函数无法更改MicrosoftExcel的环境。这意味着这种职能不能执行下列任何一项工作:

  1. 插入、删除或格式单元格在电子表格上。
  2. 更改另一个单元格的值...
  3. 将工作表移动、重命名、删除或添加工作表。
  4. 更改任何环境选项,如计算模式或屏幕视图。
  5. 向工作簿添加名称...
  6. 设置属性或执行大多数方法。

在下面的代码中,可以看到点1、2、4和5可以很容易地实现。

Function SetIt(RefCell)
    RefCell.Parent.Evaluate "SetColor(" & RefCell.Address(False, False) & ")"
    RefCell.Parent.Evaluate "SetValue(" & RefCell.Address(False, False) & ")"
    RefCell.Parent.Evaluate "AddName(" & RefCell.Address(False, False) & ")"

    MsgBox Application.EnableEvents
    RefCell.Parent.Evaluate "ChangeEvents(" & RefCell.Address(False, False) & ")"
    MsgBox Application.EnableEvents

    SetIt = ""
End Function

'~~> Format cells on the spreadsheet.
Sub SetColor(RefCell As Range)
    RefCell.Interior.ColorIndex = 3 '<~~ Change color to red
End Sub

'~~> Change another cell's value.
Sub SetValue(RefCell As Range)
   RefCell.Offset(, 1).Value = "Sid"
End Sub

'~~> Add names to a workbook.
Sub AddName(RefCell As Range)
   RefCell.Name = "Sid"
End Sub

'~~> Change events
Sub ChangeEvents(RefCell As Range)
    Application.EnableEvents = False
End Sub

扫码关注云+社区