Displaying numbers - German format

Question

Hi,

I have a excel macro file which has some number values. I have formatted the numbers as "0.00" in US format. While formatting I'm reading the Windows regional settings and according to the regional settings the values are displayed. When I sent to this my customer in germany the results were displayed  as below.

E.g. Value(in US format)            German Format displayed

a) 423.36                                             423,36

b) 4.00                                                  004

c) 32.00                                                 032  

The items b and c seems are displayed as multiples of hundred. Is there any permanent or workaround solution for this? Appreciate your help.

P.S. I'm using windows 7 and Excel 2010 and my customer is also having Excel 2010.

Thanks,

smartncute

Answer 1

Hi Smartncute,

Welcome to the MSDN forum.

I've tried to reproduce the problem you described, however I cannot produce the same scenario as yours on my side. And my enviroment is the same as yours: windows 7 and Excel 2010 .

My steps are as follows:
1)Make sure the Control Panel ->Region and Language ->Formats item is chosen as English(United States).
2)Open an Excel application, type"423.36", "4.00", "32.00" in Cell A1, A2, A3. Make sure the
  three values are in type of number.
3)Write a Marco to assign value "423.36", "4.00", "32.00" to cell B1, B2, B3.These three numbers
  are generated in "0.00" NumberFormat.
4)Open Control Panel ->Region and Language. Choose the "Formats" item as German(Germany).

After these 4 actions been taken, the numbers are automatically changed into "423,36", "4,00", "32,00"
in both A and B column.

So, according to my description, you'll find that the result will show no difference whatever the data were input by hand or via Macro. If the problem persists on your side, it will be nice if you can show us the detailed reproducible steps list and your entire Macro.

Besides, your problem is more like to be an Excel  end user issue, if so,  I think it is suitable to repost the problem on the answer forum:
 
http://answers.microsoft.com/en-us/office/forum/excel
 
The experts there are more familiar with the usage of Excel and may give you more suggestions or workarounds.
Thank you for your understanding. Hope your problem can be resolved there. Wish you a nice day.

Best Regards,

---

Bruce Song [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Hi Bruce,

Thanks so much for the reply. Sorry that I am not very precise about my requirement. I am using the macros as these are calculated values. To get the windows regional settings I am using some API's and macros which gives us all the details of the settings. So that when I open the same file it shows all the values in US format and when my customer in germany opens he will get all the values displayed in german format. I used the same code in Excel 2003 which worked fine. But in Excel 2010 it shows me in a wrong manner. I can give you the sample code here which i used for formatting.

dim dec_sep as string,c as range

if dec_sep="," then

       c.numberformatlocal="0,00"

elseif dec_sep="." then

        c.numberformatlocal="0.00"

dec_sep is the string value which represents the decimal seperator which is in the regional settings.

Again thanks for your reply.

Answer 3

When you use formats like "0.00" in your Excel Workbook (English-US locales) and send this workbook to your German customers. They will, see your numbers in their locales when they open the document.

Why use VBA to do what Excel normally does by itself? Any reason for that in the first place? Anything I missed?

---

The Data Specialist (Blog)