Date as variable vs in excel worksheet

[don]

I have a variable vdate defined as
vdate = "01/01/2007"

When I compare it to a date on a worksheet of the same date, ie,
1/1/2007, Excel says they aren't equal.
If I check both using ISdate() it shows True.

If I change the vdate = 1/1/2007 (no quote marks) it considers it a
division calculation.

If I assign vdate to a cell, ie.
cells(1,1)=vdate

and then compare cells(1,1) to the dates on the spreadsheet it finds
them to be equal.

How do I compare the vdate to the dates on the worksheet without going
thru the process of saving it to a cell.

Thanks
Don

 

[Vergel Adriano]

One way is to assign vdate as

vdate = #1/1/2007#

 

[JMB]

Isdate means "1/1/07" can be converted to a date, not that it actually is a
date.

Check VBA help for CDate function to convert it, or define your variable as
a date (date literals are enclosed w/#).

Const vdate As Date = #1/1/2007#

 

[NickHK]

Don,
Others have pointed out ways of dealing with this, but now you see the
benefit of setting the correct data type for your variable and not relying
on Variants, which can hold any type.
If you had:
Dim vdate as Date
depending on what/how you were doing, you would either get an error upon
assignment or correct comparison.

Also note that dates are basically Doubles. Hence you can:
Dim vdate As Date

vdate = #1/1/2007#
'Or
'vdate = "1/1/2007"

MsgBox vdate + 1

But this will error with a Type Mismatch
Dim vdate 'As Variant

vdate = "1/1/2007"
'vdate now holds a string
'This will error
MsgBox vdate + 1

How VBA deals with days v. months in a date like 1/1/2007 is another matter.
I always try to be as explicit as possible. DateSerial is useful for this.

NickHK