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

Question

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.

Answer 1

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