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!


+-------------------------------------------------------------------+
|Filename: dates.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4031 |
+-------------------------------------------------------------------+

 

[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]

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

 

[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/Ch22/ProgRefCh22.htm
International Issues

 

[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).