除非重新输入，否则Excel正在计算具有VBA函数的公式作为错误 [英] Excel is calculating a formula with a VBA function as an error unless it is re-entered

问题描述

我在工作表中设置了一个简单的if语句，其中if条件是VBA用户定义的函数：

 函数CellIsFormula（ByRef rng）
 CellIsFormula = rng（1）.HasFormula 
结束函数
  

此功能似乎正常工作：

但是由于某些原因我无法弄清楚，单元格正在评估一个错误。更糟糕的是，在评估公式时，excel将错误归因于不产生错误的计算步骤：

返回顶部这一切，真正的打击我的想法是，如果我只是重新输入公式，或强制完全重新计算（ Ctrl + Alt + F9

我已经尝试通过向函数代码添加 Application.Volatile 使公式变得不稳定，但没有改变任何东西。刷新计算的其他方法，例如将计算设置为手动，然后返回自动，启动重新计算工作表，或者仅使用 F9 或 Ctrl + F9 不能正常工作，只需重新输入公式或 Ctrl + Alt + F9 将导致该函数正确重新计算。 / p>

更改if语句中引用的一个单元格将不会解决问题但，更改CellIsFormula函数引用的单元格，确实解决了这个问题。

解决方案

我发现确切的问题，但我想要起来 - 请你们全力以赴帮助我弄清楚这一点，并给予GSerg的信用，因为虽然我并没有完全失去运气，但他已经死了，他的建议是：

Excel在某些特定阶段期间希望使范围的某些属性不可用。

找到GSerg。

问题出在事件处理程序。该工作簿包含一系列事件处理程序，如Workbook_Open，Worksheet_Change等。现在，这些事件处理程序采取的一个操作将导致工作簿中的一些单元格重新计算。 如果excel在宏运行时触发重新计算，任何包含此UDF的单元格将导致错误。这是因为由于某些原因，在VBA触发重新计算期间， .HasFormula属性为不可用，就像@GSerg所说：

可能 - 下一个位是Excel的一部分的监督，但一旦宏完成运行，如果重新计算完成，导致错误，因为UDF没有正常运行，excel不会尝试再次运行UDF。生成的错误值将被假定为调用的返回值，除非认为该UDF的参数已更改，否则不会更改。 Excel将缓存用户定义函数调用的结果，直到单元格的参数引用被更改为止。

这就是为什么通过评估公式将显示一切工作，直到最后一步，实际上并不评估最后一步，它只显示电子表格中的值，如上次计算的。

解决方案

实际上有两种可能的解决方案。我发现的第一个解决方案是在事件处理程序开始时禁用自动计算，然后重新启用它。由于某种原因，即使在计算时计算的宏被设置为xlCalculationAutomatic，它将导致UDF成功重新评估，并且属性可用。

第二个解决方案，我更喜欢，因为它防止这一次意外地再次发生，是使用不同的方法来检查一个公式：

  Function CellIsFormula（ByRef rng As Range）As Boolean 
 CellIsFormula = Left（rng（1）.Formula，1）==
结束函数
  

.Formula属性永远不可用。所以这个问题永远不会发生。

I've got a simple if statement set up in a worksheet where the if condition is VBA user defined function:

Function CellIsFormula(ByRef rng)
    CellIsFormula = rng(1).HasFormula
End Function

This function seems to work fine:

But for some reason that I can't figure out, the cell is evaluating to an error. What's worse, is when evaluating the formula, excel is attributing the error to a calculation step that doesn't produce an error:

To top it all off, and what really blows my mind, is that if I simply re-enter the formula, or force a full recalculation (Ctrl+Alt+F9) - the formulas evaluate no problem!

I've tried making the formula volatile by adding Application.Volatile to the function code, but it didn't change anything. Other methods to refresh the calculation, such as setting calculation to manual and then back to automatic, hidding "recalculate sheet", or just using F9 or Ctrl+F9 do not work, only re-entering the formula or Ctrl+Alt+F9 will cause the function to recalculate properly.

Changing one of the cells referenced in the if statement will not fix the problem, but, changing the cell referenced by the "CellIsFormula" function, does fix the problem. Every time the sheet is re-opened though, the error is back.

解决方案

I discovered the exact problem but I want to up-vote you all for trying to help me figure this out, and give GSerg the credit because, while I wasn't completely out of luck, he was dead on with his suggestion that

Excel does like to make certain properties of a range unavailable during certain stages of calcualtion.

Good find GSerg.

The problem was with Event Handlers. The workbook contains a series of event handlers like Workbook_Open, Worksheet_Change, etc. Every now and then, one of the actions taken by these event handlers will cause some cells in the workbook to recalculate. If excel triggers a recalculation while a macro is running, any cells containing this UDF will result in an error. This is because for some reason, during the VBA triggered recalculation, the .HasFormula property was unavailable, just like @GSerg said:

Presumably - the next bit is an oversight on Excel's part, but once the macro is done running, if a recalculation has been done, resulting in errors because UDFs didn't run properly, excel will not try to run the UDFs again. The resulting error value will be assumed to be the return value of the call, and will not change unless it thinks the parameter to that UDF has changed. Excel will cache the result of the User Defined Function call until the cell its parameter references is changed.

That is why stepping through 'Evaluate Formula' will show everything working until the very last step, where it doesn't actually evaluate the last step, it just shows the value from the spreadsheet as was last calculated.

Solution

There were actually two possible solutions. The first solution I found was to disable automatic calculation at the beginning the Event Handlers, and re-enable it afterwards. For some reason, even though a macro is running at the time calculation is set back to xlCalculationAutomatic, it will cause the UDFs to be successfully re-evaluated, and the property is available.

The second solution, which I prefer because it prevents this from accidentally ever happening again, is to use a different method to check for a formula:

Function CellIsFormula(ByRef rng As Range) As Boolean
    CellIsFormula = Left(rng(1).Formula, 1) = "="
End Function

The .Formula property is never unavailable. So this problem never occurs.

这篇关于除非重新输入，否则Excel正在计算具有VBA函数的公式作为错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！