In Python 3, I use UDFs in Excel via xlwings to calculate a formula. The formula is calculated more than 4,000 times, and it takes about 25 seconds to update the worksheet. The following formula is used as an example. The formula is called in Excel in each Excel cell, using the formula with reference to the cells, =test_1(B20,C20,D20) . The Optimized VBA Connection parameter is set to true, OPTIMIZED_CONNECTION = True .
@xw.func def test_1(x, y, z): a = x**2 + y**2 + z**2 return a
Calculating the same formula in VBA or Excel is almost instantaneous. So my question is: why is it so slow and is there a way to improve speed?
* New information
Using array formulas is much faster than calling UDF several times. The formula below does the same as the original formula, but takes the range as input and returns the range.
@xw.func @xw.ret(expand='table') def test_array(x, y, z): a = np.array(x)**2 + np.array(y)**2 + np.array(z)**2 return np.transpose(a[np.newaxis])
This is a good workaround when you can use it. However, in cases where this is not possible, the problem remains.
source share