PDA

View Full Version : Date Variable Showing 12:00:00 AM, Why?



V.B.A.02
07-28-2017, 03:41 AM
Hello Everyone,

I have just come across below strange behavior of excel VBA and just trying to understand why is this happening, Please see below code:



Sub DateVarTest()
Dim Dt1, Dt2, Dt3 As Date


Dt1 = "": Dt2 = "": Dt3 = ""


Dt1 = Date
Dt2 = Date + 1
Dt3 = Date + 2


'Further Codes


End Sub




As soon as I start running code step by step using F8 key, I see by hovering mouse on variables Dt1, Dt2 and Dt3 that Dt1 and Dt2 are empty but Dt3 has value as "12:00:00 AM".

Strange thing is that if I add one more variable Dt4 and then what I observe is that Dt1, Dt2 and Dt3 are now shown empty but Dt4 (which is last variable declared as date) shows value as "12:00:00 AM".

** If I had just one variable it would have value as "12:00:00 AM". but with multiple variables as date, only last variable has value as "12:00:00 AM" others are just empty.

Can anyone help me understand this strange behavior of VBA?

Thanks for Help.!

mana
07-28-2017, 03:51 AM
Dim Dt1, Dt2, Dt3 As Date


means


Dim Dt1 as Variant, Dt2 as Variant, Dt3 as Date

Paul_Hossler
07-28-2017, 05:39 AM
Since Dt1 and Dt2 are Variants and Dt3 is a Date, assigning an empty String to a Variant gives an empty string which has a numeric value = 0

In addition, I get a Type Mismatch error on Dt3=""

This returns today, tomorrow, and the day after for Dt1, Dt2, and Dt3 but 12:00 for Dt4




Option Explicit
Sub DateVarTest_ph()
Dim Dt1 As Date, Dt2 As Date, Dt3 As Date
Dim Dt4 As Date

Dt1 = Date
Dt2 = Date + 1
Dt3 = Date + 2

MsgBox Dt1
MsgBox Dt2
MsgBox Dt3

Dt4 = 0
MsgBox Dt4

End Sub




All has to do with the way Excel stores and uses Dates

SamT
07-28-2017, 05:51 AM
DT3, in fact all Date types are, is actually a special case of the Double type
Dt3 = 0 will not return an error

12:00:00 AM = zero hours: zero minutes: zero seconds after midnight
0 = Date(01/00/1900 12:00:00 AM) = 12/31/1899
1 = Date(01/01/1900 12:00:00 AM)

V.B.A.02
07-31-2017, 05:51 AM
Thanks Everyone,