issue refreshing table return in excel while function well calculated

[Louis-Alexis]

Hi everyone, I'm having an issue when returning an array to excel with a UDF.
when i launch a shift + F9 to recalculate all the sheet, one of my UDF function is well called and seems to return the good result ( i've set a message box before returning the array and it seems that the array is good), but the result is not display in the cells and so it is the prior result that stay in the cells. Refreshing problem maybe ?
So the other functions in the sheet are recalculated with the wrong datas.
So i have to calculate this function manually and alone cto see the good array in the cells.
Does someone have an idea of why ?

thank you very much. here is the mask of the function :

@xw.func(volatile = True)
@xw.ret(expand='table')
def my_function(parameter1,parameter2):
    wb = xw.Book.caller()
    sht = wb.sheets['Sheet1']
    array= [sht.range((3,i)).value for i in range(1,100)]
    array= list(filter(lambda x : x is not None,array))
    if parameter1 in array:
        row = 5
        col = array.index(parameter1 )*5+1
        data = sht.range((row,col),(row+200,col+4)).value
        data = np.array(list(filter(lambda x : x[0] != None,data)))
        new_data  = []
        for x in data:
            if x[0] <= parameter2 and x[3] !=0 and x[4] != 0:
                new_data.append(list(x))
            elif x[0] > parameter2 and x[1] != 0 and x[2] != 0:
                new_data.append(list(x))
        msg_box(new_data)
        return new_data
    else:
        return None

[fzumstein]

Get rid of all the wb/sht objects and pass your ranges as parameters

[Louis-Alexis]

thank you very much for your answer but it doesn't seem to work better. i can describe the situation more precisely :
it seems that the data are updated but at the end of the refresh vba macro ( a macro that i wrote enabling to refresh the sheet in a certain orderm cells by cells )
worst ! my function return a 2d array with 5 columns and a unknown numer of rows, and just the first column is updated at the beginning of my procedure, but not entirely .... it is really weird and really painfull, is there a wayto refresh cells in excel ? not recalculate for it seems that it well calculates the function but just refresh ?

[DougAJ4]

I am having similar problems. I have a sheet with a number of UDFs calling Numpy functions. On opening the spreadsheet some of the results show an error in the first item. If I recalculate everything with Ctrl-Alt-F9 some of the errors are fixed, but others appear elsewhere. If I select the functions individually and refresh with F2, Enter, they always return the correct result.
A related problem is that if I enter text in the function output range, below the entry cell, or delete a result, it doesn't update automatically, or with ctrl-alt-F9.

Typical python code is shown below, and I have attached two screen-shots, both after a full-recalc.

@xw.func
@xw.arg("x", np.array, ndim = 2, dtype = np.float64)
@xw.ret(expand='table')
def xl_isfinite(x):
    return np.isfinite(x)

[rhkleijn]

Count me in as well. I have also encountered this problem that dynamic UDFs (those with the @xw.ret(expand=...) decorator) do not consistently update the cells outside the cell containing the formula especially in larger workbooks in which the output of a dynamic UDF is used in subsequent calculations.

I regard dynamic UDFs as one of the nicest features of xlwings. Unfortunately, as long dynamic UDFs cannot be fully relied upon to always correctly recalculate results automatically it is prohibiting me from sharing my xlwings-workbooks with dynamic UDF's with my coworkers. I have used them myself for some time, refreshing manually with F2, Enter.

The problem
A couple of months ago I decided to dig into the source code of xlwings and noticed that xlwings retries at most 10 times before it drops the task. Retrying is needed when Excel temporarily doesn't allow writing because it is busy with something else.

Towards a solution
I hacked the source code of my local xlwings installation to not limit the number of retries while at the same not clogging the system. I implemented this by retrying periodically (at least every 100 ms) or earlier when another task arrived. For me this has made a lot of difference.

While searching GitHub I noticed PR #872 by @Colin-b who is also trying to solve this same problem. His approach has some resemblance to mine, with two differences. A minor one is that his PR introduces a second queue whereas I requeue a task again at the front of the original queue. A more important one is that it does not have the periodic polling which I found to be necessary as one cannot be sure that there will always be another task arriving to trigger the retrying of an earlier one.

I have never used git before, but I managed to create a personal fork of xlwings at https://github.com/rhkleijn/xlwings/tree/robust-task-retry) and put (a cleaner version) of my approach in there.

Feel free to try it and if you find this useful, I can submit it as a PR.

[Colin-b]

I introduced the 10 times retry mechanism when encountering this issue for the first time. And with a whole lot more udf being called 'at once' by Excel, I updated it to this new PR you are talking about. It is implemented in a forked version and used since months already so it covers my use case. I am more than willing to consider removing it if you have a nicer solution solving another problem then, do you know why your Excel is returning busy if it is not because he is still calling udfs ?

…

On Sat, May 26, 2018, 22:08 rhkleijn ***@***.***> wrote: Count me in as well. I have also encountered this problem that dynamic UDFs (those with the @xw.ret(expand=...) decorator) do not consistently update the cells outside the cell containing the formula especially in larger workbooks in which the output of a dynamic UDF is used in subsequent calculations. I regard dynamic UDFs as one of the nicest features of xlwings. Unfortunately, as long dynamic UDFs cannot be fully relied upon to always correctly recalculate results automatically it is prohibiting me from sharing my xlwings-workbooks with dynamic UDF's with my coworkers. I have used them myself for some time, refreshing manually with F2, Enter. *The problem* A couple of months ago I decided to dig into the source code of xlwings and noticed that xlwings retries at most 10 times before it drops the task. Retrying is needed when Excel temporarily doesn't allow writing because it is busy with something else. *Towards a solution* I hacked the source code of my local xlwings installation to not limit the number of retries while at the same not clogging the system. I implemented this by retrying periodically (at least every 100 ms) or earlier when another task arrived. For me this has made a lot of difference. While searching GitHub I noticed PR #872 <#872> by @Colin-b <https://github.com/Colin-b> who is also trying to solve this same problem. His approach has some resemblance to mine, with two differences. A minor one is that his PR introduces a second queue whereas I requeue a task again at the front of the original queue. A more important one is that it does not have the periodic polling which I found to be necessary as one cannot be sure that there will always be another task arriving to trigger the retrying of an earlier one. I have never used git before, but I managed to create a personal fork of xlwings at https://github.com/rhkleijn/xlwings/tree/robust-task-retry) <https://github.com/rhkleijn/xlwings/tree/robust-task-retry> and put (a cleaner version) of my approach in there <rhkleijn@175d6da> . Feel free to try it and if you find this useful, I can submit it as a PR. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#880 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AHPX_T1GqgT_Fze6AoXGEB1PvjZTTYMQks5t2bZVgaJpZM4UNko4> .

[rhkleijn]

Thanks for your reaction. I’ll share my solution as a PR. I expect it to work in all cases where your solution works plus some more.

I suspect Excel is busy (by which I mean in this context that it returns RPC_E_SERVERCALL_RETRYLATER) not only from its regular recalculation activities (walking its dependency graph and evaluating formulas, including calliing UDFs), but that the delayed writes from dynamic UDFs cause all kinds of additional (blocking) activities.
From Excel’s perspective xlwing’s delayed write for dynamically expanding UDFs is a combination of things like clearing cells, accessing object properties, and writing values to cells which in turn can trigger new calculations (including updating the dependency tree) and e.g. screen updates.

[Louis-Alexis]

thank's a lot for your answers ! but i think that it is incredible haha now the first function that wouldn't be refreshed on excel is now well calculated and well refreshed but it seems that my others functions aren't refreshed ...
I have to enter multiple times shift-F9 in order to be sure that all my sheet is calculated
I think that the problem is when we mix UDFs and excel formulas isn't it ? because when my first udf function is calculated, i have other cells that depends on the array returned by the function, in fact i copy on of the array returned column in an other place in the sheet so my formula is just something like this {=A5:A62} where A5 to A62 is one of the array returned column

[Louis-Alexis]

I've tested different tyhings like replace all my formulas to have only UDFs but it doesn't work much more. interestingly, the first function to be called calcul well but doesn't seem to display the return result before excel call the next function .... so my second function take the wrong input to make all its calculus

[DougAJ4]

Louis-Alexis, this has been recognised as a bug, but it is only a problem when you use 'expand = table'. If you remove that line you can still enter the UDFs as array functions, and they will update when ever any input data changes, or you do a global recalculation.

[Louis-Alexis]

DougAJ4 okay, thank you very much !

[rhkleijn]

Louis-Alexis, especially the combination of volatile=True with expand='table' may be problematic. The xlwings documentation states

The way that dynamic array formulas are currently implemented doesn’t allow them to have volatile functions as arguments.

The UDF itself being marked volatile=True may give the same kind of problem, because by using expand='table' cells outside of the calling cell may be changed which will trigger recalculation when volatile=True, even if those cells are not referred to in any formula.

Can you retry with @xw.func(volatile=False) or does your application require the UDF to be volatile?

[Louis-Alexis]

Okay ! i tried with volatile = False
But it doesn't seem to work better> I have to press shift-F9 multiple times in order to be sure that all the sheet is refreshed. it is a little bit painful in fact :/
Is there a problem when output data of a UDF are input data of an other UDF ?

thank's for your support

[Louis-Alexis]

I've just found something really interesting. I've created a macro that calcul step by step the cells, columns, rows i want, in the good order (using selection.calculate) and i've put a button. it seems to work better but in order to have your sheet refreshed, you need in that way to click on the button and then move the mouse out in order to refresh the dynamics arrays ... haha that's really weird.

EDIT :
But anyway sometimes i also have to click multiple time on the button ...

EDIT :
It seems that there is a problem with form button of excel and it is better to use ActiveX Button

[mg3146]

I'm also having this same issue, and my function is pretty basic. No expanding table or anything like that. Just some date calculations.

@xl.func
def RT_GetNextIMMDate(the_date, monthlyIMM = False):
    the_date = datetime.datetime(the_date.year,the_date.month,the_date.day).date()
    the_date = fdate.getNextIMMDate(the_date, monthlyIMM)
    return the_date

[ghost]

Hi I have the same problem. I created a excel function UDF to do a tensorflow neural network. If I try to run several cells in one go (with different inputs), then after 3-4 cells it exists with "Automation Error" message. I tried using search and replace = by =, tried selection.calculate, SHIFT+F9, nothing helps. More than 3-4 cells and it stops.

I am using windows 10, excel office profession plus 2016, Python 3.6.6 :: Anaconda custom (64-bit), xlwings version 0.11.8.

[fzumstein]

fixed by #881