I have an Excel 2007-based application, in which we've recently started to make use of ExcelDNA.
The workbook and XLL are deployed onto Citrix servers based in London. It works well, however, users in non-London locations, such as Singapore, experienced very poor performance. The cause turned out to be a slowdown in the performance of calls to Application.Run,
which started taking a minimum of 0.4s to run.
After using a packet sniffer, we discovered that during Application.Run, Excel was doing directory searches for .TLB files in the directory %HOMEDRIVE%%HOMEPATH%\WINDOWS.
Now the HOMEDRIVE location for a SGP based user is physically in SGP, and the network hop from Excel in LDN to a SGP drive was causing the performance degradation.
The .TLB file it was searching for is listed in Tools/References of the Excel workbook, but is unrelated to the macro we were trying to run using Application.Run. The .TLB files are located on the C: drive of the Citrix server, same as the Windows OS.
It's a complete mystery as to why Excel decided to start searching %HOMEDRIVE%%HOMEPATH%\WINDOWS. Nothing that I am aware of uses this directory, or is registered there.
This problem only started happening after we created some .NET components, which are COM-visible and used in VBA, and which we register at startup using the ExcelDNA COMserver registration trick (as described here
My question is:
Why excel would be searching this location for TLBs? Does the ExcelDNA COMServer registration do something that could cause this? We never saw this before we started using Excel DNA, hence the post here.
I've been unable to find any documentation that describes the mechanism that Application.Run uses to identify "runnable" methods.