My C# COM-visible assembly can be used in Excel everywhere but not on standard machines at work

[lalbatros]

Hello,

I need fresh ideas.

I have a very hard question (more than one year haunting me).
I developed a COM-visible assembly in C#.
This assembly contains computing functions intended to be used in Excel-VBA.
I also created a setup program to install the assembly on user's machine.

It works perfectly on my development box where VS is installed.
When installed on many machines at home or by friends, it also worked perfectly.

Unfortunately, it doesn't work perfectly when installed on any machine at work.
This problem can be reproduced with a very small test assembly.

This Excel-VBA snippet works on all machine:


Function test(str As String)
Set obj = New netcom2vba.netcom2vba
test = obj.myLen(str)
End Function


While this snippet works everywhere but not at work:


Function test(str)
Set obj = New netcom2vba.netcom2vba
test = obj.myLen(str)
End Function


At work, it returns the following error message observed when debugging:

*** Class does not support Automation or does not support expected interface ***


This suggests clearly that at work something is missing.
The missing piece makes Excel-VBA unable to make the necessary type conversions automatically.
It works only when the type of the parameter needs no conversion.

You can't imagine the time I spent to try to find out the missing piece or any obstacle.
For example, I used procmon to observe file access and registry accesses, but I could not pinpoint something useful.
I also used the Fusion Logs Viewer to observe any assembly binding problems, but saw no useful difference between successful machines and failing machines.

How could you help me?
I am now short of ideas.
So, I need fresh ideas.
Ideas about where to look, questions to ask myself, tests to be done.
Maybe some reading you remember or some experience that may put me on track.

Thanks for your suggestions,

Michel

 

[mole999]

by default excel will turn
Dim arg into a variant

beyond that I can't test

 

[lalbatros]

I made the whole tiny test available on onedrive, see the link here below.
In the AsusTestSetup\Debug folder you can find the setrup for the tiny test.
If you want you can try this test.
But the outcome is 99.9% of chances that it simply works.
It is only on machines at work that it fails.

I want to make avoid typing the parameter because this would be too difficult for the actual assembly.
The actual assembly is a translation from Java(J++) to C# from a package that I wrote 15 years ago.
There are many Excel workbook based on this package and I hope I could avoid modifying the VBA in all of these workbooks.
In addition, it would be unlogical to do so, since it systematically work on "normal" machines, and it only fail on machines at work.
Also, in these actual workbooks, I use "late binding" by CreateObject.
Therefore, not only I don't have to worry about references, but I could (did) make a one-to-one replacement of the J++ library by the new C# translation, by simply using the same progId.

Thanks

PS:
I had two or three discussions already on this topic on MSDN.
See for example: https://social.msdn.microsoft.com/F...ne-in-my-company?forum=exceldev&prof=required

https://onedrive.live.com/redir?res...46&authkey=!APpJWZdgWLuGDQ4&ithint=folder,lnk

 

[mole999]

this may or may not be something

In LateBinding version

I set a breakpoint on Err.Clear

and excluded the On Error Resume Next

Trigger the function and as stepping through

beyond this line Set o = CreateObject("asusAssembly.ClassAsus1")

the rest of the function does not execute TEST2 also has the same outcome

Early binding reports

Compile Error Can't Find Project or library

 

[mole999]

OK my tests

before your msi install
[TABLE="width: 527"]
<colgroup><col></colgroup><tbody>[TR]
[TD]VBA {000204EF-0000-0000-C000-000000000046} 4 0
[/TD]
[/TR]
[TR]
[TD]Excel {00020813-0000-0000-C000-000000000046} 1 6
[/TD]
[/TR]
[TR]
[TD]stdole {00020430-0000-0000-C000-000000000046} 2 0
[/TD]
[/TR]
[TR]
[TD]Office {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52} 2 4
[/TD]
[/TR]
</tbody>[/TABLE]

after
[TABLE="width: 527"]
<colgroup><col></colgroup><tbody>[TR]
[TD]VBA {000204EF-0000-0000-C000-000000000046} 4 0 [/TD]
[/TR]
[TR]
[TD]Excel {00020813-0000-0000-C000-000000000046} 1 6 [/TD]
[/TR]
[TR]
[TD]stdole {00020430-0000-0000-C000-000000000046} 2 0 [/TD]
[/TR]
[TR]
[TD]Office {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52} 2 4 [/TD]
[/TR]
[TR]
[TD]asusAssembly {EA9D130E-706F-404A-8E0B-70BDC55E8DD1} 1 [/TD]
[/TR]
</tbody>[/TABLE]

with late binding I get
[TABLE="width: 743"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]Late[/TD]
[TD]test1:[/TD]
[TD]qqq[/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]test2:[/TD]
[TD]dddd[/TD]
[TD="align: right"]64[/TD]
[/TR]
</tbody>[/TABLE]


with Early I get
[TABLE="width: 743"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]Early[/TD]
[TD]test1:[/TD]
[TD]qqq[/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]test2:[/TD]
[TD]wdff[/TD]
[TD="align: right"]64



[/TD]
[/TR]
</tbody>[/TABLE]
what I do notice in early binding is that I get errors initally, but if i edit C6 and C7 then they resolve as expected

BTW windows 10 64 bit and all versions of excel 2003, 2007, 2010 and 2013 though only tested on the 2007

not sure if dddd and wdff are supposed to provide the same result

 

[lalbatros]

Hello mole999,

Thanks for testing.
Once more it works on a "normal" machine.
The result depends on the number of character in the string, it does not matter.

Thanks

 

[lalbatros]

I must now confess what I did this evening!
I uninstalled Office2010 Pro Plus, and installed Office 2010 Pro from a personal CD.
It didn't change anything.
I then undid what I did and it didn't change anything either.
That was for the records, let's say.

Next step will be patient tests with progmon, but I will need a good night an fresh air!!!

Thanks again!

Michel