Number formatting with VBA - Problem when used in different Country.

[Joe]

I need an urgent help.

I am working in India. My customer is in Germany.
In India DOT (.) is used as decimal seperator. eg 3.589
In Germany COMMA (,) is used as decimal seperator. eg 3,589

in my code I used the following line for formatting.

' ********
With Selection.NumberFormat = "0.00"
' ********

Its working fine in India. But its not working in Germany.


I tried to change MY EXCEL settings to the German way. I went to
OPTIONS and modified the decimal seperator as COMMA (,). But with the
same code, that is working.

Now I am confused, how to handle this.

Hope any of you has any clue on this. Pls help.

Thank you.

Regards / Joe

 

[Joe]

I need an urgent help.

I am working in India.  My customer is in Germany.
In India DOT (.) is used as decimal seperator.  eg 3.589
In Germany COMMA (,) is used as decimal seperator.  eg 3,589

in my code I used the following line for formatting.

' ********
With Selection.NumberFormat = "0.00"
' ********

Its working fine in India.  But its not working in Germany.

I tried to change MY EXCEL settings to the German way.  I went to
OPTIONS and modified the decimal seperator as COMMA (,).  But with the
same code, that is working.

Now I am confused, how to handle this.

Hope any of you has any clue on this.  Pls help.

Thank you.

Regards / Joe

I still have the basic problem unsolved.
But I have figured a way around in this case!!

Before I paste the new value, I round it to two digits, rather than
going for formatting option.
This way its working fine and the excel automatically switches between
the two settings without problem.

 

[Ron Rosenfeld]

I need an urgent help.

I am working in India. My customer is in Germany.
In India DOT (.) is used as decimal seperator. eg 3.589
In Germany COMMA (,) is used as decimal seperator. eg 3,589

in my code I used the following line for formatting.

' ********
With Selection.NumberFormat = "0.00"
' ********

Its working fine in India. But its not working in Germany.


I tried to change MY EXCEL settings to the German way. I went to
OPTIONS and modified the decimal seperator as COMMA (,). But with the
same code, that is working.

Now I am confused, how to handle this.

Hope any of you has any clue on this. Pls help.

Thank you.

Regards / Joe

I don't have much experience with this but you should be able to use the
International property to determine the separator of the country settings of
the computer on which your workbook is open. And then use those separators. I
think you may also have to use numberformatlocal.

This seems to work, but there may be more efficient methods:

========================
Option Explicit
Sub SetFormat()
Dim c As Range
Dim sDecSep As String
Dim sThousandsSep As String
Dim sFmt As String

Set c = Range("A1")
sDecSep = Application.International(xlDecimalSeparator)
sThousandsSep = Application.International(xlThousandsSeparator)
sFmt = "#" & sThousandsSep & "###" & sDecSep & "00"

c.NumberFormatLocal = sFmt
Debug.Print c.NumberFormatLocal

End Sub
===============================
--ron

 

[Joe]

I don't have much experience with this but you should be able to use the
International property to determine the separator of the country settings of
the computer on which your workbook is open.  And then use those separators.  I
think you may also have to use numberformatlocal.

This seems to work, but there may be more efficient methods:

========================
Option Explicit
Sub SetFormat()
Dim c As Range
Dim sDecSep As String
Dim sThousandsSep As String
Dim sFmt As String

Set c = Range("A1")
sDecSep = Application.International(xlDecimalSeparator)
sThousandsSep = Application.International(xlThousandsSeparator)
sFmt = "#" & sThousandsSep & "###" & sDecSep & "00"

c.NumberFormatLocal = sFmt
Debug.Print c.NumberFormatLocal

End Sub
===============================
--ron- Hide quoted text -

- Show quoted text -

thanks Ron...
But I have some doubt on that..

As I said, I changed my settings of Decimal Seperator from DOT to
COMMA.
but even then the code (.NumberFormat = "0.00") was working in my
system.

I got your logic.. But if that was the case I should have put
(.NumberFormat = "0,00") for it work after I changed the setting.
That was not the case.

Let me check this anyway...

thnaks
Joe