Wrong date-formating while using data fields?
 

Hello,

I have the following problem:
If a call the following makro:

Public Sub Test()
Dim arrTable As Variant
Dim ws As Worksheet

Set ws = ActiveSheet

arrTable = ws.Range(ws.Cells(1, 1), ws.Cells(2, 2))
ws.Range(ws.Cells(1, 1), ws.Cells(2, 2)) = arrTable
End Sub

and in Cell(1,1) is a date in European Format (24.12.2004)
the data field arrTable writes the date in American format (12/24/2004)
back.

This does not happen, if I don't use date field, but normal variables.
On my computer I habe Excel 2000 and Excel XP. It only happens with
Excel XP, but
works right for Excel 2000.
The Country code on my computer is "German".

Can anyone help me?


Thanks,

Hermann

Wrong date-formating while using data fields?
 

Hi Sascha,

and in Cell(1,1) is a date in European Format (24.12.2004)
the data field arrTable writes the date in American format (12/24/2004)
back.

arrTable = ws.Range(ws.Cells(1, 1), ws.Cells(2, 2))
ws.Range(ws.Cells(1, 1), ws.Cells(2, 2)) = arrTable

In both of these, you're using the (default) .Value of the range, which
includes data types based on the cell formatting. Try using .Value2, which
doesn't use the formatting to identify data types:

arrTable = ws.Range(ws.Cells(1, 1), ws.Cells(2, 2)).Value2
ws.Range(ws.Cells(1, 1), ws.Cells(2, 2)).Value2 = arrTable


Regards

Stephen Bullen
Microsoft MVP - Excel

Professional Excel Development
The most advanced Excel VBA book available
www.oaltd.co.uk/ProExcelDev

Wrong date-formating while using data fields?
 

Thank you Stephen,

your tip was good. I use it now in the following way:
arrTable = ws.Range(ws.Cells(1, 1), ws.Cells(2, 2)).Value
ws.Range(ws.Cells(1, 1), ws.Cells(2, 2)).Value2 = arrTable

because I need, the information, if a cell is a date or a number.
But it only works, if I don't want change the format of a cell (e.g.
from date to number).

I really do not unterstand, why my code with only ".Value" does not work
under my Excel XP. It works with Excel 2000.

Thanks,

Sascha



Stephen Bullen wrote:
Hi Sascha,


and in Cell(1,1) is a date in European Format (24.12.2004)
the data field arrTable writes the date in American format (12/24/2004)
back.


arrTable = ws.Range(ws.Cells(1, 1), ws.Cells(2, 2))
ws.Range(ws.Cells(1, 1), ws.Cells(2, 2)) = arrTable

In both of these, you're using the (default) .Value of the range, which
includes data types based on the cell formatting. Try using .Value2, which
doesn't use the formatting to identify data types:

arrTable = ws.Range(ws.Cells(1, 1), ws.Cells(2, 2)).Value2
ws.Range(ws.Cells(1, 1), ws.Cells(2, 2)).Value2 = arrTable


Regards

Stephen Bullen
Microsoft MVP - Excel

Professional Excel Development
The most advanced Excel VBA book available
www.oaltd.co.uk/ProExcelDev

Wrong date-formating while using data fields?
 

Hi Sascha,

I really do not unterstand, why my code with only ".Value" does not work
under my Excel XP. It works with Excel 2000.

No idea. This was a nasty bug in earlier versions of Excel (i.e.
international handling of dates, times, formulae etc when entered using
arrays), that I thought was fixed in Excel 2000. Maybe it's made a
comeback.

Regards

Stephen Bullen
Microsoft MVP - Excel

Professional Excel Development
The most advanced Excel VBA book available
www.oaltd.co.uk/ProExcelDev