Excel 正在使用 VBA 函数计算公式作为错误，除非重新输入

Excel is calculating a formula with a VBA function as an error unless it is re-entered

提问by Alain

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

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

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:

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

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!

为了最糟糕的是，和真正吹拂我的心灵，是，如果我只需重新输入公式，或强制完整计算（Ctrl+ Alt+ F9） -公式评估没有问题的！

I've tried making the formula volatile by adding Application.Volatileto 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 F9or Ctrl+F9do not work, only re-entering the formula or Ctrl+Alt+F9will cause the function to recalculate properly.

我已经尝试通过添加Application.Volatile到函数代码来使公式变得易变，但它没有改变任何东西。其他方法以刷新计算，如设置计算为手动，然后返回到自动，hidding“重新计算片材”，或只使用F9或Ctrl+F9不工作，仅再进入式或Ctrl+ Alt+F9将导致正确地重新计算功能.

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.

更改 if 语句中引用的单元格之一不会解决问题，但是，更改“CellIsFormula”函数引用的单元格确实可以解决问题。每次重新打开工作表时，错误都会回来。

采纳答案by Alain

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

我发现了确切的问题，但我想给你们所有人投票以帮助我解决这个问题，并给予 GSerg 荣誉，因为虽然我并非完全不走运，但他坚持他的建议

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

Excel 确实喜欢在计算的某些阶段使范围的某些属性不可用。

Good find GSerg.

好找到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:

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

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 calluntil the cell its parameter references is changed.

大概 - 下一点是对 Excel 的疏忽，但是一旦宏运行完毕，如果重新计算已经完成，由于 UDF 未正常运行而导致错误，Excel 将不会尝试再次运行 UDF。结果错误值将被假定为调用的返回值，除非它认为该 UDF 的参数已更改，否则不会更改。Excel 将缓存用户定义函数调用的结果，直到其参数引用的单元格发生更改。

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.

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

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.

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

回答by GSerg

I couldn't reproduce this error, but:

我无法重现此错误，但是：

1. The signature should be:

   Public Function CellIsFormula2(ByVal rng As Range) As Boolean
     CellIsFormula2 = rng.Cells(1).HasFormula
   End Function
   

2. Excel does like to make certain properties of a range unavailable during certain stages of calcualtion. I've many times seen the .Textproperty being suddenly unavailable. So if changing the signature does not work, then you are probably out of luck.

1. 签名应该是：

   Public Function CellIsFormula2(ByVal rng As Range) As Boolean
     CellIsFormula2 = rng.Cells(1).HasFormula
   End Function
   

2. Excel 确实喜欢在计算的某些阶段使范围的某些属性不可用。我曾多次看到该.Text物业突然不可用。因此，如果更改签名不起作用，那么您可能不走运。

回答by Fink

I think your problems are because the 'HasFormula' Property returns a variant, not a boolean. If the range has mixed formulas and values, HasFormula will return null. Plus your not defining the rng as a Range object, and not specifying output type. I suggest an approach like this. It can be modified to return a boolean pretty easily.

我认为您的问题是因为 'HasFormula' 属性返回一个变体，而不是一个布尔值。如果范围有混合的公式和值，HasFormula 将返回 null。另外，您没有将 rng 定义为 Range 对象，也没有指定输出类型。我建议采用这样的方法。可以很容易地修改它以返回一个布尔值。

Public Function CellIsFormula(rng As Range) As String

Application.Volatile

    Dim testVal As Variant

    testVal = rng.HasFormula 'HasFormula returns variant type

    'testval is null if cells are mixed formulas and values
    If IsNull(testVal) Then
        testVal = "Mixed"
    End If

    Select Case testVal
        Case True
            CellIsFormula = "All Cells in Range Have formula"
        Case False
            CellIsFormula = "No Cells in Range Have formula"
        Case "Mixed"
            CellIsFormula = "Some Cells in Range Have formula"
        Case Else
            CellIsFormula = "Error"
    End Select
End Function