Solved

Excel VBA decimal separation

Posted on 2011-09-08
11
1,093 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:RSTOCKEWLL
  • 5
  • 2
  • 2
  • +1
11 Comments
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36501888
You could go down the replace route, but another possibility is to use the crude functions in VBA that are not locale-aware, so Str() and Val() always work on . for decimal wherever you are.  For example Val(3/2) always displays 1.5, not 1,5 even when I have my language settings set the German.  Similarly, Val(3.2) always interprets the . as a decimal, which can be useful when you're reading from your xml file.

This is not ideal, but I've had to make use of it before.
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36501919
Correction: Str(3/2) always displays 1.5, not 1,5
0
 

Author Comment

by:RSTOCKEWLL
ID: 36501991
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....
0
 
LVL 10

Expert Comment

by:ukerandi
ID: 36502134
check this attachment

AnswerDecimal.xls
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36502194
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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 10

Expert Comment

by:ukerandi
ID: 36502268
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
0
 
LVL 10

Expert Comment

by:plummet
ID: 36502339
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!
0
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 500 total points
ID: 36502377
ukerandi - the strength of using Str() is that you can use it in any locale, even those that use a period as the decimal separator - you would not need to check.  I don't know if any locale has a separator that is neither . nor , but Str should work regardless
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36502415
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).
0
 

Author Closing Comment

by:RSTOCKEWLL
ID: 36502435
Just the ticket, the Str function works perfectly.

Thanks

Rick
0
 
LVL 10

Expert Comment

by:plummet
ID: 36502451
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:

Function FormatUK(dNumber As Double) As String

    FormatUK = Format(dNumber, "currency")

End Function

Open in new window


Then in the spreadsheet, simply call the function, for example to convert a number in cell C2:

=FormatUK(C2)

Open in new window


I hope this helps.

0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now