none
"Class does not support automation ... " in some Regional Settings, but not in others. How can that be? RRS feed

  • Question

  • Hello,

    Below you can find two snippets to illustrate my issue:

    - a C# COM-Visible class that exposes a very simple function to be used in Excel
    - two VBA functions using the above C# class to be tested on an Excel spreadsheet

    On some machines, this test worked perfectly.
    On other machines it failed and returned this error message:

    Class does not support Automation or does not support expected interface

    Let me first note how misleading this message is.
    If a Class is a COM server on one machine, why not on all machines?
    I had a hard time to understand what happens, see others threads .

    Finally the cause of this issue was simple: Regional Settings !
    The small test worked perfectly on the  "English (United States)" regional settings.
    The test failed in other regional settings like for example: "French(Belgium)" or "German(Germany)" or "Italian(Italy)" ...
    However, the C# class  and  the VBA functions are not dependent on any regional settings.
    How could that be?

    Changing the regional settings on user's machines is not an acceptable workaround.
    (other program may need other regional settings!!)

    What else could I do?
    Would that be a COM-related bug?
    Would some additional setup be needed for Windows 7 or Office 2010?
    Any idea or suggestions highly welcome!

    With my thanks and best regards,

    Michel


    //C# COM-Visible Assembly to be used in Excel
    using System;
    using System.Runtime.InteropServices;
    namespace againOneV01
    {
        public class againOneV01Class
        {
            public double MJAgain(String str)
            {
                return str.Length;
            }
        }
    }
    ' Excel-VBA test functions to be used in Excel
    ' param would be a Cell of type Excel.Range Function early(param) ' early binding On Error Resume Next Set obj = New againOneV01.againOneV01Class yyy = obj.MJAgain(param) If (Err.Number <> 0) Then yyy = Err.Description early = yyy End Function Function late(param) ' late binding On Error Resume Next Set obj = CreateObject("againOneV01.againOneV01Class") yyy = obj.MJAgain(param) If (Err.Number <> 0) Then yyy = Err.Description late = yyy End Function










    • Edited by Lalbatros Saturday, September 19, 2015 7:19 AM
    Friday, September 18, 2015 9:21 PM

Answers

  • Hi Lalbatros,

    I found a solution to overcome this issue. For this regional issue, when your regional was French, the thread culture of againOneV01Class  in com is French, and we need to set it with en-us, but we could not set it in the MJAgain, if we set it MJAgain, this method would be called right, so the culture would not set correctly. We could set it in the constructor of this class.

    Here is a simple code.

    namespace ComVisible
    {
        public class againOneV01Class
        {
            public againOneV01Class()
            {
                System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-us");
            }
            public double MJAgain(String str)
            {
                Debug.Print(System.Threading.Thread.CurrentThread.CurrentCulture.Name);
                return str.Length;
            }
        }
    }

    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:51 AM
    Thursday, October 8, 2015 5:57 AM

