data formats in Excel VBA

[johnnymac]

I always seem to have trouble with type-casting in VBA. I have a column of dates I want to access but I get errors (Type mismatch) no matter how I declare my variables. The data has a date and time column. Since I am not using time in this macro, I had declared the variable as Dim dateAcq As Long and dateAcq = Cells(i,j). The data in i,j is formatted as a date and should be a whole number.

I get Type mismatch errors with Integer, Long, Double, String, …
and even when I use an integer function dateAcq = Int(Cells(i,j)).

The macro seems to run well if these functions are undeclared Variants, but I am curious why I am getting errors.

[AB33]

It can't be an integer as it has only 32678 bits. Why can't you declare it as a date? Regardless of the value has time, it should be declared as a date. The error might be somewhere else. Could you attach the sample?

[johnnymac]

Thanks. I can't attach anything at the moment because I am running a very long macro. When I compare two dates I want to make sure it is only looking at the integer portion. Usually these are entered as dates or copied from other spreadsheets, and should be clean date formats without any fractional part, but I have no guarantee of that.

Since a typical file will have thousands of lines - mostly machine generated - I am never quite sure. The dates are attached by users in the field.

[AB33]

If you want to omit the time bit and only want to compare the date, use CLng— which converts the decimal in to long

Please Login or Register  to view this content.