Unexplained slowness of method 'Invoke' of 'PyIDispatch' objects

[airguru]

Hello,

I have succesfully written a fairly complex library and it works fine on almost all machines that I have deployed it so far. These had different hardware, operating system versions, Excel versions, antivirus softwares, security configurations etc.

However, on one specific machine, which is almost the most HW performant of them all, one of my macros performs very slow. It involves some spreadsheet cell modification within PauseExcel() block. In profiler, I see that most of the time is spent during: ~:0(<method 'Invoke' of 'PyIDispatch' objects>), where a total of 18 calls are performed, with 0.2s per call for a total of 3.7s (95% of total execution time). In all other environments so far this is not a problem at all and is at least 5-10x faster.

I have compared everything I could between my and target environment, and could not find any meaningful difference. This is on xloil 0.18.6.

Do you have any idea what could be the source of the issues, where to even start looking?

Thanks in advance.

[cunnane]

Sorry, missed a reply here. PyIDispatch is part of win32com so outside of xlOil, so I can see a few things to try:

1. As far as possible, disable security software and remove other Excel addins to eliminate effects from these
2. If possible, alter the macro to avoid using calls through win32com (properties/methods with CamelCase) and prefer using xlOil accessors (properties/methods with snake_case). If the methods you need don't exist in xlOil I can look at adding them.
3. Consider surrounding the code with xloil.events.pause() and xloil.events.allow(). This stops xlOil's event mechanism, whereas PauseExcel suppress's Excel's events. I think this is unlikely to work, but is easy to try.

Let me know how this goes!

[airguru]

I am still on track of this issue!

New findings: I managed to narrow down the source of this problem, it happens during copy & paste format call:

xlo.active_workbook()['range_name'].to_com().Copy()
target_range.cell(r,0).PasteSpecial(c.xlPasteFormats)

I will try to narrow it down if it's something specific in the formatting, that may be causing problems.

I have also tried to reimplement this by manually copying all format-related properties of a cell, (Interior, Font etc.) but so far it seems to be a quite complex task that doesn't yield good results.

The strange thing still is, that it does happen on some machines, while not on others. Where it happens, I am now also getting this notice in excel, maybe it may be related?