Maybe a little off topic

  • Thread starter Thread starter Nicke
  • Start date Start date
N

Nicke

Does anyone know how "Managed NewsGroup Support" works? I
am a MSDN Universal subscriber and refering to info from
Microsoft I am guaranteed response wihtin 2 business
days... How do they know I am guaranteed that when I post
a question? However here is the question again that I
don't got any answer on, maybe someone can help me now...
Thanks in advance.

I use intersect to get values from an array to a sheet.
Everything works fine in Excel 97,2000 and XP. But in
Excel 2003 it fails when a number contains a comma as
decimal delimiter. Excel 2003 seems to always use period
as a decimal delimiter even if my national settings says
comma.

Example:

Dim Arr As Variant
ReDim Arr(0, 0)
Arr(0, 0) = "123,345"
Intersect(Range("A1"), Range("A1")) = Arr

This will return "123 345" in Excel 2003 and 123,345 in
all other versions.

Is this a bug?

BTW, i've checked "Use system separators" in the options
dialog.

Any tip will be appreciated

/Nicke
 
Hi Nicke,

We track the issue following the email you registered in the MSDN database. In this way, each MSDN subscriber's post will be checked by Microsoft
support professional.

For this issue, I'd suggest you can check the regional setting from control panel (start->Control Panel->Regional and language options ). Is there any
difference of the setting in the box Excel 2003 installed from the one Excel 2000 or 2002 installed?

My regional setting is English(united states) and the output in my Excel 2003 is "123,456" after the execution of your vba code.

Please feel free to let me know if you have any further questions.

Does this answer your question? Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Hi,

Thank you for your answer, here is my response.

Yes I tried to changed to English (USA) and got 123,456,
my (and my clients) settings is Svenska or Swedish.

The problem is that here in Sweden we have a comma (",")
for a decimal separator and space for thousand separator.

When I switched to English I got 123,456 but what I try
to put in the cell is (in your language) 123.456 (less
than 124, hope you understand).

It seems like Intersect always want a period as a comma
(it don't care about the national settings) because if I
change the code to Arr(0,0) = "123.345", and have comma
as a delimiter, it interpret the period as a comma and
put the right value in the cell.

This was discovered when a client had changed to Excel
2003 and got too large values in my program and when I
debugged my program I noticed this behaviour in Excel
2003.

I hope you can confirm this behaviour and possible help
me with a workaround.

Thanks in advance.

/Nicke
-----Ursprungligt meddelande-----
Hi Nicke,

We track the issue following the email you registered in
Click to expand...
the MSDN database. In this way, each MSDN subscriber's
post will be checked by Microsoft
support professional.

For this issue, I'd suggest you can check the regional
Click to expand...
setting from control panel (start->Control Panel-
Regional and language options ). Is there any
difference of the setting in the box Excel 2003
Click to expand...
installed from the one Excel 2000 or 2002 installed?
My regional setting is English(united states) and the
Click to expand...
output in my Excel 2003 is "123,456" after the execution
of your vba code.
 
Hi Nicke,

Thank you for replying!

So far as I know, intersect is working following the regional settings. If you set the language to Swidesh, "123.345" will be displayed in "123" in the
cell(1, 1) with intersect. If you set the regional setting to Chinese(PRC), it is also the same. However, the "123,345" can be displayed very normally
in Chinese(PRC) which is not the same to "123 345" in Swidesh.

From my experience, you can add one '#' before the data "123,345". In this woraround, intersect will not change the "123,345" to "123 345". Please
note you will need to remove the '#' before you use the value.

Please feel free to let me know if you have any further questions.

Does this answer your question? Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Hi, thank you for your answer.

OK, I think I know whats going on here... My array
contain "123,456" but obviously you must have "123.456"
to use intersect. "123,456" returns "123 456" which is
1000 times bigger than "123,456" (in Sweden). "123.456"
returns "123,456".

Why I don't have recognized this before is that "123,45"
returns "123,45" (formated as text though). The result is
wrong when there is more than 2 decimals.

So I guess I have to change my program to replace the
comma with period.

/Nicke
-----Ursprungligt meddelande-----
Hi Nicke,

Thank you for replying!

So far as I know, intersect is working following the
Click to expand...
regional settings. If you set the language to
Swidesh, "123.345" will be displayed in "123" in the
cell(1, 1) with intersect. If you set the regional
Click to expand...
setting to Chinese(PRC), it is also the same. However,
the "123,345" can be displayed very normally
in Chinese(PRC) which is not the same to "123 345" in Swidesh.

From my experience, you can add one '#' before the
Click to expand...
data "123,345". In this woraround, intersect will not
change the "123,345" to "123 345". Please
 
Hi Nicke,
I use intersect to get values from an array to a sheet.
Everything works fine in Excel 97,2000 and XP. But in
Excel 2003 it fails when a number contains a comma as
decimal delimiter. Excel 2003 seems to always use period
as a decimal delimiter even if my national settings says
comma.

Example:

Dim Arr As Variant
ReDim Arr(0, 0)
Arr(0, 0) = "123,345"
Intersect(Range("A1"), Range("A1")) = Arr

This will return "123 345" in Excel 2003 and 123,345 in
all other versions.

Is this a bug?

BTW, i've checked "Use system separators" in the options
dialog.
Click to expand...

The first rule of ensuring VBA works globally is to *never* let Excel
interpret strings for you. Always do it yourself. In this case,
ensure that your array contains Doubles for any numbers you want to put
in the sheet, using CDbl(text) if the text uses the system separators
or Val(text) if it uses US separators.

Dim Arr As Variant
ReDim Arr(0, 0)
Arr(0, 0) = CDbl("123,345") 'Comma is the decimal separator
Intersect(Range("A1"), Range("A1")) = Arr

I'm assuming here that the text isn't hard-coded into the application
<g>. If it is, always hard-code it using US settings.

The underlying problem of how Excel populates a range from an array has
been 'fixed' in various ways for different data types in a number of
versions. However, I get '123 345' in all versions from '97 onwards on
my machine (with Swedish settings).

For a detailed account of the issues when using Excel in non-US
environments, you might like to read my 'International Issues' chapter
of the "Excel 2002 VBA Programmers Reference".

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk
 
Hi Stephen,

My program is a report generator that only display values
in Excel. What I found out was that all worked fine with
2 decimals, i.e. Arr(0,0) = "123,23" was displayed
correctly (almost anyway, I guess Excel got a little
confused and format 123,23 as text). Because I always
right justify the values everything has seemed OK and the
problem has never been discovered until now when 3 digits
after the decimal point occured.

How I managed to get this to work in Excel 97,2000 and XP
is a mystery, I guess I only used two decimals then...

I guess I'll have to change the values in the array to
contain period instead of comma with CDbl as you say.

Thanks

/Nicke
 
Hi Nicke,
I guess I'll have to change the values in the array to
contain period instead of comma with CDbl as you say.
Click to expand...

You're not really changing the values in the array to contain a period
or a comma - what CDbl does is change the value from text strings
(which Excel can misinterpret) to actual numeric values (of type
Double), which Excel can't misinterpret.

Regards

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