UnDo in VBA

[SteDal]

TASK: I am updating RemainingDuration and other fields on a Schedule and is
capturing the resulting scheduling changes. For each set of changes, I need
to Undo the changes so that the first set of changes do not interfere with
the second set of changes, and so on. I therefore need VBA to do an UnDo
after each set of changes. The following is a simplified version based on
the example on Page 441 in Rod Gill’s excellent book:

Sub TestUndo()
Application.OpenUndoTransaction "Test"
ActiveProject.Tasks.Add "New Task"
Application.CloseUndoTransaction
Application.Undo 1
End Sub

The 'Application.Undo 1' statement creates R-t error 1100: 'This method is
not available in this situation.'
I tried replacing it with “EditUndo†and get the same error.

The only way I see out of this is placing the VBA in Excel, and closing
Project without Saving, then opening again (Application.FileOpenEx) for the
next round of tests. Not an elegant solution. As the code must also work with
Project Server, I must find the logon details and pass on to
Application.FileOpenEx.

I hope someone out there can propose a better solution, ?

 

[Jack Dahlgren MVP]

If you just want to add a task then remove it (that is what your code says)
then you could just add it then delete it.
If you are trying to do something else, then post what it is you are trying
to do and perhaps we may be able to to help you figure out a better way.

-Jack Dahlgren

 

[Rod Gill]

The Undo code (in my excellent book - thanks!) only works for Project 2007
so if you have 2003 or earlier, it won't.

With 2003 you will need to do a close with no save or simply reverse the
previous changes using code.

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[SteDal]

Thanks Jack and Rod,

I am using MsP 2007 Professional with SP1.
The following is a shortened portion of my real code, 1st attempt:

Sub TestUndo2()
Dim tsk As Task
Dim i_ID As Long: i_ID = 80
Dim i_RemDur As Long
For Each tsk In ActiveProject.Tasks ' Set RDu large
If Not tsk Is Nothing Then
If tsk.ID = i_ID Then
i_RemDur = tsk.RemainingDuration
tsk.RemainingDuration = tsk.RemainingDuration + 48000 ' 100
days
End If
End If
Next tsk ' this is End of For Each
For Each tsk In ActiveProject.Tasks ' Capture the results
' Capture the results
Next tsk
For Each tsk In ActiveProject.Tasks ' Re-Set RemainingDur
If Not tsk Is Nothing Then
If tsk.ID = i_ID Then tsk.RemainingDuration = i_RemDur
End If
Next tsk ' this is end of For Each
End Sub

The above is the Do-it-yourself UnDo like Jack suggested. Trouble with this,
is that is does not always work; the schedule some times remains changed
after tsk.RemainingDuration = i_RemDur (I can provide several examples, but
that is not the topic here), but a manual UnDo re-set perfectly. So I set out
to let VBA do the UnDo. So I re-wrote the above to:

Sub TestUndo3()
Dim tsk As Task
Dim i_ID As Long: i_ID = 80
Dim i_RemDur As Long
Application.OpenUndoTransaction "Test"
For Each tsk In ActiveProject.Tasks ' Set RDU large
If Not tsk Is Nothing Then
If tsk.ID = i_ID Then
i_RemDur = tsk.RemainingDuration
tsk.RemainingDuration = tsk.RemainingDuration + 48000 ' 100
days
End If
End If
Next tsk ' this is End of For Each
For Each tsk In ActiveProject.Tasks ' Capture the results
' Capture the results
Next tsk
Application.CloseUndoTransaction
Application.Undo 1
'EditUndo ' Alternative to Application.Undo 1 ?
End Sub

Again the Sub TestUndo3 is a shortened version of the real code. While
testing the shortened Sub TestUndo3, I discovered it works!
The real code still does not work. The above TestUndo3 and the real code are
identical for the loop that updates RemainingDuration. The main difference is
that TestUndo3 does nothing is the loop that examines the result of the
chance in RemainingDuration the real version reads quite a lot of schedule
data, but don't update any Task or Assignment Fields.

So I guess my real question is in which situations 'Application.Undo n'
works and when it does not work?

The error message is
Run time error 1100. This method is not available in this situation.

 

[Jack Dahlgren]

If you post the code that works, and don't post the code that doesn't work it
is impossible to debug.

To get any further you will have to post what is NOT working. Post which
line it is choking on.

With broken code it may be possible to debug. VBA works in all situations
that it works in. It does not work when it doesn't work or when the author
has made an error. Without the code sample it is impossible to tell what the
situation is and either suggest a correction or a possible work around.

The first step in debugging someone else's code is replicating the problem.
We can't do that yet.

-Jack Dahlgren

 

[SteDal]

I have now investigated this, and have found a pattern that I think explains
what happens. Here is shortened code that fails in MsP VBA:

Function Test_MSDN(i_ID As Long, i_RemDurDays As Long)
Dim proj_App As MSProject.Application
Dim d As Date
Dim d1 As Date
Dim Tsk As Task
Dim i_MpD As Long ' Minutes pr day
Set proj_App = GetObject(, "MSProject.Application")
i_MpD = ActiveProject.HoursPerDay * 60
proj_App.OpenUndoTransaction "Crit. path test"
'OpenUndoTransaction "Crit. path test"
d = ActiveProject.ProjectFinish ' storing current project completion
For Each Tsk In ActiveProject.Tasks ' Set RDU large
If Not Tsk Is Nothing Then
If Not Tsk.Summary Then
If Tsk.ID = i_ID Then Tsk.RemainingDuration =
Tsk.RemainingDuration + (i_RemDurDays * i_MpD)
End If
End If
Next Tsk ' this is end of For Each
d1 = ActiveProject.ProjectFinish
proj_App.CloseUndoTransaction
proj_App.Application.Undo 1 ' undo one Undo block
'CloseUndoTransaction
'MsgBox "Wait for MsP"
'Application.Undo 1 ' undo one Undo block
Test_MSDN = d1 - d
End Function

The above code has also been run in Excel, which is why it contains lines
starting with ', referring to the open MsP Application.

Running in Excel it works!
Also, when running in MsP it works if I include the MessageBox between
"CloseUndoTransaction" and "Application.Undo 1".

To me it looks as i MsP needs some time to react, which it gets when Excel
is the master (much slower processing), and by use of the MessageBox. I run
it from Excel.

I have spent about 30 hours researching this, hope this post is of use to
others.

 

[Rod Gill]

HI,

Does EditUndo work?

try inserting a DoEvents command to let Project and Windows catch up.
--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com