Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro behaves differently then when recorded and stuffs dates (or date formats)
I recorded a macro to paste dates from a text file and the macro behaves differently and gives different results then when I recorded it. The macro stuffs up the dates which are pasted or stuffs up the date format and I cannot fix the result. The action is part of a larger macro, but I have attached a simplified example which simulates the problem. I have recorded the macro using macro record. While I record the result is correct. However when I replay the macro the date returned can be incorrect. Note my date format is d/mm/yy. The error only occurs for dates with the day less than 13! (Something to do with the month range 1 to 12???) eg: date copied is 1/11/05 the result is 11/1/2005, however the date 28/10/05 copies correctly. BUT I repeat the error does not occur during live recording - it only occurs when playing the macro. I have tested this on a second PC and it still occurs. I am using English (Australia) for region. I tested English (US) and the error still occurred. I am interested to see if the same problem occurs for other users. You could quickly try this example yourself. I attach 2 files dates.txt and dates.xls Simply copy the dates in dates.txt (to your clipboard), then within an excel file start recording a macro, then simply paste the dates. Stop macro recording. Now select another column and run the macro. Are the results the same? They weren't for me! dates.xls has a macro (macro1) already. It also shows the results I got. All the macro does is -ActiveSheet.Paste- Cheers! +-------------------------------------------------------------------+ |Filename: dates.zip | |Download: http://www.excelforum.com/attachment.php?postid=4031 | +-------------------------------------------------------------------+ -- stephenb ------------------------------------------------------------------------ stephenb's Profile: http://www.excelforum.com/member.php...o&userid=28726 View this thread: http://www.excelforum.com/showthread...hreadid=484187 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro behaves differently then when recorded and stuffs dates (or date formats)
Recorded macros are always problematic: they make assumptions about the
context in which they are run, which might not be valid during playback. They are really only good for repetitive keystroke sequences and for giving you hints about how to *write* the macro. Where date formats are not explicitly defined, Microsoft's standard practice is to assume mm/dd/yy format unless the numbers are inconsistent with that (as with 28/10/05, which *can't* be mm/dd/yy, and is therefore assumed to be dd/mm/yy). The answer is usually to clean up the macro code so it is explicit about what it's doing. "stephenb" wrote in message ... I recorded a macro to paste dates from a text file and the macro behaves differently and gives different results then when I recorded it. The macro stuffs up the dates which are pasted or stuffs up the date format and I cannot fix the result. The action is part of a larger macro, but I have attached a simplified example which simulates the problem. I have recorded the macro using macro record. While I record the result is correct. However when I replay the macro the date returned can be incorrect. Note my date format is d/mm/yy. The error only occurs for dates with the day less than 13! (Something to do with the month range 1 to 12???) eg: date copied is 1/11/05 the result is 11/1/2005, however the date 28/10/05 copies correctly. BUT I repeat the error does not occur during live recording - it only occurs when playing the macro. I have tested this on a second PC and it still occurs. I am using English (Australia) for region. I tested English (US) and the error still occurred. I am interested to see if the same problem occurs for other users. You could quickly try this example yourself. I attach 2 files dates.txt and dates.xls Simply copy the dates in dates.txt (to your clipboard), then within an excel file start recording a macro, then simply paste the dates. Stop macro recording. Now select another column and run the macro. Are the results the same? They weren't for me! dates.xls has a macro (macro1) already. It also shows the results I got. All the macro does is -ActiveSheet.Paste- Cheers! +-------------------------------------------------------------------+ |Filename: dates.zip | |Download: http://www.excelforum.com/attachment.php?postid=4031 | +-------------------------------------------------------------------+ -- stephenb ------------------------------------------------------------------------ stephenb's Profile: http://www.excelforum.com/member.php...o&userid=28726 View this thread: http://www.excelforum.com/showthread...hreadid=484187 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro behaves differently then when recorded and stuffs dates (or date formats)
Jezebel, thanks for a response. While your reply may be true in man cases, I still don't feel it's helped me. If you quickly try what have done as described in my original post I think you will understan what I mean (it's simple and won't take long). I have also tried t remove assumptions by formatting cells before and after the paste an yet the problem still occurs. To me it appears to be an Excel bug unless someone can prove otherwise or perhaps provide a workaround Thanks again -- stephen ----------------------------------------------------------------------- stephenb's Profile: http://www.excelforum.com/member.php...fo&userid=2872 View this thread: http://www.excelforum.com/showthread.php?threadid=48418 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro behaves differently then when recorded and stuffs dates (or date formats)
Dates in Excel VBA are interpreted as US English format if it is at all
possible. You have described this behavior in detail. This isn't a bug, but designed behavior. See Stephen Bullen's site for information on international considerations. http://www.oaltd.co.uk/ExcelProgRef/...rogRefCh22.htm International Issues -- Regards, Tom Ogilvy "stephenb" wrote in message ... Jezebel, thanks for a response. While your reply may be true in many cases, I still don't feel it's helped me. If you quickly try what I have done as described in my original post I think you will understand what I mean (it's simple and won't take long). I have also tried to remove assumptions by formatting cells before and after the paste and yet the problem still occurs. To me it appears to be an Excel bug, unless someone can prove otherwise or perhaps provide a workaround. Thanks again. -- stephenb ------------------------------------------------------------------------ stephenb's Profile: http://www.excelforum.com/member.php...o&userid=28726 View this thread: http://www.excelforum.com/showthread...hreadid=484187 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro behaves differently then when recorded and stuffs dates (or date formats)
Thanks Tom. But why in this technological age would sophisticated software assume English (US) if I have regional settings of English (Australia) and I have formatted the cells as dd/mm/yyyy? Did you try the test I refer to? Even if it is not a bug, surely there must be a workaround. Best Regards Steve -- stephenb ------------------------------------------------------------------------ stephenb's Profile: http://www.excelforum.com/member.php...o&userid=28726 View this thread: http://www.excelforum.com/showthread...hreadid=484187 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro behaves differently then when recorded and stuffs dates (or date formats)
The implications of VBA trying to arbitrate dates by reference to your regional settings are really pretty horrendous: it would add a level of complication that, as a programmer, you really wouldn't want to have to deal with. Eg, what happens when you're talking to an SQL system, where the SQL standard (which is a non-Microsoft issue) specifically precludes using dd/mm/yyyy? You'd end up with VBA formats flipping back and forth according to assumptions about context. The good workaround is to use non-ambiguous date formats in all contexts. ISO 8901 is ideal, because it's language independent and is recognised by pretty well all current software including SQL. Or use dd-mmm-yyyy, which is also non-ambiguous (at least within the English-speaking world). "stephenb" wrote in message ... Thanks Tom. But why in this technological age would sophisticated software assume English (US) if I have regional settings of English (Australia) and I have formatted the cells as dd/mm/yyyy? Did you try the test I refer to? Even if it is not a bug, surely there must be a workaround. Best Regards Steve -- stephenb ------------------------------------------------------------------------ stephenb's Profile: http://www.excelforum.com/member.php...o&userid=28726 View this thread: http://www.excelforum.com/showthread...hreadid=484187 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I convert date/time formats differently? | Excel Discussion (Misc queries) | |||
Formula behaves differently | Excel Worksheet Functions | |||
changing a recorded macro - date problem.... | Excel Programming | |||
RECORDED MACRO PASTE'S DATE DIFFERENTLY TO MANUAL PASTE | Excel Worksheet Functions | |||
In excel the formula bar behaves differently on different machines | Excel Discussion (Misc queries) |