All replies

  • Hi Lalbatos,

    >> The small test worked perfectly on the  "English (United States)" regional settings.The test failed in other regional settings like for example: "French(Belgium)" or "German(Germany)" or "Italian(Italy)"

    With failed test, if you change the regional settings to English, will it work correctly? What you changed, Formats(Region), location(Region) or any other place? I made a test under Format Region is French(Belgium), but I failed to reproduce your issue.

    Based on the error message, how did you distribute your class? If you manually copied the DLL to a computer, I suggest you create a setup program for your application and deploy it to distribute all the dependent dlls.

    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.


    Monday, September 21, 2015 6:37 AM
  • Hello Edward,

    Thanks for testing!

    Indeed, when a test fails, it will work correctly just by setting the region to "English (United States)".
    The distribution is based on a Visual Studio Installer Setup Project.
    I simply added to the "Application Folder" the "Primary Output" of the Assembly project.
    The tlb is automatically added.

    Regarding the dependent dlls, in a sense they must be on any system.
    My guess is that the dependent dlls are very basic system dlls, like ole32.dll .
    But, it is true, that this might be an issue.
    Before I discovered the effect of the regional setting, I investigated in other directions, using the Process Monitor tool for example.
    On machines where the test failed, I observed that Excel failed to access some files:

    api-ms-win-core-quirks-l1-1-0.dll
    mscoree.dll
    combase.dll
    ole32.dll
    mscorrc.dll.DLL
    OLEAUT32.dll
    XLLEX.DLL

    Most of these files where available, but Excel did not look at the right place.
    These failures to access these files is not necessarily linked to the issue, since Excel would typically explore several path to access registry keys and files.
    But it could be related to the issue
    I observed that Excel searched for XLLEX in a locale-dependent folder.
    That's why and when I changed the Region, just by curiosity!

    I must also indicate that I tested all these things on 4 machines.
    On one of them I did not observe the regional issue, it always worked as far as I tested enough possibilities!
    This machine is based on a French Windows 7 and Office. Changing regional settings had no effect.

    Thanks,

    Michel

    Monday, September 21, 2015 8:35 AM
  • Hi Lalbatros,

    It seems an issue about running English version of Excel and locale for the current user is configured for a language other than English. I am not sure whether it is the issue, but I suggest you try the method below:

    1.Install the Multilingual User Interface Pack for your version of Office

    2.As you found, Excel searched for xllex in a lcale-dependent folder. With first method, it will install a file called xllex.dll. For another way, I suggest you create a 1033 directory under Microsoft Office\Office11, copy excel.exe to the 1033 directory, and rename it to be xllex.dll.

    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.


    Tuesday, September 22, 2015 6:17 AM
  • Thanks Edward,

    There was already an  \Office14\1033  folder, and it contains a small xllex.dll file. (no other Officexx folder)
    That's probably because we use and English version of Excel (as well as Windows) .
    However, our Regional Settings are for "French (Belgium)".

    I made a full copy of the 1033  to a new  2060 folder, but it doesn't work.

    It could be that the xllex.dll  is only a part of the problem.
    With the "French (Belgium)" regional setting, Excel failed to find the ole32.dll it in the folder Microsoft.NET\Framework\v4.0.30319 .
    With the "English (US)" regional setting this does not happen in the ProcMon log file.

    It looks as if the regional setting changes even the way a COM-dll is loaded in Excel.

    I should try installing the "Language Interface Pack" as it looks like it is related to Regional Settings.
    I guess this is what I can find here on the shop ?
    From the web page, it is not clear that it would put Excel in better moods!

    I thank you Edward,

    Michel

    Tuesday, September 22, 2015 8:20 AM
  • Hi Lalbatros,

    Did it work if you install Language Interface Pack?

    If it did not work, I suggest you try the methods below:
    1. Use Debug.Print early(ActiveCell.Value) instead of Debug.Print early(ActiveCell)
    2. Or you could try

     public double MJAgain(Object str)
             {
                 return str.Length;
             }

    Hope it will help.

    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.


    Friday, September 25, 2015 7:31 AM
  • If a Class is a COM server on one machine, why not on all machines?

    A COM+ Server must be installed with regsvr32.exe under normal circumstances.

    I had such a case in the past, but I can't remember the details. In the end the solution was to reinstall that package, cause the COM+ registration data in the registry was not complete.

    Then: Your code expects a string, but you're trying to hand over a variant. Also your implicit variables are variant, which can be a problem with COM.

    So check whether your library works with explicit strings on the bad machine:

    Option Explicit
    
    Function Early(AParam As String) As Double
    
        On Error Resume Next
      
        Dim AgainOneV1 As againOneV01.againOneV01Class
    	Dim Result As Double
    	
        Set AgainOneV1 = New againOneV01.againOneV01Class
        Result = AgainOneV1.MJAgain(AParam)
    	Set AgainOneV1 = Nothing
        If (Err.Number <> 0) Then 
    	  Result = -1
    	  MsgBox Err.Description
    	End If
    	
        Early = Result
    	
    End Function
    
    Function Late(AParam As String) As Double
        
    	On Error Resume Next
      
        Dim AgainOneV1 As Object
    	Dim Result As Double
        
    	Set AgainOneV1 = CreateObject("againOneV01.againOneV01Class")
        Result = AgainOneV1.MJAgain(AParam)
    	Set AgainOneV1 = Nothing
        If (Err.Number <> 0) Then 
    	  Result = -1
    	  MsgBox Err.Description
    	End If
    
        Late = Result
    	
    End Function

    btw, use always Option Explicit.

    Friday, September 25, 2015 8:29 AM
  • Hi Lalbatros,

    Have your issue been resolved? If you have, it would be appreciated if you could share us your solution. If not, 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.


    Monday, September 28, 2015 6:46 AM
  • Hello Edward,

    No, it has not yet been really resolved.
    It seems that installing the ad-hoc "language pack" is a necessary condition for a COM-Visible class to work in Excel-VBA.
    The ad-hoc "language pack" is determined from the Regional Settings in Windows.
    The funny thing is, there is no "language pack" for some Regional Settings.
    For example:  "French(Belgium)" or "Dutch(Belgium)".
    Requiring end-users to change their Regional Settings is not an acceptable solution.
    (unless these Regional Settings are definitively useless)

    I find it very strange that such a deep rooted functionality (COM) would be dependent on Regional Settings.
    Even stranger considering that Regional Settings should play absolutely no role in the simple tests I made.

    I doubt that a solution could be easy.
    But who knows? Maybe I am not the only one to have found this problem.
    Some suggestions might still be useful.

    Thanks for your attention,

    Michel

    Thursday, October 1, 2015 6:06 AM
  • Hi Lalbatros,

    Did it work if you install Language Interface Pack?

    If it did not work, I suggest you try the methods below:
    1. Use Debug.Print early(ActiveCell.Value) instead of Debug.Print early(ActiveCell)
    2. Or you could try

     public double MJAgain(Object str)
             {
                 return str.Length;
             }

    Hope it will help.

    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.


    Hi Michel,

    Have you tried these two methods?

    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.


    Thursday, October 1, 2015 9:17 AM
  • Does it also fail, when you use the correct data types in VBA?

    Thursday, October 1, 2015 1:09 PM
  • Hi Lalbatros,

    I found a solution to overcome this issue. For this regional issue, when your regional was French, the thread culture of againOneV01Class  in com is French, and we need to set it with en-us, but we could not set it in the MJAgain, if we set it MJAgain, this method would be called right, so the culture would not set correctly. We could set it in the constructor of this class.

    Here is a simple code.

    namespace ComVisible
    {
        public class againOneV01Class
        {
            public againOneV01Class()
            {
                System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-us");
            }
            public double MJAgain(String str)
            {
                Debug.Print(System.Threading.Thread.CurrentThread.CurrentCulture.Name);
                return str.Length;
            }
        }
    }

    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:51 AM
    Thursday, October 8, 2015 5:57 AM
  • Does it also fail, when you use the correct data types in VBA?

    No it doesn't fail then.
    This indicates that regional settings "are not needed" when no conversion is needed.

    Friday, October 9, 2015 6:51 AM
  • Thanks Edward,

    That's the solution!

    Would you know how to detect which are the possible (available) cultures on a given machine?
    I ask because in case "en-US" would not be available, then the problem would pop up again.
    That could be solved then by choosing an available culture.

    Thanks,

    Michel

    Friday, October 9, 2015 6:55 AM
  • Hi Michel,

    >> I ask because in case "en-US" would not be available, then the problem would pop up again.

    Have you fall down this issue?

    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.


    Monday, October 12, 2015 5:28 AM