macro execution

[Walter L. skinner]

I have a 174 line macro that searches task and checks dates and changes the
font color depending on the checks. The file is approximately 8000 lines. The
problem is that it takes almost an hour to get through the file apparently
because of all the I/O and displaying on the screen. Is there a way to speed
up this macro execution by either making an executible module vice
interpreting or to turn off the display refresh while the macro is running.


Thanks for your help

Walter L. Skinner

 

[JackD]

It should not take nearly this long. Without seeing the code though it is
difficult to say exactly what is wrong.
If you post the macro perhaps we can find a way of making it more efficient.
How does it behave on a machine with more memory?

--
-Jack ... For Microsoft Project information and macro examples visit
http://masamiki.com/project
or http://zo-d.com/blog/index.html




..

 

[Walter L. skinner]

Here is the code. Thanks in advance for your help.


Sub USG_RAAF_Late()
' Macro USG_14_DAY
' Macro Recorded 5/5/05 by g386179.
Dim Late_Date As Date
Dim Start_Date As Date
Dim Look_Ahead_Date As Date
Dim Entered_Date As Date
Dim sw3 As Integer
Dim SearchString, SearchChar, MyPos
Dim quit_sw As Integer
quit_sw = 0
MyPos = 0
sw3 = 0
WindowSplit
ViewApply Name:="Walts view text1"
SelectTaskColumn Column:="Predecessors"
TableEdit Name:="Walts table text1", TaskTable:=True, NewName:="",
FieldName:="", NewFieldName:="Unique ID", Title:="", Width:=10, Align:=2,
ShowInMenu:=False, LockFirstColumn:=True, DateFormat:=255, RowHeight:=1,
ColumnPosition:=1, AlignTitle:=1
TableApply Name:="Walts table text1"
SelectTaskColumn Column:="Unique ID"
Font Size:="7"
TableEdit Name:="Walts table text1", TaskTable:=True, Align:=1,
ColumnPosition:=1
TableApply Name:="Walts table text1"
ColumnAlignment Align:=pjCenter
SelectTaskColumn Column:="Predecessors", Additional:=1
Font Size:="7"
TableEdit Name:="Walts table text1", TaskTable:=True, Create:=True,
OverwriteExisting:=True, FieldName:="ID", Title:="", Width:=5, Align:=2,
ShowInMenu:=False, LockFirstColumn:=True, DateFormat:=255, RowHeight:=1,
AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True,
NewFieldName:="Name", Title:="", Width:=73, Align:=0, LockFirstColumn:=True,
DateFormat:=255, RowHeight:=1, AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True,
NewFieldName:="Unique ID", Title:="", Width:=7, Align:=1,
LockFirstColumn:=True, DateFormat:=255, RowHeight:=1, AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True,
NewFieldName:="Predecessors", Title:="Predecessors", Width:=7, Align:=2,
LockFirstColumn:=True, DateFormat:=255, RowHeight:=1, AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True,
NewFieldName:="Successors", Title:="Successors", Width:=7, Align:=2,
LockFirstColumn:=True, DateFormat:=255, RowHeight:=1, AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True,
NewFieldName:="Flag1", Title:="Weekly Status", Width:=0, Align:=2,
LockFirstColumn:=True, DateFormat:=255, RowHeight:=1, AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True,
NewFieldName:="Text1", Title:="", Width:=6, Align:=2, LockFirstColumn:=True,
DateFormat:=255, RowHeight:=1, AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True,
NewFieldName:="Text6", Title:="", Width:=5, Align:=2, LockFirstColumn:=True,
DateFormat:=255, RowHeight:=1, AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True,
NewFieldName:="Text2", Title:="Resp", Width:=13, Align:=2,
LockFirstColumn:=True, DateFormat:=255, RowHeight:=1, AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True, NewFieldName:="%
Complete", Title:="%", Width:=5, Align:=2, LockFirstColumn:=True,
DateFormat:=255, RowHeight:=1, AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True,
NewFieldName:="Duration", Title:="Dur", Width:=0, Align:=2,
LockFirstColumn:=True, DateFormat:=255, RowHeight:=1, AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True,
NewFieldName:="Baseline Start", Title:="BL Start", Width:=8, Align:=2,
LockFirstColumn:=True, DateFormat:=255, RowHeight:=1, AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True,
NewFieldName:="Baseline Finish", Title:="BL Finish", Width:=8, Align:=2,
LockFirstColumn:=True, DateFormat:=255, RowHeight:=1, AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True,
NewFieldName:="Start", Title:="", Width:=8, Align:=2, LockFirstColumn:=True,
DateFormat:=255, RowHeight:=1, AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True,
NewFieldName:="Finish", Title:="", Width:=8, Align:=2, LockFirstColumn:=True,
DateFormat:=255, RowHeight:=1, AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True,
NewFieldName:="Start2", Title:="EAC Start", Width:=0, Align:=2,
LockFirstColumn:=True, DateFormat:=255, RowHeight:=1, AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True,
NewFieldName:="Finish2", Title:="EAC Finish", Width:=0, Align:=2,
LockFirstColumn:=True, DateFormat:=255, RowHeight:=1, AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True,
NewFieldName:="Actual Finish", Title:="", Width:=0, Align:=2,
LockFirstColumn:=True, DateFormat:=255, RowHeight:=1, AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True,
NewFieldName:="Text9", Title:="System", Width:=0, Align:=0,
LockFirstColumn:=True, DateFormat:=255, RowHeight:=1, AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True,
NewFieldName:="Text1", Title:="Customer", Width:=0, Align:=0,
LockFirstColumn:=True, DateFormat:=255, RowHeight:=1, AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True,
NewFieldName:="Text8", Title:="AC", Width:=0, Align:=2,
LockFirstColumn:=True, DateFormat:=255, RowHeight:=1, AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True,
NewFieldName:="Notes", Title:="", Width:=0, Align:=2, LockFirstColumn:=True,
DateFormat:=255, RowHeight:=1, AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True,
NewFieldName:="Text3", Title:="USG MY / 119", Width:=0, Align:=2,
LockFirstColumn:=True, DateFormat:=255, RowHeight:=1, AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True,
NewFieldName:="Text6", Title:="Blk 5.4/FRL", Width:=0, Align:=2,
LockFirstColumn:=True, DateFormat:=255, RowHeight:=1, AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True,
NewFieldName:="Text7", Title:="SFI Ph1/Ph2", Width:=0, Align:=2,
LockFirstColumn:=True, DateFormat:=255, RowHeight:=1, AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True,
NewFieldName:="Text10", Title:="New ECD", Width:=0, Align:=2,
LockFirstColumn:=True, DateFormat:=255, RowHeight:=1, AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True,
NewFieldName:="Text12", Title:="WBS", Width:=0, Align:=2,
LockFirstColumn:=True, DateFormat:=255, RowHeight:=1, AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True,
NewFieldName:="Text13", Title:="IMP", Width:=0, Align:=2,
LockFirstColumn:=True, DateFormat:=255, RowHeight:=1, AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True,
NewFieldName:="Text14", Title:="IMP No.", Width:=0, Align:=2,
LockFirstColumn:=True, DateFormat:=255, RowHeight:=1, AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True,
NewFieldName:="Predecessors", Title:="", Width:=0, Align:=2,
LockFirstColumn:=True, DateFormat:=255, RowHeight:=1, AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True,
NewFieldName:="Successors", Title:="", Width:=0, Align:=2,
LockFirstColumn:=True, DateFormat:=255, RowHeight:=1, AlignTitle:=1
TableApply Name:="Walts table text1"
SelectTaskColumn Column:="Text6"
ColumnDelete
' code to set color for late, late start and 14 day look ahead
'

