Application.CommmandBars.Add performance problem

[Roy H. Berger]

I am seeing a rather odd problem when adding a custom toolbar from an
add-in. The add-in potentially adds three custom toolbars, but the
performance problem only seems to occur when the first toolbar is
added. And it only seems to be a problem when you add/delete
something from the Application.CommmandBars collection for the first
time. I am running Office 2003 on WinXP Pro.

For instance, I had the following code:

On Error Resume Next
Application.CommandBars("My Toolbar").Delete
On Error GoTo 0

Set TM1Toolbar = Application.CommandBars.Add("My Toolbar",
msoBarTop, False, True)

Set MyToolbar_Default = TM1Toolbar
....


On the above code, the "Delete" operation took 20-30 seconds. So
then, figuring that the delete of something that potentially wasn't
there (I clean up when the work book closes) was causing some sort of
problem, I bracketed the delete code as follows:

If (Not MyToolbar_Default Is Nothing) Then
Application.CommandBars("My Toolbar").Delete
End If

So I was no longer doing an extraneous delete. This just pushed the
problem to the "Add":

Set TM1Toolbar = Application.CommandBars.Add("My Toolbar",
msoBarTop, False, True)

This line now took 20-30 seconds to run. Now this is before I even
start adding buttons to the toolbar. In fact those button add
operations run quickly as well as adding the additional toolbars. It
just seems like the first "access" to the Application.CommmandBars
collection is causing some sort of delay. Anyone ever seen this
before?

TIA

Regards,

Roy Berger
(e-mail address removed)

 

[Rob van Gelder]

I don't really know what's going on.
It sounds suspiciously like an issue I frequently encounted. A Windows XP
SP0/SP1 issue which occasionally makes delete operations on a network share
take about 30 seconds.

You could run tools from http://www.sysinternals.com (regmon / filemon) to
monitor Excel.EXE. It may give clues as to what is causing the delays.

 

[Roy H. Berger]

Thanks for your reply. I will attempt to use the tools you suggest,
but I'm skeptical that it is a nework share issue, since this all
happens locally on my workstation, and it is really just the XLA
loading and initializing itself, not really doing any file deletions.
I am running WinXP SP1, but will also check the Micro$oft site for
issues/fixes on this. Thanks again.

Roy