Dear all,
In this piece of Excel VBA code:
Sub a()
Dim Datim As Date
Dim Outr As Range
Set Outr = Range("b2")
Datim = "17/03/2006 12:00"
Do
Outr.Value = Datim
retry:
If Abs(Datim - Outr) > 0.0000001 Then
Debug.Assert False
Outr = Datim + 1
GoTo retry
End If
Datim = Datim + CDate("00:05:00")
Set Outr = Outr.Offset(1)
Loop Until Datim > "1 apr 2006"
End Sub
the code marked red shows that if you store the VBA date "18/03/2006 00:00" in a spreadsheet cell the contents of this cell becomes "17/03/2006 00:00" and when it is read it back into VBA then you get the value "17/03/2006 00:00"
However, if you store "18/03/2006 00:00" +1 in the spreadsheet cell and read it back then you get "18/03/2006 00:00".
This behaviour only occurs when the VBA date is "integer", i.e. when the hour and minute part of the date is "00:00". Otherwise dates transfer as expected.
Are there any explanations for that (or is it only my Excel2003 which behaves like that )
Regards
Morten
What is the difference between Excel Dates and VBA Dates ?
nbrege
Hi PeterMo
Thanks for your effort: nice to know that it I havnt completely lost my mind.
But it surely puzzels me that it can behave like that. Basically I thought thay the "date" data type, both in VBA and in Excel, was basically a "double" (internally and with respect to arithmetic) which by default is formatted as a date when presented to the "user".
Morten
Julian V
Hi Morten
If it's any comfort I get the same issue with Excel 2003 on my PC. Interestingly( ) if you replace the line after the Do with
Outr.Value = Cdate(Cstr(Datim))
then it works as you would have hoped.
PeterMo.