1

I have noticed that when using VBA to open different workbooks within another instance of the Excel application, large loops run much slower when reading data from the second application instance than if I simply bind the different workbooks to the original instance.

I have tried both early and late binding methods as well as extensive troubleshooting to resolve this. I am wondering if it just takes the system longer to communicate between two instances of itself. I have also noticed the second application uses about less than half the memory as the first instance (not sure if this is relevant).

One reason for wanting to utilize two separate instances of the Excel application is to avoid the outer perimeter window flicker when opening several workbooks at once within the host application. The workaround is to open those workbooks in a different excel application instance. However, this is where I encounter significant performance loss.

Infrequent Coder
  • 79
  • 5

1 Answers1

0

Early binding is quicker than late binding. But is not that different.

Early binding jumps to the offset in the function table (VTable), ie the address at position 5.

Late binding takes 4 function calls to early binding's 1. However your program spends little time binding. Note each . requires the function calls ie 1 for early and 4 for late.

In and Out of process is significant.

In in process the dll is loaded, COM gets out of your way and your program just jumps to the address in the table.

In Out of process COM load a dll that simulates the Excel app called a proxy. You call the com proxy, it marshals the data and sends it to the other Excel via an networking protocol called Remote Procedure Call (RPC). Then it happens in reverse.

See Is using variants in vba bad for performance? for why your code may be slow.

Did you discover why your code was slow?

Lundt
  • 142
  • 1
  • 1
  • 3
  • I see. So, this explains why the performance is so slow when communicating with a second instance of the Excel application. Is there a reason why speed is not affected when creating a single instance from an external application such as Outlook? – Infrequent Coder Aug 27 '22 at 23:06
  • If you shuffle the same data outlook will be the same speed. See https://stackoverflow.com/questions/27939826/is-using-variants-in-vba-bad-for-performance for why your code may be slow. – Lundt Aug 27 '22 at 23:11