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

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


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:Property Unavailable

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.


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).HasFormulaEnd 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.


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 StringApplication.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 SelectEnd Function