RSTOCKEWLL
asked on
Excel VBA decimal separation
Dear Experts,
I am a relative novice in Excel VBA and would therefore appreciate some assistance.
I have some excel VBA that is effectively creating from an excel sheet a structured XML file for import into an ERP system.
The XML standards imparted by the vendor mean that it will only accept a (.) decimal separator and not a (,) comma decimal separator.
This becomes a problem when my users in Germany populate the Excel sheet as their locale is set to use (,) comma decimal separator.
I therefore wondered if it was possible via the Excel VBA to do a find and replace on these values before creating the XML, if the decimal separator in the Excel sheet was set to comma (,)??....
Any thoughts would be greatly appreciated as always.
I am a relative novice in Excel VBA and would therefore appreciate some assistance.
I have some excel VBA that is effectively creating from an excel sheet a structured XML file for import into an ERP system.
The XML standards imparted by the vendor mean that it will only accept a (.) decimal separator and not a (,) comma decimal separator.
This becomes a problem when my users in Germany populate the Excel sheet as their locale is set to use (,) comma decimal separator.
I therefore wondered if it was possible via the Excel VBA to do a find and replace on these values before creating the XML, if the decimal separator in the Excel sheet was set to comma (,)??....
Any thoughts would be greatly appreciated as always.
Correction: Str(3/2) always displays 1.5, not 1,5
ASKER
I am not sure if the above is going to help, say I have a value of 600,56 entered in an excel sheet whereby the (,) is the deciaml separator, the Val() function will return this as 600 as it will ignore anything after the comma??
What I would like is for the 600,56 to return 600.56.....
Hope this makes sense....
What I would like is for the 600,56 to return 600.56.....
Hope this makes sense....
No, it does work, but it's Str you need not Val - it was probably my original post that confused you. You would use Val("23.54") to return a value from your xml. You can test this by switiching you regional and language settings to German in Control Panel and restarting Excel.
Check this one, you need to click on Run Button in the excel sheet,if not working your requirement please your excel sheet then i can code for you :)
AnswerDecimal.xls
AnswerDecimal.xls
Hi, do your German users use the . as a thousands separator? I seem to remember from my time working there that a number was formatted 123.000,12.
I think there's a good simple solution for this and I'll let you know when/if I think of it!
I think there's a good simple solution for this and I'll let you know when/if I think of it!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I seem to be advocating Str() very strongly now considering I originally said it was not ideal, but it also has the benefit that you do not ned to worry about the thousands separator if you are just looking at numeric values from an Excel worksheet - the thousands separator is only part of the numeric formatting for the cell and does not appear in the Cell.Value (as long as Cell.Value is a number and not a string representation of one).
ASKER
Just the ticket, the Str function works perfectly.
Thanks
Rick
Thanks
Rick
Hi RStockewll
If I understand your issue correctly, then adding the following function in VBA seems to work, probably if your PC is set to use US/UK number format but the spreadsheet value is in German format:
Then in the spreadsheet, simply call the function, for example to convert a number in cell C2:
I hope this helps.
If I understand your issue correctly, then adding the following function in VBA seems to work, probably if your PC is set to use US/UK number format but the spreadsheet value is in German format:
Function FormatUK(dNumber As Double) As String
FormatUK = Format(dNumber, "currency")
End Function
Then in the spreadsheet, simply call the function, for example to convert a number in cell C2:
=FormatUK(C2)
I hope this helps.
This is not ideal, but I've had to make use of it before.