Has the way excel evaluates the Date type changed in Excel 2007

I have some fairly old code that runs just fine in Excel versions before 2007. In 2007, it throws a runtime error: run-time error 16 : expression is too complex. The expression is pretty simple and the error seems unrelated to the actual problem.

I'm trying to compare 2 dates.

Example code follows:

Function getContractEnd() As Date
    getContractEnd = Range("ContractEndDate").Value
End Function

Sub Foo()
    Dim currentDate As Date
    Do
      'stuff
    Loop Until currentDate > getContractEnd    'run-time error 16
End Sub

Excel 2007 works fine when the condition is changed to:

Loop Until DateValue(currentDate) > DateValue(getContractEnd)

Why does Excel 2007 essentially force me to cast these parameters? Both should evaluate to Date data types?

The only possible explanation I can dream up is that something has changed in the way the return value from the getContractEnd function is evaluated in Excel 2007, but I can't find any documentation to support that.

From stackoverflow

• Hi Dave,

  I was able to reproduce your problem in Excel 2003 with VBA 6.3 - very funny!

  This seems to be a problem specific to the Loop construct which cannot handle the conditional statement involving two "Date" types.

  As soon as at least one of the terms becomes a Variant/Date it starts to work. I proved this by removing either the "As Date" in the Sub Foo() ...Dim statemant or in Function getContractEnd().

  Funnily enough a debug.print VarType(...) will always return 7 no matter if Date or Variant/Date

  The same error occurs when rewriting to Loop While

  Funnily enough (2) I was not able to reproduct this behaviour in an If-Statement. Even a Do While and Do Until works fine.

  Hope this helps

  Good luck MikeD

  DaveParillo : Works for me. I'd love to see some documentation explaining why this behavior exists, but I was able to change the behavior in Excel 2007 by messing about with the return type and changing to a while loop. Still can't break this on my Excel 2003, but I'll accept that it's more about the Loop and less about moveing from 2003 to 2007. Thanks.