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

by

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.

More Related Contents:

  1. Refresh Excel VBA Function Results
  2. Function Overloading and UDF in Excel VBA
  3. using a sheet in an excel user defined function
  4. How to get MS Excel to list files in a folder with their metadata (in particular, the tags created for the files)?
  5. Excel – opening file stuck on 100%, until VBA code finishes
  6. Using a UDF in Excel to update the worksheet
  7. How to add a reference programmatically using VBA
  8. automatically execute an Excel macro on a cell change
  9. VLOOKUP with multiple criteria returning values in one cell
  10. VBA check if file exists
  11. Copy/Paste/Calculate Visible Cells from One Column of a Filtered Table
  12. Trying to use variables as values vba to sql
  13. How to insert an embedded picture?
  14. How to call a macro from a button and pass arguments
  15. Obtain attribute names from XML using VBA
  16. VBA (Excel) Dictionary on Mac?
  17. Paste Excel range in Outlook
  18. Code for looping through all excel files in a specified folder, and pulling data from specific cells
  19. VBa conditional delete loop not working
  20. Run-time error ‘1004’ : Method ‘Range’ of object’_Global’ failed
  21. Concatenate column headers if value in rows below is non-blank
  22. How do I issue an HTTP GET from Excel VBA for Mac
  23. Update excel sheet based on outlook mail [closed]
  24. Export chart as image – with click of a button
  25. Error13 in Excel VBA in data file
  26. Change font color for a part of text in cell
  27. Excel cell from which a Function is called [duplicate]
  28. How to extract text within a string of text
  29. Best way to protect Excel VBA code? [closed]
  30. VBA: Remove duplicates fails when columns array is passed using a variable

Leave a Comment