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.
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?