Excel - Date variables and empty cells?

Asked By salgud on 29-Jul-08 01:16 PM
I'm working on a program that works with a lot of dates. I found that if I
set a date variable equal to a cell that might contain a date or might be
empty, if the cell is empty, it returns "12:00:00 AM" which I assume to be
midnight, Jan. 1, 1900, which is more difficult to test for than a blank
cell. So it seems to me that it would be better to use a variant type
variable here instead of a date variable since I have to test for blank
cells. The variant returns "empty" for blank cells, and dates for cells
with dates in them. Am I on the right track, or headed off on a spur?

If I do use the variants, how do I test for "Empty"? Is it just "If rRange
= "empty", or do I need to use something else? Will testing for "" work for
this? Basically, I have several tests where if the cell is blank, do
something, if it has a date in it, do something else.

Any thoughts any of you experts has on working with dates and blank cells
will be greatly appreciated.

Rick Rothstein \(MVP - VB\) replied on 29-Jul-08 01:44 PM
The problem is not the empty cell, that is returning the empty string; the
problem is you are putting it into a variable declared as being of type
Date. However, this is not hard to test for at all...

If YourDateVariable = 0 Then  'Assume there is no date in the cell

or, you could test the cell directly...

If Range("A1").Value = "" Then  'There is nothing in the cell

ward376 replied on 30-Jul-08 09:49 PM
test for 0

Cliff Edwards