Overflow error

[Tatsujin]

Any know how to fix this overflow error? Cell A1 has a date value of "01/01/2200".

Sub errTest()
Dim d1, currDay As Date
Dim r As Integer

currDay = Date
d1 = Sheet1.Range("A1").Value

r = currDay - d1 - 7 ' Error because A1 = 01/01/2200

msgbox r

End Sub

[Peter T]

"Tatsujin" <tatsuj...@gmail.com> wrote in message

Various reasons the result will overflow. d1 is undeclared so when returning
a cell value it will change to a Double or Date (or 'Empty' if the cell is
empty, String or Error). You've got r declared as an Integer but Date is
beyond the bounds of and Integer so any calculation with the d1 and currDate
will likely return as a Date. Try this
Dim r as Integer
r = Date

Change the r declaration to Long or if need to be sure working with dates to
As Date. Also declare d1 As Long or As Date as applicable for your purposes.
In passing, except in limited scenarios, normally declaring As Integer
rather than As Long serves no useful purpose. In doesn't save memory or
improve efficiency in +32bit computing.

Unless sure of what the cell will contain more work might be required to
avoid or handle unexpected values resulting in errors or invalid results.

Peter T