Allow to use Threading and Dynamic Array Formulas

[Colin-b]

Hi,

For now dynamic array formulas are prefixed with the implicit intersection operator (@) when using threading on generated user defined functions.
Meaning that a single value is shown instead of the expected array.

import xlwings as xw

@xw.func(async_mode='threading')
def many_values():
    return ["test", "test2"]

The issue comes from the fact that Range.Formula is used when updating the formula to trigger a new call retrieving the cached result:

xlwings/xlwings/udfs.py

Line 39 in 7af1737

apps[self.pid].books[self.book].sheets[self.sheet][self.address].formula = \

Leading to the underlying use of Range.Formula as in the following:

xlwings/xlwings/_xlwindows.py

Line 743 in 7af1737

def formula(self):

Switching to Range.Formula2 fixes the issue.

I will gladly provide a pull request but I was wondering about the design approach for this fix, so first I would like to check with you if the following can be merged:

• Exposing formula2 (as a property, the same way it is done for formula) inside xlwings._xlwindows.Range and xlwings._xlmac.Range classes.
• In xlwings.udfs.AsyncThread.run, try to set formula2 with the previous formula2, if it fails, fallback to previous behavior (setting formula with the previous formula).

However I would point out that I don't have access to a Mac then I am unable to test the behavior on Mac, that's why I am proposing to use a try/except block on the operation, as I am unaware of the result.

Also, I don't have access to a version of Microsoft Excel that does not have Range.Formula2, so I am unable to test the behavior when Range.Formula2 is not available, that's why I am proposing to use a try/except block on the operation, as I am unaware of the result.

According to my tests, Range.Formula2 works fine with arrays and single value return types.

[fzumstein]

Mac currently doesn't support UDFs, so you don't have to worry about that part. You'll most likely get a pywintypes.com_error if the formula doesn't exist, so try/except sounds good. I'll be able to test it.