Author Topic: isCalced  (Read 1430 times)

kris.metha

  • Newbie
  • *
  • Posts: 13
  • Karma: +0/-0
    • View Profile
isCalced
« on: August 18, 2014, 11:47:25 AM »
Hi Tony,

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

IsCalced = True
On Error GoTo Fail
If TypeOf theParameter Is Excel.Range Then
vHasFormula = theParameter.HasFormula
'
' HasFormula can be True, False or Null:
' Null if the range contains a mix of Formulas and data
'
If IsNull(vHasFormula) Then vHasFormula = True
If vHasFormula Then
'
' CountA returns 0 if any of the cells are not yet calculated
'
If Application.WorksheetFunction.CountA(theParameter) = 0 Then IsCalced = False
End If
ElseIf VarType(theParameter) = vbEmpty Then
'
' a calculated parameter is Empty if it references uncalculated cells
'
IsCalced = False
End If
Exit Function
Fail:
IsCalced = False
End Function

Kind regards,
Kris

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 574
  • Karma: +13/-1
    • View Profile
Re: isCalced
« Reply #1 on: August 19, 2014, 03:04:28 PM »
Hi Kris,

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.

Best regards,
Tony

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 574
  • Karma: +13/-1
    • View Profile
Re: isCalced
« Reply #2 on: August 20, 2014, 09:50:24 AM »
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.

I'll log that as something to look at for a future version.

Best regards,
Tony

kris.metha

  • Newbie
  • *
  • Posts: 13
  • Karma: +0/-0
    • View Profile
Re: isCalced
« Reply #3 on: August 21, 2014, 07:12:03 AM »
Hi Tony,

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.


This works Okay as long as the function is registered with thread_safe = False. Once thread_safe=True, xl.WorksheetFunction.CountA will throw exception com_error: (-2146777998, 'OLE error 0x800ac472', None, None), the famous VBA_E_IGNORE exception
which is discussed at MSDN here :  http://social.msdn.microsoft.com/Forums/vstudio/en-US/9168f9f2-e5bc-4535-8d7d-4e374ab8ff09/hresult-800ac472-from-set-operations-in-excel?forum=vsto . It seems that in concurrent function calculation, Excel will be in suspension mode and fail to entertain any COM call.

Because my original goal is to use this in a thread_safe=True function, do you think that using xl_cell property as you mention would work in this case?

def isCacled(excelRangeStr):
    try:
        xl = _xl_app()
        try:
            myRange = xl.Range(excelRangeStr)
        except:
            logging.error("isCacled failed %s is not a valid range"%str(excelRangeStr) )   
        for theParameter in myRange:
            vHasFormula = theParameter.HasFormula
            if vHasFormula == True:
                logging.info("isCalced:HasFormula for %s"%str(theParameter.Address))
                if xl.WorksheetFunction.CountA(theParameter) == 0:
                    logging.info("isCalced:False for %s"%str(excelRangeStr))
                    return False
        return True
    except:
        logging.error("isCacled failed with %s"%str(excelRangeStr) )
        logging.error(traceback.format_exc())
        return True

@xl_func("string blah, cached_object co, string isCacledRange : cached_object ", thread_safe=True)
def doSomethingLong(blah, co, isCacledRange):
    if isCacledRange != "" and cacheObject.isCacled(isCacledRange) == False:
        return
    time.sleep(7)
    return [blah]
   
Kind regards,
Kris

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 574
  • Karma: +13/-1
    • View Profile
Re: isCalced
« Reply #4 on: August 21, 2014, 10:21:28 AM »
Hi Kris,

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.

Best regards,
Tony

kris.metha

  • Newbie
  • *
  • Posts: 13
  • Karma: +0/-0
    • View Profile
Re: isCalced
« Reply #5 on: August 22, 2014, 07:07:56 AM »
Thanks, Tony.

Looking forward to trying it.

kris.metha

  • Newbie
  • *
  • Posts: 13
  • Karma: +0/-0
    • View Profile
Re: isCalced
« Reply #6 on: July 06, 2015, 09:38:46 AM »
Hi Tony,

Hope you are well.

Just to check with you whether this functionality has been released?

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).

Kind regards,
Kris

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 574
  • Karma: +13/-1
    • View Profile
Re: isCalced
« Reply #7 on: July 06, 2015, 06:33:06 PM »
Hi Kris,

yes! it's done :) Well... it's in the beta version of 2.3 which you can download from here:
https://beta.pyxll.com/download.html

I'm expecting that build to be the final build. You can see the other changes here:
https://beta.pyxll.com/changelog.html

Best regards,
Tony