如何防止VBA功能在代码内重新执行 [英] How to prevent VBA function from re-executing inside the code

问题描述

我正在从Excel工作表中调用VBA函数。当我在VBA函数的代码中更改单元格时，Excel会尝试重新执行该函数（再次在第二次迭代和...）

示例：如果您有代码：

 函数test（）作为变量
范围（A1）= 1 
 test =test
结束函数
  

当您使用= test（）在任何地方，它将返回 #VALUE！。调试将显示，当您更新A1时，它将尝试重新执行test（）。

可以阻止Excel执行此操作吗？例如。说'不要更新我的任何号码，直到我完成这个功能'？我尝试了 Application.Calculation 标志，或进行一些外部并发检查，但似乎不起作用。

解决方案

正如Gary的学生在他的评论中所说：不要尝试从UDF更改工作表单元格，只能通过其标题返回一个值。

I'm calling VBA function from an Excel worksheet. When I change a cell inside the code of the VBA function, Excel tries to re-execute that function again (and again in the second iteration and ...)

Example: if you have the code:

Function test() As Variant
    Range("A1") = 1
    test = "test"
End Function

When you use "=test()" anywhere, it will return #VALUE!. A debug will show that when you update A1, it will try to re-execute test().

Can you prevent Excel from doing this? E.g. saying 'don't update any of my numbers until I'm done with this function'? I've tried the Application.Calculation flag, or doing some external concurrency checks, but that doesn't seem to work ...

解决方案

As Gary's student says in his comment: Don't attempt to change a worksheet cell from a UDF, only return a value thru its header.

这篇关于如何防止VBA功能在代码内重新执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！