SelectTaskColumn Column:="Text2"
TableEdit Name:="Walts table text1", TaskTable:=True, NewName:="",
FieldName:="", NewFieldName:="Text5", Title:="", Width:=10, Align:=2,
ShowInMenu:=False, LockFirstColumn:=True, DateFormat:=255, RowHeight:=1,
ColumnPosition:=6, AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True, NewName:="",
FieldName:="", NewFieldName:="Text6", Title:="", Width:=10, Align:=2,
ShowInMenu:=False, LockFirstColumn:=True, DateFormat:=255, RowHeight:=1,
ColumnPosition:=6, AlignTitle:=1
TableEdit Name:="Walts table text1", TaskTable:=True, NewName:="",
FieldName:="", NewFieldName:="Text20", Title:="", Width:=5, Align:=2,
ShowInMenu:=False, LockFirstColumn:=True, DateFormat:=255, RowHeight:=1,
ColumnPosition:=1, AlignTitle:=1
TableApply Name:="Walts table text1"
Entered_Date = InputBox("Enter Late Date")
EditGoTo ID:=1
SelectTaskField Row:=0, Column:="Text5"
Late_Date = Entered_Date
Look_Ahead_Date = Late_Date + 14
'Debug.Print Entered_Date, Look_Ahead_Date, Late_Date
Do
SelectTaskField Row:=0, Column:="Text5"
SearchString = ActiveCell ' put contents of text5 into searchstring
SearchChar = "3" ' looking for a 3 in the text5 position
MyPos = InStr(1, SearchString, SearchChar, 1)
If MyPos > 0 Then
sw3 = 1 ' found a 3 in string
'Debug.Print "3 in the text5 position"
Else
sw3 = 0 ' no 3 found
'Debug.Print "No 3 in the text5 position"
End If

