none
Currency converts from $ to £ when user locale is set to English (UK) RRS feed

  • Question

  • I would like the currency symbol to always show up as $ irrespective of the current user locale (the value in question is always in $s). However if locale is changed to English(UK) the $ becomes a £. It becomes Euro if locale is changed to German(Germany) and so on.

    Here is the relevant code chunk that uses Excel Automation to specify the format and then insert a value.

        OLECHAR* szNumberFormat = (OLECHAR*)L"NumberFormat";
        pXLRange->GetIDsOfNames(IID_NULL, &szNumberFormat, 1, GetUserDefaultLCID(), &dispid); 


        VariantClear(&CallArgs[0]);
        CallArgs[0].vt = VT_BSTR;
        CallArgs[0].bstrVal = SysAllocString(L"\"$\"#,##0.00;(\"$\"#,##0.00)");  
        DispParams.rgvarg = CallArgs;
        DispParams.rgdispidNamedArgs = &dispidNamed;
        DispParams.cArgs = 1;  
        DispParams.cNamedArgs = 1;
        LCID lcid = 1033; // English(US)
        hr = pXLRange->Invoke(dispid, IID_NULL, lcid, DISPATCH_PROPERTYPUT, &DispParams, NULL, NULL, NULL);

        OLECHAR* szValue = (OLECHAR*)L"Value2";
        pXLRange->GetIDsOfNames(IID_NULL, &szValue, 1, GetUserDefaultLCID(), &dispid);

        VariantClear(&CallArgs[0]);
        CallArgs[0].vt = VT_R8;
        CallArgs[0].dblVal = 795.123;  
        DispParams.rgvarg = CallArgs;
        DispParams.rgdispidNamedArgs = &dispidNamed;
        DispParams.cArgs = 1;
        DispParams.cNamedArgs = 1;
        hr = pXLRange->Invoke(dispid, IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_PROPERTYPUT, &DispParams, NULL, NULL, NULL);

    Note that locale is US English i.e. 1033 for the NumberFormat invocation since the format string is always going to be in US English which I believe is the right thing to do.

    If I change the format string to be 

    CallArgs[0].bstrVal = SysAllocString(L"[$$]#,##0.00;([$$]#,##0.00)");

    then currency always shows up as $ irrespective of the locale set.

    Q1:Is this the right way to fix the issue? I was not able to find any help on this or an msdn arcticle.

    Secondly specifying this format still causes a period i.e. '.' to show up as a comman i.e. ',' when say locale is set to German.

    Q2: Is that expected behavior?

    Q3: Other currencies are not impacted this way. Is there documentation that points out the special nature of '$'?

    Thanks.

    -azeem.

    Thursday, February 12, 2015 6:13 PM

All replies

  • Hi azeem,

    >> Currency converts from $ to £ when user locale is set to English (UK)

    I think the currency symbol changing is expected since the locale was changed, and the SysAllocString function could be a work around to achieve your goal (always show up as $ irrespective of the current user locale).

    # Creating Office Solutions for Use in Multiple Countries/Regions

    Regards,

    Jeffrey


    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, February 13, 2015 11:36 AM
    Moderator
  • Hi Jeffrey,

    Thanks for your response. I cannot find documentation about "[$$]". I found this link which talks about number formatting but it doesn't say anything about "[$$]"

    https://support.office.microsoft.com/en-us/article/Create-or-delete-a-custom-number-format-2d450d95-2630-43b8-bf06-ccee7cbe6864?CorrelationId=9fe1926d-457e-4587-bc47-5c59325306d6&ui=en-US&rs=en-US&ad=US

    This stackoverflow posting provided the clue

    http://stackoverflow.com/questions/14443263/format-a-cell-as-arbitrary-currency-regardless-of-locale-using-vba

    I was hoping to find something in msdn that confirmed this. In any case thanks!

    -azeem.

    Friday, February 13, 2015 6:07 PM

  • If anyone has seen documentation about "[$$]" could you point it out? Thanks

    -azeem.


    Friday, February 13, 2015 11:25 PM