none
VBA Unique List like AutoFilter Dropdown RRS feed

  • Question

  • Is there a quick way to generate a unique list array from a custom field (e.g. Text1) like MS Project Standard 2007 presents in the AutoFilter drop-downs without cycling through all tasks and comparing each task.Text1 value to each unique entry already stored in an array and redimensioning that array with each addition?
    Thursday, May 2, 2013 1:33 AM

Answers

  • Dennis,

    "Quick" is a relative term. One way to speed things up is to dimension the array once at the beginning. How do you do that when the file can contain any number of tasks? Simple. Count the tasks at the onset. Then dimension the array to that value. There will never be more elements in the array than there are tasks. Will the array size be overkill? Maybe, but the storage space is a lot "cheaper" than the overhead or re-dimensioning over and over.

    Something else to consider to speed things up. If you know that not every task will have a value in Text1, then apply a filter first and operate on the elements in that limited set.

    But to answer your question directly, no, I can't think of a "quicker" way.

    John

    • Marked as answer by Dennis Kern Friday, May 3, 2013 3:21 PM
    Thursday, May 2, 2013 2:49 AM

All replies

  • Dennis,

    "Quick" is a relative term. One way to speed things up is to dimension the array once at the beginning. How do you do that when the file can contain any number of tasks? Simple. Count the tasks at the onset. Then dimension the array to that value. There will never be more elements in the array than there are tasks. Will the array size be overkill? Maybe, but the storage space is a lot "cheaper" than the overhead or re-dimensioning over and over.

    Something else to consider to speed things up. If you know that not every task will have a value in Text1, then apply a filter first and operate on the elements in that limited set.

    But to answer your question directly, no, I can't think of a "quicker" way.

    John

    • Marked as answer by Dennis Kern Friday, May 3, 2013 3:21 PM
    Thursday, May 2, 2013 2:49 AM
  • If you really have a lot of tasks and unique values, consider copying the entire Text field in one go to Excel then use Excel's remove duplicates feature.

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Thursday, May 2, 2013 7:56 PM
    Moderator
  • Thank you John & Rod.  Appreciate the quick feedback.  I'll end up implimenting John's recommendation as I believe the array method will run faster than automating Excel from within Project.
    Friday, May 3, 2013 3:27 PM
  • Dennis,

    You're welcome and thanks for the feedback. I use arrays in all of my moderate and complex macros. I've also done data sorting routines in several and they run very fast.

    John

    Friday, May 3, 2013 3:43 PM