为什么vba udf'range.formula =＆quot;不工作吗？

发布于 2025-01-30 02:13:50 字数 457 浏览 3 评论 0原文

我想通过range.formula =将公式从一个/单元将其复制到另一个单元/单元格。但这并不能如预期的那样起作用。

如果我逐步运行VBA，则该函数将以range.formula =结束而不会出错。

Function test1(sOURCE As Range, tARGET As Range)
    tARGET.Formula = sOURCE.Formula
    test1 = tARGET.Formula
End Function

原文

I want to copy the formula from one cell/cells to another cell/cells by Range.Formula =.
But it not work as expected.

If I run the VBA step by step, the function will ended at Range.Formula = without error.

Function test1(sOURCE As Range, tARGET As Range)
    tARGET.Formula = sOURCE.Formula
    test1 = tARGET.Formula
End Function

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

哆兒滾 2025-02-06 02:13:50

您正在尝试使用UDF更改另一个单元格的公式。根据 Microsoft文档这是无法做到的，但是Ryan Wells实际上找到了一个不错的解决方法。请参阅：如何如何要使用vba函数UDF 更改另一个单元格。还要进行比较： vba：如何更改通过函数的另一个单元格的值？

我们需要在“助手”子上使用评估。我已经稍微调整了Wells提供的第一个示例，以适应您的需求：

Function copyFormula(copyFrom As Range, copyTo As Range)

copyFrom.Parent.Evaluate "copyOver(" & copyFrom.Address() _
                        & "," & copyTo.Address() & ")"
                        
copyFormula = "Formula " & copyFrom.Address() & " -> " & copyTo.Address()

End Function

Private Sub copyOver(copyFrom As Range, copyTo As Range)

copyTo.Formula = copyFrom.Formula
    
End Sub

这可行，但请记住，即使将计算设置为自动，该公式也不会在插入后自动计算。实施：

结果：

克服此问题的一种方法是在使用公式的工作表中包括Worksheet_change子。例如，简单：

Private Sub Worksheet_Change(ByVal Target As Range)

Calculate

End Sub

但是表格中的任何后续操作似乎都会触发计算。另外，请注意，在此单元格中使用copyformula时，您将无法覆盖目标单元。由于它只会用公式立即覆盖它（如果未应用上述技巧，则使用0作为输出）。这可能会使您的用户感到困惑。

You are trying to change another cell's formula with a UDF. According to the Microsoft documentation this cannot be done, but Ryan Wells has actually found a nice workaround. See: How to change another cell with a VBA function UDF. Compare also: VBA: How to change the value of another cell via a function?

We need to use Evaluate on a "helper" sub. I have slightly adjusted the first example provided by Wells to suit your needs:

Function copyFormula(copyFrom As Range, copyTo As Range)

copyFrom.Parent.Evaluate "copyOver(" & copyFrom.Address() _
                        & "," & copyTo.Address() & ")"
                        
copyFormula = "Formula " & copyFrom.Address() & " -> " & copyTo.Address()

End Function

Private Sub copyOver(copyFrom As Range, copyTo As Range)

copyTo.Formula = copyFrom.Formula
    
End Sub

This works, but please bear in mind that (intriguingly) the formula will not automatically calculate after insertion, even with calculation set to automatic. Implementation:

Result:

One way to overcome this problem is to include a Worksheet_Change sub for the worksheet where you are using the formula. E.g. simply:

Private Sub Worksheet_Change(ByVal Target As Range)

Calculate

End Sub

But any subsequent action in the sheet seems to trigger the calculation. Also, please be aware that you won't be able to overwrite the target cell while you have a copyFormula in use with this cell. Since it will just keep overwriting it immediately with the formula (with 0 as output, if the above trick isn't applied). This might be confusing to your users.

回复收藏 0 原文