VBA:在用户定义的函数中创建单元格注释

Question

我有一个UDF，它根据一堆输入变量进行计算。我正在尝试让 UDF 创建一个包含有关这些变量信息的单元格注释，但我只是得到了 #VALUE！错误。

这就是我要做的：

Function profit(income As Single, loss As Single) As Single
Dim cell As Range
cell = ActiveCell
profit = income - loss
call create_comment(cell, income, loss)
End function

呼叫子：

Private Sub create_comment(cell As Range, income As Single, loss As Single)
cell.ClearComments
cell.Addcomment "income =" & income & "loss =" & loss
End Sub

所有的帮助感谢！

Answer 1

我像那样改变了你的函数

Function profit(income As Single, loss As Single, cell As Range) As Single
'Dim cell As Range
'cell = ActiveCell
profit = income - loss
Call create_comment(cell, income, loss)
End Function

然后你在函数的调用中输入活动单元格，就像这样

=profit(1000,10,H10)