Trace dependents not working

[luca...@gmail.com]

Hi everyone,
I have been using Excel-DNA for many years now (Thank you very much, Govert!).

I noticed recently that one of my Add-Ins was preventing the 'Trace Dependents' feature from working, while not affecting 'Trace Precedents'.

It happens on the computers of all users. Although I haven't tested exhaustively, it seems to be a problem that's been around since 2017, but happens more frequently in the latest Excel 365 versions, often being pointed out as related to Add-Ins in general—but not exclusively.

For example, if I start a new workbook in a clean instance, it works. But as soon as I load my Add-In, the trace dependents stop working, giving an annoying message that no dependents were found—when they clearly exist. Mysteriously, some workbooks (not large and mostly with numeric content) are unable to trace dependents even without any open or registered Add-In.

In some blog, I saw that it might be related to the usage of .NET Core, or some dependencies relying on .NET Core in your compilation, but this is not my case.
Perhaps it's not directly related to the Add-In itself, but rather to the gradual decline of the calculation map engine in Excel, which I have noticed in recent versions.

Let me explain. Most people know that one of the great downsides of VBA UDFs is that they can easily mess with the calculation map. A cell with a UDF might become a dead cell without any noticeable warning. Another issue is with the new feature of striking out “dirty cells”—those where the calculation is not updated due to some dependence change (when in manual mode). This works poorly, showing dirty cells everywhere, even in straightforward calculations where you know exactly which cells should be affected when something changes, clearly showing that the calculation map gets lost very easily.

Another clue is that Excel-DNA UDFs (placed in a single cell) get calculated several times in a row, very often, if some of their dependencies change, highlighting another strange behavior in the calculation map.

Performance degradation is also very noticeable when dealing with large or multiple spreadsheets—issues that are also related to calculation map/engine problems. One might come to a superficial conclusion that the dependency mapping engine is to blame, which could explain the complete failure of the 'Trace Dependents' feature.

Does anybody have any insight into what could be causing this, or any information on how to fix or prevent such issues?

Best regards,
Luca Elia

[Kedar Kulkarni]

Hi Luca,

In my opinion, excel just looks at the excel references as is ie if you give =abc(A1) it will still show A1 as precedent even if the UDF abc does not exist or is part of a addin that is not loaded yet. Same is the case that the referred cell would show the erroneous formula as a dependent as the dependency chain does not care - just know that it has dependency or precedence. I have not encountered this issue ever. For a really small workbook with calculation automatic, this should never happen. 

Now coming to a very large workbook, excel may face slowness however if it is not calculating ie if the main thread is not busy and excel is in ready state, this should not occur. The other way to fix this could be to recreate the workbook by saving the workbook from xlsx to xlsb then close and reopen and xlsb to xlsx/xlsm. This way excel rewrites the entire workbook as first one is a zip/xml/ascii format and the other is a binary format. Try to check the named ranges and also external references to the workbook. 

If they are fine, I would like to see an example. Sometimes there are circular references or UDFs that are not PURE ie dont completely rely on inputs but can generate different values even input does not change.

Even if you want to reach out to microsoft, they would ask minimum steps for reproduction *. Also they would not trust any third party addins so going to Microsoft is not helpful. However any issue with excel calculation engine is a super important issue for them and it is refined over time, so any bugs usually don't spill to production release of excel and addressed promptly through emergency patch which was rare. ( * I worked for Microsoft excel support a long time ago.)

In my opinion, the 64 bit excel should handle large workbooks without issues if stored locally from office 365 drive on a fast speed internet computer. The main issues could be around volatile calculations in the files like using today(), now() which trigger calculation and the excel dna async formulas if dependent on the volatile formulas can cause a domino effect for excel to settle down. This could be a cause for excel dna to be assumed to be culprit but building smarter workbooks/UDFs is the key here to work around the features/specifications/limits of excel. eg. we asked users to use custom udf that are non volatile Today(), Now() which update automatically every n minutes and return cached value if recalculated, this causes excel calculation to settle down faster and async formulas do not trigger again and again due to input being changed fractionally.

Again if you have a consistently reproducible issue, then this is going to get traction, otherwise its usually pointing to a badly designed excel workbook or one rogue addin that does not follow rules.

thanks,

Kedar

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/exceldna/7b587cf5-6ce3-4739-8859-0a13c8069442n%40googlegroups.com.

