Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Setting .Value to a date does not respect local system setting
I have found that using a macro to set a cell value to a date does not
generate the expected result. Consider ... Sub mcrSetDate() ActiveCell.Value = "1/2/2003" ActiveCell.NumberFormat = "dd mmm yyyy" End Sub This results in "02 Jan 2003". However if I enter "1/2/2003" into a cell directly and format it I get "01 Feb 2003". Clearly VBA is not observing the system date/time format which is set to Australian. (ie. dd/mm/yy) but it is clearly parsing the .Value to a date serial. Changing the statement to the following corrects the problem... ActiveCell.Value = DateValue("1/2/2003") However this means because my macro is dealing with data of unknown type (from a text file) I have to inspect every value to decide if it is a date so I can cast it before assigning it to the .Value. Does anyone know of a cleaner way to get around this problem. The sheet involved is always new and has never had any formatting aplied by the time the data is being placed on it. Cheers, Frank. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Setting .Value to a date does not respect local system setting
vba uses USamerican internally in the VBE see what happens if you type: d1=#1-2-2003# will convert to #2/1/2003# using a range's value2 property to insert dates will solve your uncertainty.. as it will insert the value as a number rather than an (interpreted) date (see vba help) Sub DoDate() Dim d1 As Date, d2 As Date 'Jan 1st d1 = #1/2/2003# 'Feb 2nd d2 = DateValue("1-2-2003") With ActiveCell .Cells(1).Value2 = d1 .Cells(2).Value2 = d2 .Resize(2).NumberFormat = "dd mmm yyyy" End With keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Frank_Hamersley" wrote: I have found that using a macro to set a cell value to a date does not generate the expected result. Consider ... Sub mcrSetDate() ActiveCell.Value = "1/2/2003" ActiveCell.NumberFormat = "dd mmm yyyy" End Sub This results in "02 Jan 2003". However if I enter "1/2/2003" into a cell directly and format it I get "01 Feb 2003". Clearly VBA is not observing the system date/time format which is set to Australian. (ie. dd/mm/yy) but it is clearly parsing the .Value to a date serial. Changing the statement to the following corrects the problem... ActiveCell.Value = DateValue("1/2/2003") However this means because my macro is dealing with data of unknown type (from a text file) I have to inspect every value to decide if it is a date so I can cast it before assigning it to the .Value. Does anyone know of a cleaner way to get around this problem. The sheet involved is always new and has never had any formatting aplied by the time the data is being placed on it. Cheers, Frank. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Setting .Value to a date does not respect local system setting
"keepITcool" wrote in message
vba uses USamerican internally Par for the course I guess! using a range's value2 property to insert dates will solve your uncertainty.. Didn't seem to change the outcome at all. Note that I am casting from a String rather than Date. ActiveCell.Value2 = "1/2/2003" produces 02-Jan-2003 ActiveCell.Value2 = #1/2/2003# produces 37623 which formats as 02-Jan-2003 as it will insert the value as a number rather than an (interpreted) date Looks like I will have to keep parsing the string and casting it myself! Thanks for the input. Frank. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Setting .Value to a date does not respect local system setting
"William" wrote
Try using "DateSerial" [ DateSerial(Year, Month, Day) ] Thanks - but I have no problem if I cast the value myself - only if I leave it to Excel. Cheers, Frank. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Setting .Value to a date does not respect local system setting
Frank
you draw some quick conclusions. not willing to learn or study.?? <vbg ofcourse inserting #1/2/2003# produces Jan2nd. #xxxxx# denotes VBA date.. which as i mentioned is ALWAYS US format) but inserting DateValue("01-02-03") will insert Feb 01 because datevalue will translate string to a date against locale just as format will produces a string from date or use dateserial.. just and please read help on date conversions... a small demo.. Sub tst() 'hardcoded date MsgBox "JAN 2nd: " & Format(#1/2/2003#, "dd-mmm-yyyy") MsgBox "use datevalue to get a LOCALE date from string" & _ vbNewLine & Format(DateValue("01/02/03"), "dd-mmm-yyyy") 'note the output is unformatted... MsgBox "or use dateserial with year,month,day integers" & _ vbNewLine & DateSerial(2003, 2, 1) 'lets combine it and format the output MsgBox "FEB 1st: " & Format(DateValue("01-02-03"), "dd-mmm-yyyy") & _ vbNewLine & FormatDateTime(DateSerial(2003, 2, 1), vbLongDate) End Sub once you've a valid date (can be a double or a date data type) THEN use value2 to assign to excel cell. keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Frank_Hamersley" wrote: "keepITcool" wrote in message vba uses USamerican internally Par for the course I guess! using a range's value2 property to insert dates will solve your uncertainty.. Didn't seem to change the outcome at all. Note that I am casting from a String rather than Date. ActiveCell.Value2 = "1/2/2003" produces 02-Jan-2003 ActiveCell.Value2 = #1/2/2003# produces 37623 which formats as 02-Jan-2003 as it will insert the value as a number rather than an (interpreted) date Looks like I will have to keep parsing the string and casting it myself! Thanks for the input. Frank. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Setting .Value to a date does not respect local system setting
see my other post.. you DO have a problem with casting the date!
keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Frank_Hamersley" wrote: "William" wrote Try using "DateSerial" [ DateSerial(Year, Month, Day) ] Thanks - but I have no problem if I cast the value myself - only if I leave it to Excel. Cheers, Frank. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Setting .Value to a date does not respect local system setting
"keepITcool" wrote
ofcourse inserting #1/2/2003# produces Jan2nd. #xxxxx# denotes VBA date.. which as i mentioned is ALWAYS US format) Why "of course" and "ALWAYS" - simply signifies slack or simpleton programming to me! but inserting DateValue("01-02-03") will insert Feb 01 because datevalue will translate string to a date against locale As you will have determined, my opinion is that all indiscriminate date casting whether explicit or implicit should consider locale. If as programmer you find a reason to force a non locale cast, then you should code it distinctly such as DateSerial() offers. Sub tst() Thanks for the tute but I have already BTDT - I was only looking for a way to ensure locale processing always occurred. It is no big drama to me that Excel is not up to it. As it happens I already have a proprietary IsDate() function to catch other quirks in Excel like IsDate("5,011,554"). Cheers, Frank. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Setting .Value to a date does not respect local system setting
the only quirk is YOU seem determined to demonstrate that if you cast a LOCALE date string to a cells's value you run the risk of excel interpretation. To avoid this behaviour: cast a date as a DOUBLE or DATE to value or value2 =no interpretation cast a locale STRING to formulaLOCAL it will be interpreted against locale ! Sub DateCasting() Dim d As Date d = DateSerial(2003, 2, 1) ActiveCell.NumberFormat = "general" ActiveCell.FormulaLocal = Format(d, "d/m/yy") MsgBox "entered as DMY string" & vbNewLine & _ "system uses " & IIf(Application.International(xlMDY), "MDY", "DMY") & _ vbNewLine & "result:" & (ActiveCell.Value = d) End Sub keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Frank_Hamersley" wrote: Thanks for the tute but I have already BTDT - I was only looking for a way to ensure locale processing always occurred. It is no big drama to me that Excel is not up to it. As it happens I already have a proprietary IsDate() function to catch other quirks in Excel like IsDate("5,011,554"). Cheers, Frank. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Setting .Value to a date does not respect local system setting
Frank
I do not understand. Your original post implied you encountered problems in returning the "correct" date with the code -----ActiveCell.Value = "1/2/2003"----- Why is it that the code below (or KeepItCool's solution) does not resolve your problem? -----ActiveCell.Value = DateSerial(2003, 2, 1)----- XL2002 Regards William "keepITcool" wrote in message ... | see my other post.. you DO have a problem with casting the date! | | keepITcool | | < email : keepitcool chello nl (with @ and .) | < homepage: http://members.chello.nl/keepitcool | | | "Frank_Hamersley" wrote: | | "William" wrote | | Try using "DateSerial" [ DateSerial(Year, Month, Day) ] | | Thanks - but I have no problem if I cast the value myself - only if I | leave it to Excel. | | Cheers, Frank. | | | | |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Setting .Value to a date does not respect local system setting
"William" wrote
I do not understand. Your original post implied you encountered problems in returning the "correct" date with the code -----ActiveCell.Value = "1/2/2003"----- Why is it that the code below (or KeepItCool's solution) does not resolve your problem? -----ActiveCell.Value = DateSerial(2003, 2, 1)----- A timely question! In my original post I explained that the "string" at hand was not known to be a correctly formatted date (locale or otherwise). This all came to my attention because I had made some VBA code changes to reduce the number of CPU cycles consumed in preparing the results sheets to the user (neatly layed out and formatted). In the old code I inspected every string, determined the most suitable "type" and established the cell formatting before assigning the string to the .Value property. In the case of a date, because the .NumberFormat had been set in advance to what is effectively my locale setting, the casting was performed correctly and all was well. However profiling showed this approach was very slow so I mod'ed the code to defer the application of cell formatting until after all the strings had been assigned to their respective cells, and then performed the required formatting on the .EntireColumn. This saved heaps of CPU time but exposed "the issue" when it ignored the locale. Anyway as I mentioned before, I am resigned to doing it the hard way because Excel sure ain't going to changes its spots - I just had a dig at the original Excel coders because IMO this sort of trait is in the same genus as the myriad problems cropping up in IE now (and to varying extent in other MS products in the past). Cheers, Frank. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Setting .Value to a date does not respect local system setting
"keepITcool" wrote
the only quirk is YOU seem determined to demonstrate that if you cast a LOCALE date string to a cells's value you run the risk of excel interpretation. Reread the post and then explain why IsDate("5,011,554") returns TRUE - that is the question (apols to the Bard). Frank. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Setting .Value to a date does not respect local system setting
the isdate function's description from vba help.. Returns a Boolean value indicating whether an expression can be converted to a date. the crux is in the word CAN.. I do admit that vba or excel's interpretation is somewhat loose. plus there are some differences between VBA and Excel functions. I ran a few loops (swapping system separators) and determined that vba's string interpretation is based on ALL of the following user's locale settings... listseparator,decimalseparator,dateseparator,hours eparator (optionally including the am pm indicators..) funny as you'd expect it to be based on either locale's or usenglish Date separator. you may be interested in : http://support.microsoft.com/default...;EN-US;Q214233 also see harlan grove's article "an interesting read" in mp.excel.worksheetfunctions: . com keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Frank_Hamersley" wrote: "keepITcool" wrote the only quirk is YOU seem determined to demonstrate that if you cast a LOCALE date string to a cells's value you run the risk of excel interpretation. Reread the post and then explain why IsDate("5,011,554") returns TRUE - that is the question (apols to the Bard). Frank. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Setting .Value to a date does not respect local system setting
"keepITcool" wrote
the isdate function's description from vba help.. Returns a Boolean value indicating whether an expression can be converted to a date. the crux is in the word CAN.. ....excepting in this case it can't be displayed (visually) as a date - although it does not cause an exception so I guess that meets the CAN criteria! plus there are some differences between VBA and Excel functions. Understood - however it was the VBA giving me the heartburn. I ran a few loops (swapping system separators) and determined that vba's string interpretation is based on ALL of the following user's locale settings... listseparator,decimalseparator,dateseparator,hours eparator (optionally including the am pm indicators..) Yuk! funny as you'd expect it to be based on either locale's or usenglish Date separator. I agree. It looks like an each way bet being placed - I presume somehow in response to something like (as I understand them) the European formats for dates like yyyy.mm.dd and decimal numbers like 999.999.999,99 ie. the same char being used in different contexts. The Q214233 article mentions in respect of dates a.. If a number contains a slash mark (/) or hyphen (-), it may be converted to a date format. It isn't nearly as comprehensive as your research shows it needs to be in respect of commas etc! also see harlan grove's article "an interesting read" It was very interesting! As it happens I have a B.Sc majoring in Zoology and the risk this trait poses for genetic analysis is not lost on me! I'll bet the smart alec's that though they would help us all out by casting "likely" dates etc didn't see this one coming! Do you know if there is any way to disable this "feature" without having to go to the lengths of preformatting the cell? Cheers Frank. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
setting default filter setting | Charts and Charting in Excel | |||
Setting default pivot table field setting to "sum" | Excel Discussion (Misc queries) | |||
Sending email via macro- setting the importance setting. | Excel Programming | |||
Help with setting up an Ordering system, please | Excel Programming | |||
Setting the system clock with excel | Excel Programming |