Error 424 (object required) when using Application.Caller.Offset
Hello,
I have written the following code to calculate the difference between two dates, which returns a Error 424 - Object required.
Option Explicit
Public Function DateDifference() As Long
Worksheets("Sheet1").Activate
Application.Volatile True
Dim Shift As integer
Shift=1
DateDifference=Datediff("d", Application.Caller.Offset(0,Shift), Application.Caller.Offset(0,Shift+1))
End Function
There are easier ways to do this, but I do need to reference the two cells with the dates in relative to the cell the function is called from. I also need to shift the two date cells along the row, hence the "shift" variable.
Even though it does calculate the difference, it breaks out at the DateDifference line and seems to have an issue with Application.Caller.Offset section. I can get it to work fully by giving it two dates explicitly.
Can someone please help me with the error.
Thanks, CJS.
|