[luca...@gmail.com]

Hi Kedar,

Thanks for looking into this. From my end, the 'Trace Dependents' issue with my Excel-DNA UDFs has been a consistent problem (but due my entire fault, as I explain later).

First of all, I appreciate your systematic approach to diagnosing the issue. Your inability to reproduce these problems on your machine was a key point, leading to the straightforward conclusion that the root cause likely lies within my machine and/or my installed add-ins.

While you correctly checked whether the UDF was volatile, my confirmation that it was not helped narrow down the possible causes. That said, it's worth noting that I encounter the issue even without using any UDF from the add-in—just by loading it into a simple spreadsheet (or even a minimal sheet using just a couple of formulas and cells).

It's clear that consistent reproducibility is a major challenge here, as the issue may depend heavily on a user's specific environment, making it difficult to provide Microsoft with a clean, universally reproducible case. Your experience working with Microsoft support reinforces this point.

Ultimately, your analysis confirms that my observations are valid for my environment (and for users of these add-ins). The problem seems to stem from a subtle bug involving interactions between a specific version of Excel and particular add-ins, rather than being an Excel-DNA, or even an Excel's flaw.

What remains unclear to me is the scenario where large—but very simple—workbooks fail to trace dependencies. This occurs even without any UDFs, circular references, or loaded add-ins. What common factor do these workbooks share that isn't immediately obvious? Perhaps something in my add-in is triggering a hidden bug in Excel, causing a persistent effect. Even after the add-in is removed, the bug continues to manifest in the "contaminated" sheets.

Based on this insight, I decided to narrow the problem down through exhaustion. I began with a "contaminated" workbook that had no apparent reason to fail, and systematically removed features until I was left with a bare-bones file: a single-cell formula referring to another cell. Remarkably, the bug still reproduced. I then created a clean workbook with exactly the same content and compared the underlying XML.

I realized the differences distilled down to:

<calcPr calcId="191029" calcCompleted="0" forceFullCalc="1"/>

versus

<calcPr calcId="191029" calcOnSave="0"/>

After some try and error I figure out a wat to remove forceFullCalc adn calcCompleted tags, and I ended up with:

Sub ResetCalcFlags()

    With Application

        .Calculation = xlCalculationManual

        .CalculateBeforeSave = False

    End With

    ThisWorkbook.ForceFullCalculation = False

    ThisWorkbook.RefreshAll

    Application.Calculation = xlCalculationAutomatic

    ThisWorkbook.Save

End Sub

This VBA routine solves the problem for any "contaminated" workbook.
Therefore, my conclusion (as far as I can tell) is: When forceFullCalc is enabled, Excel bypasses the dependency-based calculation map. Instead, every formula in every sheet is recalculated—regardless of whether its precedents (the cells it refers to) have changed. In the process, the entire dependency map is also lost.

The final step was identifying the culprit line in my code, which turned out to be hidden in some legacy logic:

workbook.ForceFullCalculation = true;

And voilà!—the problem disappeared!

Again, as far as I can confirm, setting forceFullCalc causes Excel to abandon its optimized dependency tracking in favor of brute-force recalculation, which leads to the issue.

I hope this helps someone else facing a similar situation.

[Kedar Kulkarni]

thanks for detailed explanation. I see that the few details are mentioned by MIcrosoft itself here => https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.forcefullcalculation

It clearly says the below.. (so Microsoft would argue that its a feature -- not a bug :D )

Remarks

If the workbook is in the forced calculation mode, dependencies are ignored and all worksheets are marked to calculate fully every time a calculation is triggered. This setting remains in effect until Excel is restarted.

I ideally would use Application.CalculateFullRebuild to fix the calculation chain instead of ForceFullCalculation though you may have specific requirements. As dependencies are ignored, I would expect the trace dependents would not work either (though it is not explicitly mentioned in the link)  Also this is a Workbook level method however calculation is at application level so it can cause unintentional consequences. https://learn.microsoft.com/en-us/office/troubleshoot/excel/current-mode-of-calculation 

I would use the ForceCalculation = true cautiously as the excel needs to be restarted once the setting is applied.

I would ideally just rely on excel to resolve dependency tree or make it full rebuild if needed if I think it is corrupt.

thanks 

Kedar

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/exceldna/1e854d52-6cac-4512-bfe8-f7f282b38542n%40googlegroups.com.