Application.Run looks for TLBs on %HOMEDRIVE%%HOMEPATH%\WINDOWS ?

Oct 19, 2013 at 10:49 PM
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 https://exceldna.codeplex.com/discussions/252721)

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.
Coordinator
Oct 20, 2013 at 2:17 PM
Edited Oct 20, 2013 at 2:33 PM
Hi,

I have no idea how Application.Run works. I presume you have access to Microsoft support - maybe that's worth a try.

The Excel-DNA COM registration will try to register a .tlb file, or register a packed .xll as the TypeLib for the COM Server. Are you indeed generating a .tlb file? Are you using a packed .xll?

Depending on permissions, Excel-DNA might register the COM stuff under the HKCU key. I wonder if the user's hive of the registry is not stored in %HOMEDRIVE%%HOMEPATH%\WINDOWS to enable roaming, and that this has something to do with it.

Does it make any difference what you are calling with Application.Run? Is is a something defined in the VBA module or an Excel-DNA macro or some built-in Excel thing?

Since your situation sounds quite unique, if you need further help, I suggest you contact me directly.

Regards,
Govert
Oct 23, 2013 at 4:06 PM
Govert,

Thanks for your response. We are generating a tlb file, and it's not packed.
The COM stuff is registed in HKCU, and the location of the tlb is correctly recorded.

I did a lot of digging with procmon and process explorer, and I found the following:
Excel searches for TLBs in their last known location, and this location is stored inside the XLSM.
If it doesn't find them there, then it looks in a whole series of other locations. Here's an example of all the queries that it does:

QueryOpen C:\p4\chapmanb_ws\DTRE\branches\v10.4\main.br\bin PATH NOT FOUND
QueryOpen C:\p4\chapmanb_ws\DTRE\branches\v10.4\main.br PATH NOT FOUND
QueryOpen C:\p4\chapmanb_ws\DTRE\branches\v10.4 PATH NOT FOUND
QueryOpen C:\p4\chapmanb_ws\DTRE\branches PATH NOT FOUND
QueryOpen C:\p4\chapmanb_ws\DTRE PATH NOT FOUND
QueryOpen C:\p4\chapmanb_ws PATH NOT FOUND
QueryOpen C:\p4 NAME NOT FOUND
QueryOpen C:\Documents and Settings\chapmanb\My Documents SUCCESS
QueryOpen C:\Program Files (x86)\Microsoft Office\Office12\BarCap.CRT.Pricing.Presentation.MessageLogger.tlb NAME NOT FOUND
QueryOpen C:\Documents and Settings\chapmanb\My Documents\BarCap.CRT.Pricing.Presentation.MessageLogger.tlb NAME NOT FOUND
QueryOpen C:\WINNT\SysWOW64\BarCap.CRT.Pricing.Presentation.MessageLogger.tlb NAME NOT FOUND
QueryOpen C:\WINNT\system\BarCap.CRT.Pricing.Presentation.MessageLogger.tlb NAME NOT FOUND
QueryOpen C:\WINNT\BarCap.CRT.Pricing.Presentation.MessageLogger.tlb NAME NOT FOUND
CreateFile \LDNPSN01015N01\user0065$\user\0065\chapmanb\WINDOWS\BarCap.CRT.Pricing.Presentation.MessageLogger.tlb

It seems to be a built in beavhiour of Excel to search in all these locations, even though it actually knows where the TLB is, and has already loaded it.

I don't think it's anything to do with Excel DNA at all.

Would be very interested to hear from anybody who knows about this behaviour.

Ben.