Monday, April 16, 2012 11:07 AM
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.
- Edited by smartncute Monday, April 16, 2012 11:13 AM
Tuesday, April 17, 2012 8:25 AMModerator
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:
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.
Bruce Song [MSFT]
MSDN Community Support | Feedback to us
- Edited by Bruce SongModerator Tuesday, April 17, 2012 8:28 AM
Tuesday, April 17, 2012 10:02 AM
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
elseif dec_sep="." then
dec_sep is the string value which represents the decimal seperator which is in the regional settings.
Again thanks for your reply.
Tuesday, April 17, 2012 12:08 PM
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?
- Edited by Laurent Couartou Tuesday, April 17, 2012 12:08 PM