Macro behaves differently then when recorded and stuffs dates (or date formats)

[stephenb]

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!

[Jezebel]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[stephenb]

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.

[Tom Ogilvy]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[stephenb]

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

[Jezebel]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>