S
SunGard
First of all, my question: Is this really supposed to be this slow?
Now to the story:
In order to create a custom field editor form, I need to get all lookup
table entries for each field. There are approximately 64 outline codes (for
each oc) in the loop below. The loop below takes more than 2.5 seconds (after
I've stripped everything out of it):
' --- Code Begin
For Each oc As Microsoft.Office.Interop.MSProject.OutlineCode In
Globals.ThisAddIn.Application.GlobalOutlineCodes
For Each ltEntry As
Microsoft.Office.Interop.MSProject.LookupTableEntry In oc.LookupTable
Next ltEntry
Next
' --- Code End
The total iterations of inner/outer loops is around 800. So, the user is
waiting for 2.5 seconds just to get the lookup table values organized. In
order to add these values into a dictionary for final use by a ComboBox, I
instantiate a "ValueDescriptionPair" using the following class which is as
simple as you can get:
' --- Code Begin
Public Class ValueDescriptionPair
Private m_Value As String
Private m_Description As String
Public ReadOnly Property Value() As String
Get
Return m_Value
End Get
End Property
Public ReadOnly Property Description() As String
Get
Return m_Description
End Get
End Property
Public Sub New(ByRef NewValue As String, ByRef NewDescription As
String)
m_Value = NewValue
m_Description = NewDescription
End Sub
Public Overrides Function ToString() As String
Return m_Description
End Function
End Class
' --- Code End
So, including the list building, the following code takes 6.96 seconds. This
is a long time for the user to wait:
' --- Code Begin
Dim vdp As ValueDescriptionPair = Nothing
' Add lookup tables into a dictionary for ease of search
For Each oc As Microsoft.Office.Interop.MSProject.OutlineCode In
Globals.ThisAddIn.Application.GlobalOutlineCodes
Try
' Hold list of items for eventual use by the combo box
Dim ItemObject(oc.LookupTable.Count - 1) As System.Object
i = 0
For Each ltEntry As
Microsoft.Office.Interop.MSProject.LookupTableEntry In oc.LookupTable
vdp = New ValueDescriptionPair(ltEntry.FullName,
ltEntry.FullName)
ItemObject(i) = vdp
i = i + 1
Next ltEntry
Catch ex As Exception
MsgBox("getProjectServerData: Error in populating
dictionary: " & ex.Message())
End Try
Next
' --- Code End
The above code doesn't even include adding the list to the dictionary. It is
almost unacceptable to the Users for this wait. The final code takes more
than 9 seconds to build the list, and popup the form. on second press, I use
the dictionary and it takes 1 to 2 seconds as I've created my own cache.
My question is: Is this really supposed to be this slow?
Just an empty loop is taking 2.5 seconds which is a bit baffling to me.
Instantiating a simple (class) structure for each entry adds more than 4.5
seconds.
Please give me advice - maybe I am doing something in VBA inefficiently?
Thank you!
Now to the story:
In order to create a custom field editor form, I need to get all lookup
table entries for each field. There are approximately 64 outline codes (for
each oc) in the loop below. The loop below takes more than 2.5 seconds (after
I've stripped everything out of it):
' --- Code Begin
For Each oc As Microsoft.Office.Interop.MSProject.OutlineCode In
Globals.ThisAddIn.Application.GlobalOutlineCodes
For Each ltEntry As
Microsoft.Office.Interop.MSProject.LookupTableEntry In oc.LookupTable
Next ltEntry
Next
' --- Code End
The total iterations of inner/outer loops is around 800. So, the user is
waiting for 2.5 seconds just to get the lookup table values organized. In
order to add these values into a dictionary for final use by a ComboBox, I
instantiate a "ValueDescriptionPair" using the following class which is as
simple as you can get:
' --- Code Begin
Public Class ValueDescriptionPair
Private m_Value As String
Private m_Description As String
Public ReadOnly Property Value() As String
Get
Return m_Value
End Get
End Property
Public ReadOnly Property Description() As String
Get
Return m_Description
End Get
End Property
Public Sub New(ByRef NewValue As String, ByRef NewDescription As
String)
m_Value = NewValue
m_Description = NewDescription
End Sub
Public Overrides Function ToString() As String
Return m_Description
End Function
End Class
' --- Code End
So, including the list building, the following code takes 6.96 seconds. This
is a long time for the user to wait:
' --- Code Begin
Dim vdp As ValueDescriptionPair = Nothing
' Add lookup tables into a dictionary for ease of search
For Each oc As Microsoft.Office.Interop.MSProject.OutlineCode In
Globals.ThisAddIn.Application.GlobalOutlineCodes
Try
' Hold list of items for eventual use by the combo box
Dim ItemObject(oc.LookupTable.Count - 1) As System.Object
i = 0
For Each ltEntry As
Microsoft.Office.Interop.MSProject.LookupTableEntry In oc.LookupTable
vdp = New ValueDescriptionPair(ltEntry.FullName,
ltEntry.FullName)
ItemObject(i) = vdp
i = i + 1
Next ltEntry
Catch ex As Exception
MsgBox("getProjectServerData: Error in populating
dictionary: " & ex.Message())
End Try
Next
' --- Code End
The above code doesn't even include adding the list to the dictionary. It is
almost unacceptable to the Users for this wait. The final code takes more
than 9 seconds to build the list, and popup the form. on second press, I use
the dictionary and it takes 1 to 2 seconds as I've created my own cache.
My question is: Is this really supposed to be this slow?
Just an empty loop is taking 2.5 seconds which is a bit baffling to me.
Instantiating a simple (class) structure for each entry adds more than 4.5
seconds.
Please give me advice - maybe I am doing something in VBA inefficiently?
Thank you!