Excel is calculating a formula with a VBA function

问题:

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.

回答1:

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.

回答2:

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 .Text property being suddenly unavailable. So if changing the signature does not work, then you are probably out of luck.

回答3:

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.

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