Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maybe a little off topic
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maybe a little off topic
to take advantage of MSDN Managed newgroup support, you need to
register your email address so that it is automatically flagged to MS. See: http://msdn.microsoft.com/newsgroups...welcomepage.as p (watch linewrap - the URL ends in "p"). In article , "Nicke" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maybe a little off topic
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maybe a little off topic
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 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. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maybe a little off topic
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maybe a little off topic
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 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. . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maybe a little off topic
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. 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maybe a little off topic
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 -----Ursprungligt meddelande----- 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. 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 . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maybe a little off topic
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. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Link to Help topic? | Excel Discussion (Misc queries) | |||
How is a a help topic example selected? | Excel Discussion (Misc queries) | |||
Topic did not show up? | Excel Worksheet Functions | |||
example in the help topic | New Users to Excel | |||
Off Topic Messages | Excel Worksheet Functions |