We are running the python function via xlwings on excel-macro as shown below.However, it is treated as a volatile function (recalculated as Open, Any-cell changes).
What we've checked so far is
1) The vba side suddenly returns a value such as 0.→ Only this changes to non-volatile
MLMprdct=0'Py.CallUDF(Tar_pyfile, "MLMprdct", Array (Model, Labels, Xn, OutRs, OutCs, enslst), ThisWorkbook, Application.Caller)
2) Return the value immediately on the python side.→Stay volatile
return(Xn[:,:2])#(MLMprd_calc(Mdlfile, Labels, OutRs, OutCs, lst, Xn))
3) Xn passes Range (value on Python side) as Value, and Python side does not touch Excel at all→Stay volatile
vba:
MLMprdct=Py.CallUDF(Tar_pyfile, "MLMprdct", Array (Model, Labels, Xn.value, OutRs, OutCs, Enslst), ThisWorkbook, Application.Caller)
python
#@xw.arg('rXn', xw.Range)
##def MLMprdct(Mdlfile, Labels, rXn=', OutRs=1, OutCs=2, lst='):
def MLMprdct(Mdlfile, Labels, Xn, OutRs=1, OutCs=2, lst='):
Xn=np.array(Xn)#rXn.resize(OutRs).expand('right').options(np.array).value
Does anyone say, "My xlwings-UDF function is non-volatile?" If so, could you tell me?
vba:
Public Function MLMprdct (Model As String, Labels As String, Xn As Range, Optional OutRs As Long = 1, Optional OutCs As Integer = 2, Optional senslst As String = "")'Private
Application.Volatile (False) 'don't work
sta=Time()
If TypeOf Application.Caller Is Range Then On Error GoTo failed
MLMprdct=Py.CallUDF (Tar_pyfile, "MLMprdct", Array (Model, Labels, Xn, OutRs, OutCs, enslst), ThisWorkbook, Application.Caller)
Debug.Print "MLM:", Application.Caller.Address(, , , True), Time()-sta
Exit Function
Failed:
MLMprdct=Err.Description
End Function
python: (top layer only)
@xw.func
@xw.arg ('rXn', xw.Range)
def MLMprdct(Mdlfile, Labels, rXn=', OutRs=1, OutCs=2, lst='):
Xn=rXn.resize(OutRs).expand('right').options(np.array).value
return(MLMprd_calc(Mdlfile, Labels, OutRs, OutCs, lst, Xn))
It's a self-answer. It's simple. Python doesn't matter.Finally, if you look at other cells with the following vba functions, if there is a volatile function such as Offset in the reference (see...), the recalculation communicates and eventually recalculates whatever cell you update.In other words, just updating a certain sheet in the book will recalculate all sheets that contain Offset.I finally understand.
The only way to do this is to use 1) Offset, or 2) Worksheets ("Sheet 1").EnableCalculation=False to minimize the scope of calculation when updating.
= testudf(Z26)
Function testudf(ind)
testudf=ind+1
Debug.Print "testudf", Application.Caller.Address, Now()
End Function
© 2024 OneMinuteCode. All rights reserved.