International decimal number formats

[John Cleese via OfficeKB.com]

You will be aware that some international number formats use ',' (commas) to
denote decimal places, whilst others use '.' (points). I have tried to write
a macro that will write a decimal number into a cell, and have tried to
define the number format by making use of lines:

Range("$A$1"),numberFormat = "0.0000"
OR
Range("$A$1").Value = Format(number,"0.0000")

Neither of these lines work.

I need to find a VBA command that will define the position of the decimal
place, regardless of whether the number format used by the user uses a '.'
(point) or a ',' (comma) to define the position of the decimal place.

Does anyone know how I can achieve this?

Regards

J

 

[Dave O]

Hi, John-
The information contained in a cell and the way that information is
diplayed are frequently different, and driven by the cell's format and
Windows Regional and Language Settings.

For instance, if you enter an Excel-recognizable date in a cell (try
your local equivalent of 10 January 2007) then reformat that cell to a
numeric format, the cell will display 39092.

We should consider that as we develop a solution to your question. That
said, I'm honestly not sure what your question is. When you say
"define the position of the decimal place", do you want to determine
significant digits? Or convert between formats?

Dave O

 

[andjbird via OfficeKB.com]

Hi Dave,

I want to be able to convert between formats.

For example, in certain international formats the user might enter "23,39" to
denote the US decimal number "23.39". When I try to format the storage cell
in my application with the VBA code 'Format(number,"0.0000")' Excel stores
the number as a whole number, 23 if I recall correctly. One thing is certain
the number is not stored such that it is recognisable as "23.39" in US or UK
number formats.

Can you help?

Regards J

 

[Dave O]

John-
I typed some decimal numbers and they appeared on-screen with my
default settings: a period as decimal. Then I clicked ~Start ~Control
Panel >Regional and Language Options and clicked the Custom button.
This allowed me to show my decimal places with a comma; the numbers I
had already entered were converted automatically.

So it sounds like no conversion is required at all: if you're
exchanging spreadsheets with places that use a comma to denote the
decimal place, they will likely have their preferences set in Control
Panel. No additional effort would be required.

I also tried as many ideas as I could to perform your desired
conversion, and did in fact generate a way to do it. However, it would
include entering numbers in a particular column and then hiding that
column; somewhere in the workbook you could have a "user preferences"
section where the user enters a P for period and a C for comma. Then
in a helper column right next to the hidden column enter a formula,
perhaps something like this:
=IF(A1="C",INT(C11)&","&TEXT(INT(MOD(C11,1)*100),"00"),C11)
....where A1 is the P or C column, and C11 is a hidden numeric entry
column. The problem with this idea, or should I say "among the handful
of problems" for there are many: this creates a text entry, so you
cannot perform math on it, and it's just generally chunky and ugly and
inelegant.

What do you think about the Regional and Language Options idea? And
just for curiosity, what is your timezone? I am on the East Coast of
the USA, GMT -5

Dave O

 

[John Cleese via OfficeKB.com]

Thanks Dave,

I agree there should be a simple solution to this.

If I describe in a little more detail what I am trying to do it may help.
I'm designing an application based on Excel for use in the company I work for
that has offices worldwide. Part of the function of the application involves
entering decimal numbers in a userform then saving these decimal numbers in
spreadsheet cells, so that they can be retrieving at a later date. The
problem I was having was that when the application stored the decimal numbers
in decimal formats using ",", it wasn't storing and retrieving the numbers
properly.

I tried formatting the cells using Format(number,"0.0000"), which didn't work
when the regional format used a ",". It works fine when the regional format
uses a ".".

I also tried formatting the cells using Format(number,"0,0000") for regional
formats using a "," (i.e. by checking Application.International
(xlDecimalSeparator), which also didn't work.

I'll try out your suggestions and I'll try a few additional options that I've
thought of such as checking what is recorded if I were format a cell in a
regional format that uses a "," by recording the code when I format the cell
manually.

I find regional options a pain to deal with.
My timezone is GMT. I'm based in the U.K.

Regards J

 

[Dave O]

Good morning, J-
My apologies for not responding sooner: I access newsgroups using
Google groups, and they must have changed the way they store or present
data: I couldn't locate this post (despite a fairly arduous search).
But now apparently they've changed back to the earlier format, so I was
able to find it.

Here's an idea that may work: some additional code that prompts the
user to enter his preferred decimal format, and stores that in a Named
Range in the workbook. Then allow the user to enter numbers as usual,
but provide some data validation within the code that checks to see if
he has entered a period or comma, and then makes the entry using the
preferred decimal format- irrespective of whether he has entered a
comma or a period in the input box.

This method may require you to change the data type of the previously
declared variable that handles the input, but seems workable.

Could that work for you?
Dave O