Simple question regarding MS Project, VBA and UID

S

Steve

I have a lot of experience with VBA in Excel, but am really struggling to
learn VBA in Project.

I have a simple question:

I want to write a macro that changes fields of a specific row. However,
based on the dynamics of the project, I want to refer to the task by UID
rather than ID.

For example:
SelectTaskField Row:=50, Column:="Duration", RowRelative:=False
SetTaskFiled Field:="Duration", "Value:="50"

This works. But if I add or delete tasks, it is no longer Row 50. I would
like to do this with UID. Do I have to write code that will cycle through
all 3000 rows until I find the UID? Or is there a simple way?

MS Project VBA help is not helpful.

Thank you.
Steve
 
J

John

Steve said:
I have a lot of experience with VBA in Excel, but am really struggling to
learn VBA in Project.

I have a simple question:

I want to write a macro that changes fields of a specific row. However,
based on the dynamics of the project, I want to refer to the task by UID
rather than ID.

For example:
SelectTaskField Row:=50, Column:="Duration", RowRelative:=False
SetTaskFiled Field:="Duration", "Value:="50"

This works. But if I add or delete tasks, it is no longer Row 50. I would
like to do this with UID. Do I have to write code that will cycle through
all 3000 rows until I find the UID? Or is there a simple way?

MS Project VBA help is not helpful.

Thank you.
Steve

Steve,
Yes there is a different mind set needed to understand the Project
object model compared to Excel's object model. I find some things seem
infinitely easier in Project and vice versa. It's all a matter of
learning the basics.

In Project, there are three main object types - Task, Resource and
Assignment. Excel works with rows and column whereas Project works with
tasks (displayed as rows) and fields (displayed as columns).

The SelectTaskField method in Project is actually referencing the row
number of the visible display (as do all the "select" type methods). In
other words, row 50 does not mean ID 50. For example, if a filter is
applied, row 50 is the 50th displayed row, whether or not task ID 50 is
one of those displayed rows or not.

Also, any "select" method used in VBA requires foreground processing -
the current view is germaine. If at all possible, it is much better to
write VBA macros that use background processing, that is, work directly
with Project objects independent of what is displayed in the current
view.

There are various ways to change fields in Project, some may require the
use of foreground processing but this should be the exception. If we
knew a little bit more about your end goal, we could probably help you
better.

John
Project MVP
 
R

Ran @Comverse

Hi,

Why not to use this one:

ActiveProject.Tasks.UniqueID(UID).Duration=50

And if it is GUID so it will be like that:

ActiveProject.Tasks(GetTaskIndexByGuid(GUID)).Duration=50

Ran.
 
S

Steve

Hi,

Thank you for your quick reply.

I'm trying to set up for a demonstration.....too many times the person
running the keyboard has made mistakes during the demonstrations so I'm
trying to automate it a little. For example,

1. I want to highlight the duration cell of 3 tasks that are shown
sequentially (UID 89,90,91). Because I may be in a different view or filter,
I need to refer to these independent of the view shown (although I'll make
sure that all 3 are in sequence). This works if I use the following, but
again...it is view-specific and will cause problems unless I refer to them
independent of the view or filter.

SelectTaskField row:=3, Column:="Duration", Height:=2

2. I also need to change the duration of specific tasks....again,
independently of the view. I got this and it seems to work so far

SetTaskField Field:="Duration", Value:="60",
TaskID:=ActiveProject.Tasks.UniqueID(84).ID

except it seems cumbersome.

3. And finally, I need some sort of way to allow me to step through the
code while in MS Project rather than the VBA. In the demonstrations, I make
a few changes, highlight a few cells.....then I need to pause for discussion.
After discussion, I run through some more steps. Currently, I use a msgbox
but it is cumbersome and unprofessional. In other words, I run three
lines/steps of code....have a msgbox.....run another few lines of
code....then a msgbox. In Excel, I would use a userform or a control.
Haven't figured out how to do that in Project yet. I can use a menu button
or anything that isn't awkward like a msgbox.

Thanks.
Steve
 
J

John

Steve,
My comments are in-line
Thank you for your quick reply.

I'm trying to set up for a demonstration.....too many times the person
running the keyboard has made mistakes during the demonstrations so I'm
trying to automate it a little. For example,

1. I want to highlight the duration cell of 3 tasks that are shown
sequentially (UID 89,90,91). Because I may be in a different view or filter,
I need to refer to these independent of the view shown (although I'll make
sure that all 3 are in sequence). This works if I use the following, but
again...it is view-specific and will cause problems unless I refer to them
independent of the view or filter.

SelectTaskField row:=3, Column:="Duration", Height:=2
Well, it can't be independent of the view since as I stated previously,
all "select" methods refer to the current active view. And to highlight
something in Project, (using VBA), you have to use a select method -
unless the field cell has already been selected from a previous action.
This is one area where Excel "excels" over Project - In Excel, cell font
characteristics can be set using background processing.
2. I also need to change the duration of specific tasks....again,
independently of the view. I got this and it seems to work so far

SetTaskField Field:="Duration", Value:="60",
TaskID:=ActiveProject.Tasks.UniqueID(84).ID

except it seems cumbersome.
This is easy for background processing. For example to set the duration
of Unique ID 6 to 10 days, the following statement is all that is needed:
ActiveProject.Tasks.UniqueID(6).Duration = 4800
Note that duration is expressed in minutes and for a normal work day,
there are 480 minutes in a day.
3. And finally, I need some sort of way to allow me to step through the
code while in MS Project rather than the VBA. In the demonstrations, I make
a few changes, highlight a few cells.....then I need to pause for discussion.
After discussion, I run through some more steps. Currently, I use a msgbox
but it is cumbersome and unprofessional. In other words, I run three
lines/steps of code....have a msgbox.....run another few lines of
code....then a msgbox. In Excel, I would use a userform or a control.
Haven't figured out how to do that in Project yet. I can use a menu button
or anything that isn't awkward like a msgbox.
There are various ways to do this.

One method I use all the time when I'm troubleshooting code I'm writing
is to display the VB editor as a small window over the file I'm working
with (Project or Excel). I either set up breakpoints in the code, (a
Stop statement also works nicely for a more permanent breakpoint), or
step through it to the code line of interest. I then activate the
application window to view/analyze the result. When I want to go on, I
activate the VB editor window again and step or run to the next
breakpoint. This method works great but you may find it a bit cumbersome
in a presentation scenario - it depends on the audience.

Another way to do this is with a userform just like you do in Excel.
Although I've never used an interactive userform (where control passes
from userform to application and back at prescribed points), the setup
for Project should be the same.

A final thought. For the last program I worked on, we had a monthly
program review day. Each cost account manager (CAM) walked through their
status (both technical and formal earned value) in a group meeting with
other CAMs, the program manager, a customer representative, our program
business office and other interested parties. Prior to the meeting, I
made a consolidated master file of all CAM files. I also had a couple of
macros that I could use to delineate certain characteristics of the CAM
file (e.g. 30-day look-ahead, probability of cost or schedule variance
given the current trend, etc.). We did not change any file data during
the meeting, (doing so required a formal change request document), but
we could manually highlight any fields of interest during the discussion
either by selecting the field cell or with a laser pointer. Data errors
that were discovered were noted in the meeting minutes so they could be
addressed after the meeting.

Hope this helps.
John
Project MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top