none
How to make a subset array of tasks in MS Project VBA without filters RRS feed

  • Question

  • Hi.  I was wondering if there is an easy way to copy a task to a new array in order to generate a subset of the original task array.  I don't want to create new tasks, just a new array with certain tasks that meet certain conditions.  I don't want to use filters for this because of how dynamic the array has to be (growing and reseting with time).

    This simple example code may help understand what I am trying to do (or it may just complicate things).  Thanks in advance for the help.

    Sub Subsample()
        
    '
    Dim varTask As MSProject.Task
    Dim varTaskContainer As Variant
    Dim varTaskContainerSubset As Variant
    Set varTaskContainer = ActiveProject.Tasks
        For Each varTask In varTaskContainer
            If varTask.Text5 = "Yes" Then
    '           Make a copy of vartask in varTaskContainerSubset ...
    '           I understand I might be way off on this.
                varTaskContainerSubset.Tasks.Add varTask
            End If
        Next varTask
            
    End Sub

    Thursday, February 7, 2013 11:04 PM

Answers

  • Rick,

    When the filter is created it will either be saved with the file or reside in the Global, unless your code deletes it from the Organizer when that macro finishes. So, assuming it is not deleted, all the next macro has to do it to apply that filter, which already exists. After all your macros are run, you might want to delete any filters it created. That's what I normally do when I create a filter in code. It keeps things clean and avoids the issue of having to deal with an existing filter the next time the whole process is run.

    I've never written a macro that interfaced between Project and PowerPoint, all of my code has either been internal to Project, or between Project and Excel. I've used three different methods but my preferred method is to start in Project, gather all the data into arrays, then open Excel and dump the arrays into a formatted workbook(s). I don't really know if that is faster than doing it all on the fly (i.e. read an element of Project data, write it into Excel, read the next element of Project data, etc.). I guess I find it easier to manipulate the data if it is in arrays.

    By the way, if you are using Project 2010 and userforms, I assume you installed the hotfix that addresses a crashing issue with userforms in Project 2010.

    John

    Monday, February 11, 2013 7:12 PM
  • OK so rather than creating our separate project or trying to create a whole new collection of tasks try this:

    1. Apply Filter A
    2. Do a select All
    3. Place the ID of the tasks in the Activeselection.Tasks collection into Array A
    4. Repeat 1-3 for all your filters
    5. You can then access each task (start date in this example) then by using Activeproject.tasks(ArrayA(<countervariable>).Start

    does that work?


    Brian Kennemer - Project MVP
    DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn

    • Marked as answer by Ocean_Engineer Monday, February 11, 2013 8:15 PM
    Monday, February 11, 2013 7:21 PM
    Moderator

All replies

  • Ocean_Engineer,

    You mention arrays, but there is nothing in your mock code that sets up an array, much less dimension it. Also it's not clear why a filter won't work. A filter isn't a fixed "thing", the objects, (in this case tasks), it selects will vary depending on the criteria. For example, you use text5 = "yes" as the criteria. As the number of tasks with text5 set to yes varies, so will the output of the filter (i.e. number of tasks selected).

    What exactly is your end goal?

    John

    Friday, February 8, 2013 3:04 AM
  • Sorry for my inexperience.  I was thinking that the container was considered a array.

    Without going through the hundred requirements...  I have a Container with tasks that have been through a filter.  As I work my way through the logic and process execution, one task at a time, I want to place a dependancy task (Dep.To) or possibly another task (doesn't really matter) into a separate container.  This isn't something I can write a filter on that I know of, because as I cycle through the tasks at a certain point, I have to reset the subset container and start populating again.

    I am generating powerpoint slides with info from MS Project and when I get to a certain point I generate a new slide and would need to clear the subset container.


    Friday, February 8, 2013 7:02 PM
  • Ocean_Engineer,

    A container could be an array if so defined but just calling it a container doesn't do it.

    You say you have a container with tasks. What exactly is this "container"?

    In Project there are three main object types, tasks, resources and assignments (an lots of other "supporting" objects such as the calendar object). Each object has multiple properties. For example, the name, duration, start, finish, etc. are all properties of the task object.

    In VBA arrays contains discrete elements of data. Since a task is an object with many elements (i.e. properties), those properties can be put in an array but not the object itself, at least not in an easy straightforward fashion.

    Okay, so you're working your plan a task at a time. That makes sense. But I'm getting lost on the part where you want to create something else as you work through the tasks. Because you relate to PowerPoint it sounds like you are making periodic presentations with some date from Project. What is that data?

    John

    Friday, February 8, 2013 8:34 PM
  • It is a container of tasks. After filtering, I do a SelectALL and then set "Container Variant" to ActiveSelection.

    Kind of like a symbolic Gannt chart.  But Have to make sure I make room for successors per tasks displayed so far... etc etc.  I am (currently, though scope always changing) primarily using task name, start/end of task, successors, predeccessor, a few text fields,a few flag fields, etc.  Was thinking there should be an easy way to just copy that singular object with all it's properties ( I think I am using the right term now).    

    Even something like a For Each property of Task

                                     copy property to different container variant

    And have VBA recognize the properties of the subset when I call them.

    From what you are saying though, I might be better off generating some type of array with just UniqueID's of the tasks I want to call a subset and then using those as pointers.  A little bit clunky for what I wanted to do...  but if I have to, that is possible.

    Thanks for the help.


    Friday, February 8, 2013 9:23 PM
  • I think the word you are looking for is collection. The activeselection.tasks is a 'collection' as is activeproject.tasks or activeproject.resources.

    That said there is not a way to copy an entire task into some 'other' collection. Or at least I cannot figure a way to do it. In theory you could create a custom collection of tasks and add them but I cannot get this to work.

    You could use a 'scratch pad' project as your collection. The code below will copy certain data about the tasks in the active selection of one project and add them to a new project. You could do this for your custom collection. Then while your code as running you would have the tasks and the data you needed over in this new project. Then when you code was finished you could just delete the project. here is the code.

    Sub collect()
    
    Dim pNew As Project
    Dim pActive As Project
    Dim tNew As Task
    Dim tOld As Task
    
    Dim tSelection As Tasks
    
    Set pActive = Application.ActiveProject
    Set tSelection = Application.ActiveSelection.Tasks
    
    Set pNew = Application.Projects.Add
    
    For Each tOld In tSelection
        Set tNew = pNew.Tasks.Add(tOld.Name)
        tNew.Work = tOld.Work
        tNew.Duration = tOld.Duration
        tNew.predecessors = tOld.predecessors
    Next tOld
    
    End Sub


    Brian Kennemer - Project MVP
    DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn

    Saturday, February 9, 2013 2:32 AM
    Moderator
  • I think you use of container or collection is causing confusion. VBA has an object called collection, but it only saves a single piece of data. An array is also a specific thing in VBA (see Help).

    If you want to copy data, as Brian suggests you need to copy each datum separately. It's easiest to filter your tasks first, then select all and loop through all selected tasks with:

    For Each tsk in Activeproject.ActiveSelection

    As your data changes, simply re-apply your filter to get the latest matching tasks.

    What do you actually want to do with your subset of tasks?


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Saturday, February 9, 2013 9:40 AM
    Moderator
  • Ocean_Engineer,

    I see the other two guys jumped in with their clarification but let me add my two cents.

    When you apply your initial filter all you are doing is telling Project to display a select group of data from its underlying data structure. Using VBA you can use foreground processing to loop through the tasks in filtered set. The basic structure for doing that is:

    Dim t as Task

    For Each t in ActiveSelection.Tasks

    (do some stuff)

    Next t

    You end goal still isn't clear but it sounds like you want to filter your project, then take the tasks resulting from the filtered set and copy them to a new project file. I don't understand why you want to do that but yes that can be done within the loop.

    John

    Saturday, February 9, 2013 3:48 PM
  • Sorry for all the confusion.  I have read about them being called collections.  I have been doing the For Each.  Actually quite a bit, including with recursives.  I think Brian understands the most about what I have been trying to do except due to the sensitive nature of the data, I don't think the client is going to want a second project created (even if I do delete afterwards, I know he will be nervous), not to mention that with the thousands of tasks and potentially number of powerpoint slides I will be generating / causing a large number of resets... the time that it will take from the users perspective to run the macro is going to start looking too slow.

    I don't want all of the results of a the filter in another project, just certain ones as I step through and determine whether they are slide worthy.  Too complicated and dynamic for a straight filter for each circumstance, because I would need a hundred of them when all is said in done to deal with all the decision making.    

    The only other thing I can think of is if when I run the filter, maybe it would only run on the currently displayed instead of the whole project... but no, that isn't a good idea either, because it is a limited subset of the filter.  Like I said, although not clearly, I am trying to slowly build a second subset that has successors (just for that slide, until it fills up) so I know how much space to leave on the bottom of my slide (symbolic Gannt chart) and then have all the information I need to draw thier symbolic representation.  Sounds like my idea of an array of Unique ID's as a pointer to the collection is probably going to be the only way to go.

    I know another way to go would be to set flags on the ones for the subset and then check and clear them, but I that puts me back to not a clean solution.

    Thanks for all the help and clarification.  I know this can be frustrating when I can't describe the whole project.   


    Monday, February 11, 2013 3:37 PM
  • Ocean_Engineer,

    You're welcome and thanks for the feedback.

    If you first run a filter and then manually step through the selected tasks to pick out those "worthy" of being on the PowerPoint chart, it is certainly easy enough to set a flag. Running another filter on that flag gets you to the final set. Clearing that flag for the next time is a very simple process, whether done manually, (i.e. filter, clear flag on first task, then fill down), or with simple VBA code. I don't understand how this is not a "clean" solution.

    Yes you could set an array to capture the unique ID's of the tasks you desire, but unless you have more information about how you will select those unique IDs that you are not able to share with us, this approach seems more complex and no cleaner than using a couple filters as described above.

    Just my thoughts.

    John

    Monday, February 11, 2013 5:05 PM
  • John,

          You are correct that setting flags, keeping track of how many I have set and then when time comes to use them filter based on the flag isn't that different as long as I know what the filter is that is being set before hand (all the grunt work is done in a separate sub after initial filter screenings, dependant on users interation with the GUI/Userform, and a couple of different times running the grunt work sub).  Is there a way to easilly "Return to previous filter?"  Since I am naming them, I guess I could pass the name as a string variable to the sub and then call that when needed. 

     My biggest deterrent is that I am trying to tell the client that I am not modifying his project file (or making a copy).  So far I have been able to do that.  Also, I feel like I have seen that having the VBA code bounce between Powepoint and Project as oppossed to staying in one application seems to help with the speed, although I could be mistaken there.

    Thanks again.

    Rick

    Monday, February 11, 2013 5:32 PM
  • Rick,

    When the filter is created it will either be saved with the file or reside in the Global, unless your code deletes it from the Organizer when that macro finishes. So, assuming it is not deleted, all the next macro has to do it to apply that filter, which already exists. After all your macros are run, you might want to delete any filters it created. That's what I normally do when I create a filter in code. It keeps things clean and avoids the issue of having to deal with an existing filter the next time the whole process is run.

    I've never written a macro that interfaced between Project and PowerPoint, all of my code has either been internal to Project, or between Project and Excel. I've used three different methods but my preferred method is to start in Project, gather all the data into arrays, then open Excel and dump the arrays into a formatted workbook(s). I don't really know if that is faster than doing it all on the fly (i.e. read an element of Project data, write it into Excel, read the next element of Project data, etc.). I guess I find it easier to manipulate the data if it is in arrays.

    By the way, if you are using Project 2010 and userforms, I assume you installed the hotfix that addresses a crashing issue with userforms in Project 2010.

    John

    Monday, February 11, 2013 7:12 PM
  • OK so rather than creating our separate project or trying to create a whole new collection of tasks try this:

    1. Apply Filter A
    2. Do a select All
    3. Place the ID of the tasks in the Activeselection.Tasks collection into Array A
    4. Repeat 1-3 for all your filters
    5. You can then access each task (start date in this example) then by using Activeproject.tasks(ArrayA(<countervariable>).Start

    does that work?


    Brian Kennemer - Project MVP
    DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn

    • Marked as answer by Ocean_Engineer Monday, February 11, 2013 8:15 PM
    Monday, February 11, 2013 7:21 PM
    Moderator
  • Brian,

    That is a cool trick. 

    All the filtering and multidimension array isn't needed.  Most of my logic is done in the grunt work code rather than filtering.

    I was able to code using an array of just the unique ID's as I thought I might have to.   Pretty easy and only a dozen lines of code throughout a 1000, so no big deal.

    This an example I think where the newbie in me just made things more complicated than need be.  Looks like I can use the UniqueID 's in my arrays for things like this:

    Activeproject.tasks(ArrayA(<countervariable>).Start

    to make things more robust, streamlined and elegant going forward.   Almost too simple.

    Thanks,

    Rick

    Monday, February 11, 2013 8:10 PM
  • I saw something about the hotfix, but thought somebody said it was suppossed to be rolled into a cumulative patch... and didn't work now without rolling back , install hotfix and roll forward.  So I didn't investigate more.  I have seen what I think may have been the problem though... so I might look into this more.  Thanks for the reminder.
    Monday, February 11, 2013 8:13 PM
  • I think John was mentioning arrays as well.

    for sure it is easy to get yourself into a narrow path and think that is the only way. We have all been there.

    For sure mark John's last post as an answer and mine as well if you are so inclined. :-)


    Brian Kennemer - Project MVP
    DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn

    Monday, February 11, 2013 8:20 PM
    Moderator
  • Rick,

    There seems to be confusion and/or misunderstanding about cumulative updates. You might want to read the following thread:

    http://social.technet.microsoft.com/Forums/uk/project2010custprog/thread/61d2951c-cba9-4336-b388-e5cec498f58c

    I installed the specific hotfix for the userform issue, and only that hotfix, and it seems to work for me.

    John

    Monday, February 11, 2013 8:56 PM
  • I tried to go there but when I went to request the hotfix it asked me to type in letters from a picture.  Unfortunately I couldn't see the picture.  Could be corporate not letting me.  I will try from my personal laptop at home.  Just had another problem with my userforms suddenly not being found again so I would love to have this fixed :)

    Tuesday, February 12, 2013 8:22 PM