Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Apr 2011
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts

    “Expression too complex (Error 16)” in Excel 2007.Excel 2003 and 2010 is OK

    I have this small code that works just fine in Excel 2003 and 2010. But not in Excel 2007.

    Select Case Val(cbBIL.Value)
    Case Is < Range("InputValues!Shield1")
    styleDT = "A"
    Case Is < Range("InputValues!Shield2")
    styleDT = "B"
    Case Else
    styleDT = "C"
    End Select

    When the code reaches the second line (the bold one), I get the error Expression too complex (Error 16)
    If I use a variable to store the expression Val(cbBIL.Value), then everything works just fine. Also, if I use a variable for Range("InputValues!Shield1"), then the error is on the second "Case Is".
    I'm afraid that there is a change in 2007 that I'm not aware of and it does something different elsewhere.

    If you have any idea why it is happened, please let me know.
    Thank you.

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Florin

    I am not sure what is going on, I am leaning towards a situation where VBA needs to calculate some expression before VBA uses it, but VBA can't calculate this expression due to dependencies or other conditions.

    But when you say: <<< If I use a variable to store the expression... >>> why don't you want to use a variable? It makes the code easier to figure out in the future and tidy things up. If there is a good reason why not to use a variable for these expressions please tell us, because my advice will be to do so.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. The Following User Says Thank You to Wassim For This Useful Post:

    Florin (2011-05-23)

  4. #3
    New Lounger
    Join Date
    Apr 2011
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Wassim,

    Thank you for the replay.
    Finally, I gave up and I create a variable:

    Dim tempString As String
    tempString = Range("InputValues!Shield1")

    Now it works, but my problem is why do I have to have this useless line of code?
    It cannot be a standard VBA problem because it Excel 2003 doesn't have a problem with it. It must be an Excel 2007 problem because changing from Excel 2003 to 2007 was the only change I made.

    There are no expressions that need to be calculated before others. The calculations are equivalent to:

    Val(cbBIL.Value) < Range("InputValues!Shield1")

    This operation is done w/o any problem.

    I'm worried that the problem might occur on another computer in another similar point in the program.

  5. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    What do you get for this:

    Code:
    Debug.Print "VarType=" & VarType(Range("InputValues!Shield1"))
    If you do not get 8 (string), that could explain why casting the "value" of the range to a string by assignment gives you a different result.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •