Python in Excel: How do Excel and Python Formulas Work Together?

In the previous post I showed how Python and Excel calculation methods are completely different:

• Python calculates Left-to-Right and Top-to-Bottom, ignoring dependencies, and when a PY calculates then ALL PY cells calculate – Co-Volatility.
• Excel calculates in dependency sequence and only calculates the cells that need recalculating.

So when Excel formulas and Python formulas depend on one another how does this all work?

Excel->Python->Excel->Python

Lets look at a simple example of inter-dependent Excel and Python formulas.

Cell A10 is referenced by PY Cell E10
Then the Excel formula in E11 references PY cell E10 and multiples it by 10
Then PY Cell E12 references Excel cell E11 and adds 10

The workbook is in Automatic Mode so when I change cell A10 to 42 Excel and Python need to recalculate.

The pink cells show the sequence in which the cells calculate (using a calculation tracing function), and there is a function in D8 that shows how many times Excel has Recalculated – it has changed from 1 to 2.

So what steps do Excel and Python actually have to do to make this interaction work?

Excel started by doing a recalculation of all the dirty Excel cells.
A calculation event fired.
Cell A10 was the first cell to be calculated and the result returned : Calculation sequence 1

But then cells E10:E12 all got deferred down the calculation chain. This typically happens when formulas are dependent on uncalculated cells. E10:E12 are uncalculated because the Python calculation has not yet happened,

Next a Python calc is started and the calculation event shows calculation as pending.
The PY cell E10 is calculated (Calculation sequence 2).
Scanning down the calculation chain Excel can now calculate E11 (calculation sequence 3) because the cell it is dependent on has been calculated.
But E12 has to be deferred again because it needs another PY calculation.

So another PY calc is started and E12 is calculated (calculation sequence 4)

Finally another calculation event fires as Done because there are no more dirty cells waiting to be calculated.

Conclusion

Amazingly this all works!
By flip-flopping between Excel calculation and Python calculation, and making extensive use of deferring calculations further down the calculation chain Excel manages to correctly handle 2 completely different calculation methods.
Testing this with much longer Xl->PY->XL->PY->Xl chains it all works, but inevitably the longer chains start getting a bit slow.