none
Nested IIfs and Switch in Formulas for customized fields RRS feed

  • Question

  • Is there a limit to the number of Nested Iifs in MS Project for formulas in a customized field? The same question for the Switch instruction in the same context, is ther a maximum number of search entries. All I am trying to do is, based on finding text in the [Name] field (instr), I want to place a value in one of the customized fields. How do I do this, and/or circumvent any limitations there might be.  The problem could also be a limitation to how long a formula can be. Thanks

    Steve DeSomer


    • Edited by sdesomer Tuesday, February 26, 2013 6:21 PM
    Tuesday, February 26, 2013 6:04 PM

Answers

  • Steve,

    Yes, the limit for nested "iifs" is 15. I don't know for sure but I assume a similar limit applies to the switch statement.

    I suggest you use multiple custom fields. Break your long complex formula into parts and then use another custom field to operate on the results of those.

    Hope this helps.

    John

    • Marked as answer by sdesomer Tuesday, February 26, 2013 8:55 PM
    Tuesday, February 26, 2013 6:54 PM

All replies

  • Steve,

    Yes, the limit for nested "iifs" is 15. I don't know for sure but I assume a similar limit applies to the switch statement.

    I suggest you use multiple custom fields. Break your long complex formula into parts and then use another custom field to operate on the results of those.

    Hope this helps.

    John

    • Marked as answer by sdesomer Tuesday, February 26, 2013 8:55 PM
    Tuesday, February 26, 2013 6:54 PM
  • Thanks, that was helpful. Would there be a snippet of VBA code that might work? Thanks

    Steve DeSomer

    Tuesday, February 26, 2013 7:06 PM
  • VBA code is much more flexible, but you need to run a macro to re-populate your custom field. A snippet of code isn't realy useful unles you have stated what you want to achieve. But here is one:

    Select Case Tsk.Text1
       Case 1 to 100
          'Value 1 to 1000 code
       Case "X"
          'Value X code
       Case Else
          'All other values code
    End Select


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Tuesday, February 26, 2013 8:06 PM
    Moderator
  • Steve,

    You're welcome and thanks for the feedback. It looks like Rod stepped in with a sample of VBA that might be a starting point but as he said, without knowing the exact details of what you are trying to do, we can only provide sample code.

    The advantage of using a formula is that it updates automatically. With VBA you would need to run the macro to get results, unless you used an event based macro, but that's a little more complex setting up. The advantage of VBA is that you don't have the limitations you have with a formula and troubleshooting VBA code is generally much easier than trying to troubleshoot a complex formula in a custom field.

    One other caution, string operations, whether in a custom field formula or in VBA code, can be a little tricky. Things like capitalization, misspelling, words within words (e.g. "ten" in "tension" and "maintenance"), can trip up searches for words or phrases, so think carefully when dealing with strings.

    John

    Tuesday, February 26, 2013 9:10 PM