none
VBA calling C# addin and setting formula RRS feed

  • Question

  • Hello,

    I'm using german Excel.

    (Imprtant here is that the list seperator of the language is not , like in in en_us [for German it is ;]).

    I further have a shared Addin written in C#.

    This Addin also defines some COM Interface method, one is HandleCTRLQ.

    public void HandleCTRLQ()
    {
    	try
    	{
    		// set CurrentCulture of CurrentHread to en_us
    		// set CurrentUICulture of CurrentHread to en_us
    		ExcelHelper.InternalHandleCTRLQ();
    	}
    	finally
    	{
    		// Reset CurrentCulture
    		// Resset CurrentUICulture
    	}
    }

    And HandleCTRLQ of ExcelHelper looks like

    public static void InternalHandleCTRLQ()
    {
    	// en-us set by caller
    	Excl.Range er = ...
    	er.Clear();
    	er.Formula = "MyCube($B$2,\"Sales\")";
    }
    

    When ExcelHelper.InternalHandleCTRLQ() is called via a Ribbon Button then no exception is thrown.

    Now the C# Addin also Registers a XLL and this Xll Registers a function myfunc.handleq_ctrlq.

    myfunc.handleq_ctrlq internally calls the COM Interface method HandleCTRLQ().

    Wenn now in VBA

    Application.RUN("myfunc.handleq_ctrlq")

    is excecuted an COM Exception is thrown when setting Formula.

    When I use  "MyCube($B$2;\"Sales\")" in this case It works.

    Strnage Thing is that the Debugger shows "Standard" [german local for "General"] for er.NumberFormat instead of "General" although the Cultures of the CurrentThread are en_us.

    It Looks like VBA still internal uses German and expect German in this case.

    So is there a way to Change the internal VBA language to en_us ?

    tia

      Hendrik Schmieder

    Tuesday, September 29, 2015 2:52 PM

All replies

  • Hi,

    1. Which formula is correct if you use it directly through UI?
    2. What’s the result if you use that formula through VBA directly?
    3. What’s the result if you get a COMAddIn object, then call method?

    # Walkthrough: Calling Code in a VSTO Add-in from VBA

    https://msdn.microsoft.com/en-us/library/bb608614.aspx  

    There is an article that may benefit you:

    # Creating Excel Solutions for Use in Multiple Countries/Regions Using Visual Studio Tools for Office

    https://msdn.microsoft.com/en-us/library/aa537159%28v=office.11%29.aspx?f=255&MSPPError=-2147217396

    Regards

    Starain


    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, September 30, 2015 8:48 AM
    Moderator
  • Hello,

    we don't have any VBA code.

    It is that one user wants to use one functionalty of our addin from his/her VBA code.

    Sub Refresh()
       Application.RUN("myfunc.handle_ctrlq")
    End Sub
    

    Before we do anything in our C# Addin with Excel objects we set the Culture to en_us of the current thread,

    so we can use the english version of formula, numberformarts etc.

    But this doesn't work if  VBA calls our C# code and language of Excel is German.

    The Links doesn't help because we donst use VSTO, only

    Microsoft.Office.Excel.Interop and Microsogt.Office.Core.

    Thursday, October 1, 2015 10:48 AM
  • Hi,

    I'm trying to involve some senior engineers into this issue and it will take some time. Your patience will be greatly appreciated.

    Regards

    Starain


    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, October 2, 2015 2:40 AM
    Moderator