Custom functions in error state do not get recalculated by Excel

[gmichaud]

Provide required information needed to triage your issue

Excel does not reinvoke our custom functions on cells showing a #VALUE! error when you force a full recalculation of the workbook (using the Refresh button of our add-in ribbon or when doing CTRL-ALT-F9 in Excel).

This issue has been reported by many customers over the last year and we have not been able to isolate the issue until recently. This issue appears to happen in conjunction with an error initializing custom formulas, as described in #4487.

We were able to isolate this in a fairly simple file that I can share with Microsoft via a private link on request.

Demo from Windows: https://www.loom.com/share/ef9351ad9a3748bf9e9805f20dda1317
Demo from Mac: https://www.loom.com/share/652438c17cbc4bb89fe0b501ad14b812

Your Environment

• Platform [PC desktop, Mac, iOS, Office on the web]: Issue replicated on Excel for Mac and Excel for Windows
• Host [Excel, Word, PowerPoint, etc.]: Mac 16.85, Windows Version 2404 Build 16.0.17531.20152) 64 bits

Expected behavior

All the formulas should recalculate when you hit the Refresh button or press CTRL-ALT-F9

Current behavior

Only the first formula of the Data tab recalculates (column B); other columns show #VALUE! Eventually, if you leave Excel running in the background, the formula recalculates.

Please note the warning message presented in the cell "A value used in the formula is of the wrong data type" -- this is very confusing to users as well.

Steps to reproduce

1. Open file (please request via comments, I will share through secure link) -- notice that some columns will temporarily show a #NAME! error for all the cells that were previously in an error state
2. Go to add-in ribbon "Velixo"
3. Click Refresh All button -- cells in error state do not refresh

Doing "Calculate Sheet" on the affected sheets after step 3 seems to resolve it, but this is not an acceptable solution for customers with large workbooks -- they can't be expected to figure out on which sheets calculation failed. In some cases I've also seen these columns start calculating after a very long wait.

Link to live example(s)

Demo from Windows: https://www.loom.com/share/ef9351ad9a3748bf9e9805f20dda1317
Demo from Mac: https://www.loom.com/share/652438c17cbc4bb89fe0b501ad14b812

Provide additional details

This problem started occurring on that file following a failed loading of our add-in. I had to uninstall and reinstall the add-in for it to work in any workbook, however after that the file I had open when the issue first occurred was in this weird state. This seems similar to the issue described in #4487.

Note that we also opened the XML structure of a "good" and a "bad" file to compare, and the notable difference is the aca and ca attribute of the formula (note: our formulas can all return arrays, in case that matter):

[shanshanzheng-dev]

Hi @gmichaud, We're looking into this problem, thanks for reporting! we will report back here if we have an investigation. Thanks.

[wh1t3cAt1k]

Velixo internal tracking item

[wh1t3cAt1k]

Following our recent discussion, it seems like it is a false alarm produced by the file with complex calculation dependencies. @gmichaud would you be fine with closing this following Xuan Zhou's email explanation?