' this code checks for 14 day look ahead and colors accordingly

If sw3 = 1 Then
SelectTaskField Row:=0, Column:="Text6"
If ActiveCell = "5.4" Then ' First check for Blk 5.4 record
SelectTaskField Row:=0, Column:="% Complete"
If ActiveCell < "100%" Then ' Chk to see if task completed
SelectTaskField Row:=0, Column:="Text1"
If ActiveCell = "Block 5.4" Or ActiveCell = "RAAF 5.4"
Then
SelectTaskField Row:=0, Column:="Baseline Start"
If ActiveCell <= Entered_Date + 14 Then
SelectTaskField Row:=0, Column:="Text20"
SetTaskField Field:="Text20", Value:="LA"
SelectRow Row:=0, Extend:=True
Font Bold:=True, Color:=8
End If
End If
End If
End If
End If

' this code checks for late starts
If sw3 = 1 Then
SelectTaskField Row:=0, Column:="Text6"
If ActiveCell = "5.4" Then ' First check for Blk 5.4 record
SelectTaskField Row:=0, Column:="% Complete"
If ActiveCell = "0%" Then ' Chk to see if task completed
SelectTaskField Row:=0, Column:="Text1"
If ActiveCell = "Block 5.4" Or ActiveCell = "RAAF 5.4"
Then
SelectTaskField Row:=0, Column:="Baseline Start"
If ActiveCell <= Entered_Date Then
SelectTaskField Row:=0, Column:="Text20"
SetTaskField Field:="Text20", Value:="LS"
SelectRow Row:=0, Extend:=True
Font Bold:=True, Color:=5
End If
End If
End If
End If
End If

