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]

maybe this can do it, I used similar to read the installed references, and then further work is used to ensure that when you load a workbook you ensure the references you need already exist

https://social.msdn.microsoft.com/F...able-references-of-a-vba-project?forum=isvvba

VBA Express : Excel - Determine the File Path or GUID to a VBA Reference Library

 

[mole999]

some workings

Private Sub GetGUID()
    Dim ref
    For Each ref In ThisWorkbook.VBProject.References
Debug.Print ref.Name, ref.GUID, ref.Major, ref.Minor
    Next
End Sub

In ThisWorkbook

Private Sub Workbook_Open()
[/B]Dim ID
    'Exit Sub
    On Error Resume Next
    'Reference ADO Object Library using Major / Minor GUID
    Set ID = ThisWorkbook.VBProject.References
    'ID.AddFromGuid "{00000205-0000-0010-8000-00AA006D2EA4}", 2, 5 'Microsoft ActiveX Data Objects 2.5
    ID.AddFromGuid "{00000206-0000-0010-8000-00AA006D2EA4}", 2, 5        'Microsoft ActiveX Data Objects 2.6
    ID.AddFromGuid "{00025E01-0000-0000-C000-000000000046}", 2, 5        'Microsoft DAO 3.6
    'ID.AddFromGuid "{8E27C92E-1264-101C-8A2F-040224009C02}", 7, 0        'MSCAL.OCX 2007
    ID.AddFromGuid "{3050F1C5-98B5-11CF-BB82-00AA00BDCE0B}", 4, 0        'HTML
    ID.AddFromGuid "{EAB22AC0-30C1-11CF-A7EB-0000C05BAE0B}", 1, 1        'IE

 

[lalbatros]

Thanks mole999,

The GUID inspection might prove useful when I continue with testings.
I did and will do so many tests that observing the GUIDs might clarify things from time to time.
It will also allow me to identify items that I observe when:

- analyzing execution with procmon
- or when analyzing assembly bindings with fusion log viewer

However, I have the strong feeling that references in VBA are not the reason of the problem.
Indeed, on the same machine, in the same excel file, I can test these two snippets above.
On boxes at work, the first snippet will always work, while the second will never work.
On other boxes, both snippets will work.

I thing that referencing an object library in Excel-VBA does very little: it remembers a GUID to identify the library.
When the Excel file is opened, the GUID is immediately used to resolve the path to the object library and make references immediately usable.
Therefore, it cannot explain (I guess) why one snippet could work and not the second.

Nevertheless, I have to dive in the registry and tools are always useful.

Thanks,

Michel

 

[mole999]

if the reference isn't installed the first will identify the difference between any machine, the second is the fix should you find that is the issue.

just noticed you seem to declare str As String, on the one that works, what has excel decided it is, Len suggests to me a number

 

[lalbatros]

You are right, adding references in code could be a very useful feature.

You are also right that my problem is about passing the right type of argument or not.
When a string is passed it works on all machine.
When an Excel Range(Cell) is passed, it works "all around the world" but not at work.

I have not even a basic understanding of the functioning of COM and its interop with .net .
However, I should not be too wrong to say that COM includes a mechanics that proceed to type conversions when necessary.
I think this is what is called "marshaling".
It is clear that marshaling can only work, in my context, if the tools are available to convert a Range to a string.
This means that some piece of code must be able, on behalves of Excel, to read the type of parameter which is expected (maybe in the registry, or in the dll, or in the tlb) .
After that, this piece of code must call Excel to convert the Range to a string.
And finally, my assembly must be called and the result be returned properly.

Experiments at work show that the method in my library is not even called when the type of the parameter is not as expected.
In contrast, type conversion and a call to the method does occur on any other machine in the world.
It looks like that the boxes at work are not as intelligent as other boxes!
But I have a box at work where Visual Studio is additionally installed, and this box does work correctly.

During the weekend, I hope to find the time for at least two tests on my standard box from work:

- un-install Office2010-pro-plus and install a simple Office2010-pro, see if it solve the problem
- install Visual Studio 2010 and see if there is a change

Somehow if one of these tests is successful, I would be only half happy!
That's because I have no idea how I could then help my colleagues at work without forcing then to additional install on their machines.
In addition, that would sound a little bit crazy, since it works without install anywhere else in the universe!

Thanks for your suggestions that are stimulating.

Michel

 

[mole999]

i doubt uninstalling pro plus will help you, i think clutter is left in the registry and runtimes that could make you doubt yourself, and just provide a few hours of head scratching, and wouldn't sort the work environment.

Hope you find the Eureka moment

They are all 32 or 64 bit setups aren't they

 

[lalbatros]

You are right. The clutter and the time are the reason why I delayed that experiment.
However, months ago I had evidences that this might be a possible reason of the problem.
But I can't remember what where these evidences.
Maybe testing (and taking note) will clarify that once for all.
I will wait a while and go on only if I have no idea.
The only price in the end might to ask our IT dept for a fresh image.
(That's expensive, to be honest.)

We are all Windows 7 64 bits and Office 32 bits.
Even the free machines outside that I had access to are in this configuration.

This evening I will dive again in the registry with the process monitor.
Without too much conviction!
I had hope to see sometimes when "marshaling" would occur, but I saw nothing such.
However, when the call is not working, I saw more output records to the process monitor.
Maybe I should look carefully, as there could be more than error processing.
Maybe also the clue might be seen on another process than EXEC.exe (my understanding of processes is very low).

Thanks

Michel

 

[mole999]

last few ideas

add

Option Explicit

at the top of each VBA sheet / module

recompile on the affected machines

 

[lalbatros]

Hello mole999,

Below is the new VBA test I made.
I used your suggestions and was curious about the outcome.
I also changed the COM-visible assembly (names).
Reason is that I wanted to use different names for anything different in the C# project.
In this way the log files from the procmon or fusion logs utilities are clearer.
I also used a common object o to avoid binding everytime and therefore reducing the useless lines in the log files.

The result is unfortunately as expected!
On "normal" machines, test1 and test2 execute properly.
On my test machine from work, test1 fails and test2 succeeds.

I also listed the references, and saw the same thing on any machine.

Thanks for the suggestion that makes things more and more clear.

Michel

______________________________________

Option Explicit
Dim o As Object

Function test1(s)
On Error Resume Next
Err.Clear
If o Is Nothing Then
Set o = New asusAssembly.ClassAsus1
End If
Dim arg As Range ' <<< not a string, which should ask for marshalling
Set arg = s
Dim x As Variant
x = o.len3(arg)

If Err.Number <> 0 Then x = Err.Description
test1 = x
End Function

Function test2(s)
On Error Resume Next
Err.Clear
If o Is Nothing Then
Set o = New asusAssembly.ClassAsus1
End If
Dim arg As String ' <<< a string ! no conversion needed
arg = s
Dim x As Variant
x = o.len3(arg)

If Err.Number <> 0 Then x = Err.Description
test2 = x
End Function

Sub listRef()
Dim ref As Object
For Each ref In ThisWorkbook.VBProject.References
Debug.Print ref.Name, ref.GUID, ref.major, ref.minor
Next ref
End Sub