Excel VBA error when calling MS Project Task's Unique ID

Question

In an Excel worksheet, I am iterating through tasks in a MS Project file with VBA.

I've found that my code works perfectly until I encounter a MS Project task that does not have a name but the error is confusing me. When I delete these unnamed tasks, the error goes away.

I tried adding an if statement: If prj.Tasks(i).UniqueID Is Nothing Then to catch this error but I get a Type mismatch error.

Dim i As Integer
Dim TaskID As Long
Dim ExcelRow As Integer
For i = 1 To prj.Tasks.Count
    TaskID = prj.Tasks(i).UniqueID  'This line is highlighted when I debug
    ExcelRow = GetRowByUniqueID(TaskID)
    Debug.Print ExcelRow

My error is Object variable not set (Error 91). I am confused by this because I was under the impression that all Tasks have a unique ID. When I open my .mpp file in Microsoft Project, I can see that the tasks have no name but have a unique ID in the Unique ID column.

Edit: I just realized it is not an issue with prj.Tasks(i).UniqueID but rather an issue with prj.Tasks(i). How is it possible that this variable is not set when i is within the range of the number of Tasks?

viviansplatoon · Accepted Answer · 2019-07-18 19:50:34Z

2

Okay so I just figured it out. Somehow, when I iterate through all the Tasks, some of them are Nothing. I don't really understand why but at the very least, I was able to catch this with an if statement:

If prj.Tasks(i) Is Nothing Then
    Debug.Print "This is nothing."

Else
    TaskID = prj.Tasks(i).UniqueID
    ExcelRow = GetRowByUniqueID(TaskID)
    Debug.Print ExcelRow

Now, my code does not break at all.

answered Jul 18, 2019 at 19:50

viviansplatoon

1481 silver badge16 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Mathieu Guindon Over a year ago

Since Tasks is an object collection, would declaring a local currentTask variable and then iterating with For Each currentTask In prj.Tasks work? (it would probably be more efficient anyway, since object collections want to be iterated with For Each).. it's very odd that Tasks contains Nothing items.

viviansplatoon Over a year ago

@MathieuGuindon Thanks for your suggestion. I tested that out and I'm getting the same error as I had before. After I added the if statement again, the code no longer breaks. So even with the For Each currentTask in prj.Tasks, some currentTasks are still Nothing ...

Mathieu Guindon Over a year ago

Interesting. Looks like the Project API is somewhat broken - I'm not familiar with it at all, but that's definitely surprising behavior. Indeed, that null-check seems to be your only hope.

Rachel Hettinger Over a year ago

@vivianyoung testing for nothing is the standard way to handle this when looping through tasks. As @MathieuGuindon mentioned, using a for...each loop also works and is often preferable due to not having to first know how many tasks there are (and it looks cleaner).

Mathieu Guindon Over a year ago

@RachelHettinger and it's dramatically more efficient for iterating object collections

|

Collectives™ on Stack Overflow

Excel VBA error when calling MS Project Task's Unique ID

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related