If sw3 = 1 Then
SelectTaskField Row:=0, Column:="Text6"
If ActiveCell = "5.4" Then ' First check for Blk 5.4 record
SelectTaskField Row:=0, Column:="% Complete"
If ActiveCell < "100%" Then ' Chk to see if task completed
SelectTaskField Row:=0, Column:="Text1"
If ActiveCell = "Block 5.4" Or ActiveCell = "RAAF 5.4"
Then
SelectTaskField Row:=0, Column:="Baseline Finish"
If ActiveCell <= Entered_Date Then
SelectTaskField Row:=0, Column:="Text20"
SetTaskField Field:="Text20", Value:="L"
SelectRow Row:=0, Extend:=True
Font Bold:=True, Color:=6
End If
End If
End If
End If
End If
' GoTo Skip_One bypass for test
' the following code turn 100% to green
If sw3 = 1 Then
SelectTaskField Row:=0, Column:="Text6"
If ActiveCell = "5.4" Then ' First check for Blk 5.4 record
SelectTaskField Row:=0, Column:="% Complete"
If ActiveCell = "100%" Then 'Chk to see if task completed
SelectTaskField Row:=0, Column:="Text1"
If ActiveCell = "Block 5.4" Or ActiveCell = "RAAF 5.4"
Then
SelectTaskField Row:=0, Column:="Baseline Start"
SelectTaskField Row:=0, Column:="Text20"
SetTaskField Field:="Text20", Value:="XX"
SelectRow Row:=0, Extend:=True
Font Bold:=True, Color:=9

End If
End If
End If
End If
Skip_One:
SelectRow Row:=1, RowRelative:=True
quit_sw = quit_sw + 1
Loop Until quit_sw >= 8292
'Debug.Print quit_sw, ActiveCell, Entered_Date
'FilterApply Name:="Blk 5.4 60 Day Look Ahead"
FilePageSetupHeader Text:="&""Arial"" &B &20 C-130J Integrated Master
Schedule - USG & RAAF Blk 5.4 "
FilePageSetupPage PercentScale:=65, PaperSize:=pjPaperLetter
FilePageSetupMargins Top:=0.6, Bottom:=0.6
FilePrint FromDate:="4/1/05 8:00 AM", ToDate:="4/1/06 5:00 PM",
Preview:=True

End Sub

 

[JackD]

Where to start?

1) It seems that the mountain of table formatting code is unnecessary. This
stuff is also slow. Predefine the table and a view and apply it if you want
it to show up a certain way. It seems at a cursory glance that ALL of it is
not necessary at all. Why is it there?

2) Selecting tasks in the foreground (the way you are doing it - based on
specific cells) is very slow as well. Use the tasks collection instead.
See the modified code below. I've only just done a portion of it. You should
read this article:

http://zo-d.com/blog/archives/programming/working_with_the_tasks_collection.html

and change all of your code so you are not selecting cells!

--
-Jack ... For Microsoft Project information and macro examples visit
http://masamiki.com/project
or http://zo-d.com/blog/index.html


..

Here is the code. Thanks in advance for your help.

Sub USG_RAAF_Late()
dim t as task
dim ts as tasks
dim sw3 as Boolean

Entered_Date = InputBox("Enter Late Date")
Late_Date = Entered_Date
Look_Ahead_Date = Late_Date + 14
set ts = activeproject.tasks
For each t in ts 'THIS IS THE KEY TO LOOPING THROUGH ALL TASKS
if not t is nothing then
'SelectTaskField Row:=0, Column:="Text5" DO NOT DO THIS!!
SearchChar = "3" ' looking for a 3 in the text5 position
if InStr(1, t.Text5, SearchChar, 1) <> 0 then 'REFER DIRECTLY TO
THE TEXT5 FIELD
sw3 = True ' found a 3 in string 'USE A BOOLEAN VARIABLE
Else
sw3 = False ' no 3 found
End If

' this code checks for 14 day look ahead and colors accordingly
If sw3 Then
If t.text6 = "5.4" then "AGAIN REFER TO THE TEXT6 FIELD DIRECTLY
if t.percentcomplete < 100 then
if t.text1 = "Block 5.4" Or t.text1 = "RAAF 5.4" Then
if t.baseline start <= Entered_Date + 14 Then
t.text20 = "LA" 'SET THE VALUE OF TEXT20
DIRECTLY
'not going to bother formatting the text - use a highlight or marked filter
to do this. Setting the text color individually is a poor practice.
etc. etc etc

 

[Walter L. skinner]

That was a great help. Things are much faster and I can limit the tasks to
check.

Thanks again.