none
COM Visible Assembly. Method called in VBA works on development machine, not on user machine. Method signature involved. RRS feed

  • Question

  • Hello,

    I created a COM-visible assembly as well as a setup project.

    Using the assembly from Excel-VBA on the development machine made no problem (no setup needed).
    However, when setting up the assembly on a user machine, the same VBA code did not work.

    The VBA code calls a method in the assembly whose C# signature is  

    CreateDevice(string name, string type)


    However, the VBA code does not type the parameters, which means that the method is called as

    CreateDevice(Range name, Range  type)

    I have three questions:

    Question 1
    What could I do on the C# projects side to solve the problem and avoid modifications to the VBA project?
    There are many Excel files in the wild using an old (J++) version of this library.

    Question 2
    What should I read to understand what happens? 

    Question 3
    Why does it work on the development machine and not on the user's machine?

    Thanks for your suggestions,

    Michel






    • Edited by Lalbatros Saturday, July 18, 2015 4:41 PM
    Saturday, July 18, 2015 4:38 PM

Answers

All replies

  • Hi Michel,

    What kind of assemble are you developing? Based on my understanding, we can call code in an application-level add-ins or expose code to VBA from document level customization.

    And here are the helpful links for this topic:
    Calling Code in Application-Level Add-ins from Other Office Solutions

    How to: Expose Code to VBA in a Visual C# Project

    If you are developing a common COM-visible assemble, I suggest that you reopen a new thread in C# forum to see whether it is deployed successfully to the user's computer.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, July 20, 2015 2:10 AM
    Moderator
  • Hi Fei, It is indeed a com-visible C# assembly that I developped, but with the aim to use it exclusively in excel. In the meanwhile I discovered that I can call the method in VBA when I pass the arguments with the exact type I used in C#. Only on the development machine I do not need to call with the exact parameter type. On the development machine, type conversion is performed, not on user machine. I begin to think this might be related to vsto being installed on the development machine. The reason is that I have also developped some vsto projects. Don't you thing that some library is missing on the user machine and that I should package it with my setup program? Would you know what I should add to my setup to have the user machine ready for my com-visible assembly? (including type conversion if needed) Which component of vsto could be the solution? Thanks, Michel
    Monday, July 20, 2015 9:21 PM
  • Hi Michel,

    >> I begin to think this might be related to vsto being installed on the development machine. The reason is that I have also developped some vsto projects. <<

    If you were developing com-visible assembly instead of VSTO solution, the VSTO runtime is not necessary. The VSTO only required for the VSTO solution. You can get more detail about VSTO solution from link below:
    Create VSTO Add-ins for Office by using Visual Studio

    >> Would you know what I should add to my setup to have the user machine ready for my com-visible assembly?<<

    Based on my understanding, we need to generate a type library and register assemblies with COM when it consumed by a COM application. You can get more detail about it from link below:

    Exposing .NET Framework Components to COM

    If you still have problem about building/deploy a com-visible assembly using C#, I suggest that you reopen a new thread in Visual C#.

    And if you have any problem with developing VSTO solution, please feel free to let us know.

    Regards & Fei 


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, July 21, 2015 2:45 AM
    Moderator
  • Thanks Fei. I have no problem developping vsto solutions. I have developped several. My feeling is that conversions (from Range to string for example) do not occur on my users machine, while they do on my development machine. Knowing how this conversion occurs on the development machine would probably tell me what to do for my user's machine. In addition, since the conversions involved are from Excel types (like Range) to standard C# type I believe than the missing piece on user's machine is Excel-related. Note that for this com assembly project I had excluded from the start the recourse to vsto. Simply because no vsto project matches my need here. However, my difficulty points to the need for some Excel stuff to help conversions. I browsed a lot, and I felt that this might be related to "marschaling"' maybe. Unfortunately, I could understand easily that stuff, and I could not pinpoint the missing piece I need to install on user's machine. If you knew more about that, you might have some good clue. Thanks, Michel
    • Edited by Lalbatros Tuesday, July 21, 2015 8:32 PM
    Tuesday, July 21, 2015 8:29 PM
  • Hi Michel,

    >>I have developped several. My feeling is that conversions (from Range to string for example) do not occur on my users machine, while they do on my development machine.<<

    Does the method work when you pass the string parameter in VBA? If it doesn't work, the issue may be failed to deploy com-visible assembly.

    If it works, would you mind sharing more detail about how we can reproduce this issue?

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, July 22, 2015 2:57 AM
    Moderator
  • Hi Fei, The method indeed work when passing paramters with the correct type ( string). It is not called at all otherwise, on my user's machine, while it is always correctly called on my development machine. Other methods, that have no parameters or are called with the proper type behave correctly on user's and development machine. I am on travel for several days without access to my development machine, but it would be a good idea and very simple that I create a sample. I will do that asap. In the meanwhile I can spend some time browsing the web on my iPad. Would you know where I could read an easy piece about how type can be converted automatically like they seem to be on my development machine? Thanks, Michel
    Wednesday, July 22, 2015 4:33 PM
  • Hi Michel,

    Thanks for the detail information for this issue.

    I am trying to reproduce this issue in Excel 2013 however failed.  What's version of Excel on the users's computer?

    Can you try the code on the users computer below to test wheter the Excel would convert the Range to string automatclly?

    debug.Print Range("A1")

    In addition, to make the code always work in the different enviroment, I suggest that you call the method as it declared.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, July 23, 2015 2:55 AM
    Moderator
  • Hello Fei,

    I have prepared a tiny VS Solution that illustrates my problem.
    You can find all the files here.
    This solution contains two nano projects:

    - the first defines a COM-Visible class named  netcom2VBA
    - the second is a Visual Studio Installer setup project

    In addition, there is also an Excel file with a small VBA macro.
    The purpose of the macro is to test the "public double myLen(string str)"  function implemented by the netcom2VBA class.

    I made three tests.

    1) It worked on the development machine after compilation. 
    2) It worked on another development machine after using the setup. This machine is an old PC where I did the same kind of work before. VS is installed, and I developped COM-visible project, vsto project and other stuff.
    3) It didn't work on a user machine where I had first used the setup program. Only Office is installed on this machine.

    I can confirm you that by ensuring the same argument type in VBA and in the C# class, the test will work correctly.
    However, I cannot use this as a solution.
    My project is a conversion from an older J++ project and there are many Excel files in the wild using this library.
    I need to get the new C# library working with all the old Excel files, without any change. Full compatibility.
    This should be possible, as this works perfectly on development machines.

    In addition, I am very curious to understand why currently this code doesn't run properly on user's machines while it work perfectly on development machines. Where is the difference?

    Thanks for you attention,

    Michel

    Saturday, August 8, 2015 9:16 PM
  • Hi Michel,

    Thanks for sharing the demo project with us. The solution works well for me, I tested in Office 2013. What's version of Excel installed on the machine which have this issue?

    In addition, can you debug the VBA code to see whether the 'str' for the test method is the correct value?

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, August 10, 2015 7:27 AM
    Moderator
  • Hello Fei,

    Thanks for your patience!

    I am using both Excel 2010 and Excel 2013.
    Did you test it after compiling yourself in Visual Studio?
    Is Visual Studio installed on your test machine?

    When I install this tiny package on a user machine it doesn't work.
    By user machine I mean that only Windows 7 and Office are installed.
    To make this work on a user machine, I need to modify the VBA for using the exact type for the argument.

    I tested that on several different user machines.
    However all these machine used the standard configuration in my company.
    (the development machine is also a standard configuration where I added Visual Studio)

    Debugging in VBA did not show any problem, except for the fact that the C# method is not called on a user's machine when the argument is a Variant/Range.

    This works on any machine:

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


    While this works only on the development machine:

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

    As I explained, I am not able to modify all the Excel workbooks that are using the previous (J++) version of this package.
    I need to offer a fully compatible replacement.

    Thanks again,

    Michel


    • Edited by Lalbatros Monday, August 10, 2015 8:57 AM
    Monday, August 10, 2015 8:55 AM
  • Hi Michel,

    >>Did you test it after compiling yourself in Visual Studio?
    Is Visual Studio installed on your test machine?<<

    Yes, the Visual Studio is installed on the test machine. However I didn't compile the project. I only install with setup.exe.

    >>By user machine I mean that only Windows 7 and Office are installed.<<

    Did you mean that there are Excel 2010 and Excel 2013 installed on the users's machine which have this issue?

    >>Debugging in VBA did not show any problem, except for the fact that the C# method is not called on a user's machine when the argument is a Variant/Range.<<

    What did you mean that C# methoed is not called? Did the code 'obj.myLen(str)' get any error message?

    I suggest that you put a breakpoint at the line of code below:

    Set obj = New netcom2vba.netcom2vba
    Then type the formula '=test(B3)' into a cell, please check the value of 'str' via hovering the mouse on the parameter like fingure below:

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, August 11, 2015 6:29 AM
    Moderator
  • Hello Fei,

    Thanks again for your perseverance.

    Concerning the Excel version.
    I have installed Excel 2013 only recently on my development PC.
    Before that I used Excel 2010 on all machines.
    I think that we can forget about the Excel version, which probably plays no role.
    (the problem has not changed since I installed Excel 2013)

    What do I mean by the method is not called?
    I know that it is not called because of tests I made earlier with the full COM library.
    I had put tracing statements in the code, logging to a text file.
    After a while it was clear that the code was simply not called, on the user machine, if the argument did not match the exact type.

    I modified now slightly the VBA test we are discussing:

    Function test(str)
        On Error Resume Next
        Set obj = New netcom2vba.netcom2vba
        test = obj.myLen(str)
        Debug.Print Err.Number
        Debug.Print Err.Description
        Debug.Print Err.HelpContext
        Debug.Print Err.HelpFile
        Debug.Print Err.Source
    End Function

    In this way I can observe the Err variable after the  obj.myLen(str)  statement is called.
    Here is what is printed in the Immediate window:

     430 
    Class does not support Automation or does not support expected interface
     1000430 
    C:\PROGRA~2\COMMON~1\MICROS~1\VBA\VBA7\1033\VbLR6.chm
    VBAProject

    The description suggests why the method was not called.
    I have no idea why it works on the development machine and not on the user machine.
    I would not be surprised that something would be missing in the installation process.
    (may some kind of OLE-related registering magic?)

    Yesterday, I imagined it could be an "interop" stuff missing on users machines.
    But I checked that the Excel Interop assembly are present in the GAC of the users machines.
    Therefore, the only track seems to be the error message above.
    Why this message on users machine and nothing on my development machine?

    Thanks again,

    Michel


    • Edited by Lalbatros Tuesday, August 11, 2015 10:37 AM
    Tuesday, August 11, 2015 7:37 AM
  • Hi Michel,

    >>I would not be surprised that something would be missing in the installation process.
    (may some kind of OLE-related registering magic?)<<

    Since you motioned that the function works well when you pass a string parameter. The issue shouldn't be relative to install.

    I am also trying to test it in Excel 2010 on 64-bit OS, however it failed (can't create ActiveX object). If I understood correctly, the issue only occur on one machine and I suggest that you check the difference between the machine which works and doesn't work like .Net framework and Excel detail version.

    I am also creating an environment for Excel 2010 on 32-bit OS, I would be back if I have any update.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, August 12, 2015 7:29 AM
    Moderator
  • Hello Fei,

    Thanks for your observations.
    Indeed it seems related to the target machine.
    Below is a table showing the tests I have done so far.
    The  "userx" and  "developperx"  machines are professional computers provided by my employer.
    The  "homex"  machines are private computers.
    It is clear that there is something wrong with the professional machines.
    The  developper1  machine is exactly the same as any  "userx" machine except for the admin rights and the installation of  Visual Studio.

    It seems therefore that there is "something special" with the professional machines and that this "something special" disappears if  Visual Studio  is installed.
    It would be extremy helpful if you could suggest me where to look for differences that could explain this behaviour.

    Thanks and best regards,

    Michel

     
    machinee windows rights office visual
    studio
    tiny
    class
    large
    class
    user1 Win 7 pro restricted 2010 no fail
    user2 Win 7 pro restricted 2010 no fail
    user3 Win 7 pro restricted 2010 no fail
    user4 Win 7 pro admin 2010 no fail fail
    developper1 Win 7 pro admin 2010 installed ok ok
    home1 Win 7 home admin 2007 no ok ok
    home2 Win 8 admin 2013 no ok ok
    home2 Win 8 admin 2010 no ok ok
    home3 Win 8 admin 2013 installed ok ok

    • Edited by Lalbatros Wednesday, August 12, 2015 2:05 PM
    Wednesday, August 12, 2015 2:05 PM
  • Hi Michel,

    I am trying to reproduce this issue on a clean test machine, however failed. Here is my test environment:
    Excel 2010 Version:14.0.6023.1000(32-bit) and Windows 7 Enterprise Sevice Pack 1 without Visual Studio installed.

    I am not understand the table exactly. What does 'large class' mean? And from the table above, it seems the difference is the rights of login in user. Can you reproduce this issue with admin privilege?

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, August 13, 2015 5:55 AM
    Moderator
  • Hello Fei,

    The  'large class'  is the package where I found the problem initially.
    Later, I created the tiny example that I shared with you.
    This tiny example is the 'tiny class' in the table.
    Essentially, I did not see any difference between the small example and my full package.

    What do you mean that "reproducing the issue" failed on a clean test machine?
    Do you mean that the Excel sheet worked correctly, showing no issue on your clean machine?

    In the table I indicated "win pro" for professional computers, but I actually meant Windows 7 Enterprise.
    Users's right is probably not the cause of the problem: you can see in the table that on the machine "user4" I received admin rights, but the problem is still there.

    It looks more as if the problem occurs only on the PC's from my employer ... as long as VS is not installed.
    These PC's are all based on the same initial image.
    I have no idea about what could be different with these PC's.

    I will try to spend some time listing the content of the GAC on different PC's as well as the admin Policies.
    Do you think that would be a track worth pursuing?
    Would you have any other directions?

    Thanks a lot for you efforts,

    Michel



    • Edited by Lalbatros Thursday, August 13, 2015 6:57 AM
    Thursday, August 13, 2015 6:48 AM
  • Hi Michel,

    >>What do you mean that "reproducing the issue" failed on a clean test machine?
    Do you mean that the Excel sheet worked correctly, showing no issue on your clean machine?<<

    Yes, the Excel sheet worked correctly. I suggest that you try to uninstall the dll and Office and .Net framework then reinstall the .Net framework, Office and dll to see whether it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, August 13, 2015 7:27 AM
    Moderator
  • Hello Fei,

    I un-installed the dll and the Framework 4.5.2  (only this one appears in the uninstall screen).
    Then I re-installed the Framework and the dll.
    The problem remains as before.

    I didn't re-install Office as this is not easy in my company. (bureaucracy involved)
    Would you have some suggestions for tests that I could perform before I really go for re-installing Office?

    Why do you think a re-install of Office might unfreeze the problem?

    Thanks again,

    Michel

    Note1: Excel version are: {developper1: 14.0.7153.5000, user4: 14.0.7143.5000}.
    Note2: The user4 machine was freshly installed a few weeks ago from a standard image for the single purpose of solving this problem. I did nothing else. Corruption could have come only from the process of installing my COM-visible package.


    • Edited by Lalbatros Thursday, August 13, 2015 10:10 AM
    Thursday, August 13, 2015 10:06 AM
  • Hi Michel,

    >>Why do you think a re-install of Office might unfreeze the problem?<<

    In fact, I am not sure whether re-install Office could fix this issue. All the factors I could see that may affect this issue are custom solution, Excel and .NET framework.

    >>Note2: The user4 machine was freshly installed a few weeks ago from a standard image for the single purpose of solving this problem. I did nothing else. Corruption could have come only from the process of installing my COM-visible package.<<

    Did you check the registry was added successfully? Or we can pass the string instead of Range to make sure the install was successfully.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, August 14, 2015 7:45 AM
    Moderator
  • Hello Fei,

    Indeed, the test works on user4  when passing  As String  instead of  as a Variant/Range .
    I also checked several time the registry, even for the "full project", and everything was exactly the same in all cases.

    I attempted some distraction to stimulate new ideas.
    I tried to create a setup program for some VSTO projects.
    I have never deployed these before since I was always the single user.
    I had a lot of difficulties with installers as well as with Click-Once, but finally I got that working.

    My next (somewhat hopeless) test will be a naive attempt to include a COM-visible  class within a VSTO project and to try to publish it with Click-Once. I am almost sure that it will not solve the problem. I am not even sure it will inspire me in any way. However, this might be useful later once the COM-visible problem is solved.

    Regarding the possibility to re-install Office.
    I give it a  9/10  likelihood  of solving the problem. (I came last year to the same conclusion)
    However, I could hardly ask my colleagues (or the help-desk) to re-install Office before using my library.
    Therefore, even then, I would be interested to pin-point more precisely the origin of the problem and to try a simple fix.

    This is an old problem, not yet urgent, but I need to solve it.
    Any suggestion is still welcome!
    Thanks a lot already for your help.

    Michel


    • Edited by Lalbatros Friday, August 14, 2015 8:14 AM
    Friday, August 14, 2015 8:13 AM
  • Hello Fei,

    I re-installed Excel on a standard user machine and it didn't solve the problem.

    I find it incredible that any such automation class would work on any PC but not on any PC from my company.
    (unless the PC has been somehow "fixed" by installing VS)
    What could be the reason?

    Any suggestion still welcome, really any,

    Michel

    Tuesday, September 1, 2015 8:13 AM
  • Problem is related to the regional settings.
    Some regional settings do not work.
    Probably installing the ad-hoc language pack would solve the problem.
    Problem is: some regional settings are not associated with a language pack (like  French (Belgium)).
    Question remains: Why should the functioning of a COM-visible Assembly  depend on the Regional Settings and on how Excel reacts to these settings?
    Thursday, September 24, 2015 6:35 AM
  • Problem is related to the regional settings.
    Some regional settings do not work.
    Probably installing the ad-hoc language pack would solve the problem.
    Problem is: some regional settings are not associated with a language pack (like  French (Belgium)).
    Question remains: Why should the functioning of a COM-visible Assembly  depend on the Regional Settings and on how Excel reacts to these settings?
    Thursday, September 24, 2015 6:35 AM
  • Hi Lalbatros,

    >> Why should the functioning of a COM-visible Assembly  depend on the Regional Settings and on how Excel reacts to these settings?

    It seems your issue is similar with the thread below, right? If it is, I suggest you try the last reply of mine.
    Reference: https://social.msdn.microsoft.com/Forums/office/en-US/59c2c9ad-1b5c-4eba-a1ba-8de3d91945a3/class-does-not-support-automation-in-some-regional-settings-but-not-in-others-how-can-that?forum=exceldev

    If you have any issue about this, please feel free to let us know.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by Lalbatros Friday, October 9, 2015 6:59 AM
    Thursday, October 8, 2015 6:02 AM
  • Thanks Edward.

    Your answer in the other thread solves the issue.

    Michel

    Friday, October 9, 2015 6:59 AM