Excel calculates a formula with a VBA function as an error if it has not been re-entered

Question

I have a simple if statement configured on a worksheet where the if condition is a function defined by a VBA user:

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

This function works fine:

But for some reason I cannot understand that the cell is evaluating the error. To make matters worse, when you evaluate a formula, excel attributes the error to a calculation step that does not cause an error:

To top it all, and what really strikes my mind is that if I simply re-enter the formula or force a complete recount ( Ctrl + Alt + F9 ) - the formulas do not evaluate the problem!

I tried to make the formula volatile by adding Application.Volatile to the function code, but didn't change anything. Other methods for updating calculations, such as setting the calculation manually and then back to automatic, deleting the “recalculate sheet” or just using F9 or Ctrl + F9 do not work, just re-entering the formula or Ctrl + Alt + F9 will make the function correctly recount.

Changing one of the cells referenced by the if statement will not fix the problem , but changing the cell referenced by the CellIsFormula function will fix the problem. Each time a sheet opens again, an error is returned.

Answer 1

I found the exact problem, but I want you to vote for you to help me figure it out and give credit to GSerg, because although I was not at all unlucky, he was dead by his proposal that

Excel really wants to make some range properties inaccessible at certain stages of the calculation.

Good to find GSerg.

The problem was event handlers . The book contains a series of event handlers such as Workbook_Open, Worksheet_Change, etc. From time to time, one of the actions performed by these event handlers will result in the redistribution of some cells in the book. If excel starts recounting while the macro is running, any cells containing this UDF will result in an error. This is because, for some reason, the .HasFormula property was not available during the recalculated VBA recalculation, as @GSerg said:

Presumably - the next bit is the oversight of the Excel part, but as soon as the macro is executed, if recounting is performed, which will lead to errors, since UDFs do not work properly, excel will not try to start UDF again, the resulting error value will be considered the return value of the call and will not change if it is not considered that the parameter of this UDF has changed. Excel will cache the result of calling the User Defined Function until its reference to the parameter changes.

That is why the transition to the "Evaluate Formula" will show everything that works until the very last step, where it does not actually evaluate the last step, it just shows the value from the table, as calculated by the last.

Decision

There were actually two possible solutions. The first solution I found was to turn off automatic calculation at the beginning of event handlers and turn it back on. For some reason, despite the fact that the macro works at the time of calculation, the value xlCalculationAutomatic is returned, this will lead to a successful re-evaluation of the UDF and the property will be available.

The second solution, which I prefer, because it prevents an accidental repetition of this case, is to use another method to test the formula:

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

The .Formula property is never inaccessible. Therefore, this problem never arises.

Answer 2

I could not reproduce this error, but:

• Signature must be:

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

• Excel really wants to make some range properties inaccessible at certain stages of the calculation. I have repeatedly seen that the .Text property .Text unexpectedly unavailable. Therefore, if the change of signature does not work, you are probably out of luck.

Answer 3

I think your problems are related to the fact that the HasFormula property returns an option, not a logical one. If the range has mixed formulas and values, HasFormula will return null. In addition, you do not define rng as a Range object and do not specify the type of output. I suggest this approach. It can be modified to easily get a boolean value.

 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