How to change international decimal separator

F

Finn Petersen

Hello

I can see the international setting for decimal separator with:
Application.International(xlDecimalSeparator)

but how do I change the international settings from VBA?


Finn
 
H

Harald Staff

That depends on your version. XP and probably 2003: Record a macro while changing it in
the Tools > Options > International menu for proper syntax. Earlier versions follow the
WindowsControl panel's settings. Far more complex to program -and you shouldn't, it's a
global setting for the computer and/or for the user.
 
S

Stephen Bullen

Hi Finn,
I can see the international setting for decimal separator with:
Application.International(xlDecimalSeparator)

but how do I change the international settings from VBA?
Click to expand...

Just to add to Harald's answer - Why do you think you need to?

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk
 
C

Chris R. Lee

Am I alone in thinking that it's astonishing that Microsoft, in its dominant
position, has done absolutely nothing about establishing
generally-acceptable means of expressing numbers and dates?

Numerous PhDs in international research companies waste hours checking their
dossiers to make sure that data copied from different software sources is
expressed consistently.

Try 123 456,123 : is it one number or 2 (123 and 456 point something or 456
thousand and something) ? For US/UK readers, many countries use the comma as
decimal separator (IOS standard).

Your pack of crisps is best by 04/11/01. Has it gone mouldy? (mm/dd/yy in
US, dd/mm/yy in much of Europe, yy/mm/dd in Scandanavia and much of Asia,
yyyy-mm-dd = full ISO standard).

Regards
 
N

Norman Harker

Hi Chris!

The list of date formats that are unequivocally recognised by all
versions of Excel is:

12 January 2002
12-January-2002
12/January/2002
12 Jan 2002
12-Jan-2002
12/Jan/2002
2002-01-12
2002/01/12
2002/1/12

Methods 3,4 and 5 are the most commonly selected. They are quick to
type and take up the least width of cell.

Method 7 is ISO8601:2000 approved (separated) form and has the even
greater advantage that it does not "demand" an English language date.
I believe it is a form that is recognized by all Regional settings but
I haven't checked that out for all countries and all Excel Versions
(OK for Versions down to Excel 97). For other forms the short or long
month names need translating to the appropriate language. Methods 8
and 9 are non-compliant ISO ordered and I would ask, "Why use these
when the real McCoy is available?"

You might note that all use 4 digit years and thus protect against
change of the double digit year interpretation setting. If you use
double digit years you risk major error if some darn fool changes your
double digit year interpretation setting or if your application is
used on another computer with different settings.

Even without adoption of the potentially confusing ISO method, there
are variations that allow Kwikimart to tell how far past the use by
date their stock is.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
F

Finn Petersen

Hi Stephen
Maybe I am wrong.

I write a date in a textbox in a userform.
Eks: I want to write 1st august 2003. I write in the textbox 01-08-2003(this
is the format I want to use, and which is common used in Denmark). But one
computer understands it as 8. january 2003

Isnt this due to the international date format?

Finn
 
H

Harald Staff

Hi Finn

It's a question of how the date format is set in the control panel. If it's
dd-mm-yy then we assume that this is how the user enters and uses dates. If
it's mm-dd-yy then that's what we assume.

The trick is that a date is a date is a date, how it's entered and shown is
just a question of formatting. No matter how it's entered, we assume that
the control panel and the user agree. For this Datevalue is a great tool, it
handles all common date entries (1.8 , aug 1 03, 1/8-03, almost anything)
and harmonizes it with the control panel settings. Try something like

Private Sub CommandButton1_Click()
Dim D As Date
If IsDate(DateValue(TextBox1.Text)) Then
D = DateValue(TextBox1.Text)
MsgBox "You wrote " & _
Format(D, "dddd mmmm d. yyyy")
Else
TextBox1.Text = ""
MsgBox "No date"
End If
End Sub

and do NOT prompt for something like "enter dates in format dd-mm-yyyy" -let
Datevalue do her job. The error you mention is because the user is on
dd-mm-yy and the control panel setting is mm-dd-yy. Change the computer to
match the user.
 
S

Stephen Bullen

Hi Finn,
I write a date in a textbox in a userform.
Eks: I want to write 1st august 2003. I write in the textbox 01-08-2003(this
is the format I want to use, and which is common used in Denmark). But one
computer understands it as 8. january 2003

Isnt this due to the international date format?
Click to expand...

It's partly due to the international date format, but more to do with the way
your code is handling the text entered. As Harald pointed out, instead of
forcing specific formats on your users, it is better practice to respond to
their choice of format, as set in Control Panel, by explicitly converting
between a date number and the textual representation of it.

So given a date variable dtDateVar and a text box on a form tbDateBox, the
following VBA can be used to convert between them:

'Display a date according to the user's settings
tbDateBox.Text = Format$(dtDateVar, "Short Date")

'Interpret a date according to the user's settings
If IsDate(tbDateBox.Text) Then
dtDateVar = CDate(tbDateBox.Text)
End If


Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk
 
Top