Author
Topic: isCalced (Read 1759 times)

I am just wondering whether you happen to know a way we can implement isCacled in PyXLL?

Essentially a cell with formula may get evaluated more than once during Calculate-All. And there could be a function that takes a long time (eg, fetching data from other sources) ends up being calculated multiple times.

So the solution is to test whether the cells of all the input arguments have already been calculated. If not quickly return and not to go through with the rest of the function : http://www.decisionmodels.com/calcsecretsj.htm . In this solution, WorksheetFunction.CountA is used to test whether the input argument cell has been calculated. But in the case of PyXLL, there is no way to know the address of the input arguments.

Do you know whether this is at all possible or there is a workaround?

Excerpt:

Public Function IsCalced(theParameter As Variant) As Boolean'' Charles Williams 9/Jan/2009'' Return False if the parameter refers to as-yet uncalculated cells'Dim vHasFormula As Variant

using CountA on an argument passed to a pyxll function isn't possible right now. I think it would require access to the original XLOPER object that is passed to the python function when using the var type (this would only ever work for var types).

I wonder however if you could do something similar by using the xl_cell parameter type and call WorksheetFunctions.DCountA with a Range object created from the xl_cell's address?

Otherwise, something like functools.lru_cache might help?

Let me know if the DCountA function works for you or not. If not, I can take a look at adding another new parameter type that exposes both the value and the XLOPER pointer needed for calling CountA.

Thinking about this a bit more, it should be possible to expose this via the xl_cell parameter type by adding an 'is_calced' property, which would return True only if all cells in the range (or single cell) have been calculated.

Thanks again for your suggestion. is_calced property in xl_cell would be great.

I have tested using xl.WorksheetFunction.CountA (see isCacled below) . Now I call this in another function decorated with @xl_func, (with explicit of range to check isCalced as string) see further below.

yes, I think it will be possible to expose an is_calced property that can be accessed without needing to make the function a macro equivalent function, and hence will be able to be called from a thread safe function.

I'll take a look at this over the next few days see if I can get something for you to try.

When Excel reconstructs the dependency tree during recalculation, functions sometimes get called multiple times with dirty values. This can be very long if the sheet has functions that take a long time to complete (eg, solving matrix with large dimension).