Setting OnAction Property Fails

[Josh Sale]

I have an add-in that works just fine when there is a single instance of it
running on a particular machine.

However, if two copies get launched at essentially the same time on the same
machine, I get the following error:

Method 'OnAction' of object '_CommandBarButton' failed.

The runtime error number is 80004005. The failing line of code looks like
this:

Dim cmdButton As CommandBarButton

cmdButton.OnAction = "SomeMacroName"

Again, the assignment statement and the resulting toolbar button work just
fine when a single instance of Excel and the add-in is running ... the error
only happens when two copies are started at the same time.

Any thoughts?

Do multiple copies of Excel share something that's causing this problem?

BTW, am running XL2003.

TIA,

josh

 

[Jim Thomlinson]

My question is how are you getting two instances of the addin running on one
machine concurrently? Solve that and the problems go away... Is the addin
installed through code?

 

[Josh Sale]

Jim,

We're actually running multiple instances of Excel and the add-in
intentionally. We have a non-Excel process runs on a server and that reads
messages off of a queue. Depending on the content of each message this
process spawns off a copy of Excel with command line arguments that are
based on the message content.

So we want to make this work.

The add-in isn't added by code.

josh

 

[Jim Thomlinson]

Multiple instances of excel running should not (to the best of my knowledge)
cause any difficulty. Each one resides in it's own world and does not
interact with other instances. In any one instance of Excel do you have the
addin running more than once (I might be a little slow on the uptake but I
could not determine that from your post)?

 

[Josh Sale]

No ... a single copy of the add-in in each instance of Excel.

At startup, the add-in creates a few toolbars and puts a number of buttons
on each one. So there is a period where maybe 20 or 30 toolbar buttons,
shortcut menu's, etc have their OnAction property set. And this is where it
blows it brains out.

josh

 

[Jim Thomlinson]

It sound like you are creating and destroying the toolbar at the workbook
open events. I assume you are destroying the toolbar when you are done.
Instead of using this even try using the AddinInstall event something like
this... This way your toolbar does not need to be loaded each time and
perhaps your onaction will not have the conflict.

Private Sub Workbook_AddinInstall()
Call CreateMyToolbar
End Sub

Private Sub Workbook_AddinUninstall()
Call DestroyMyToolbar
End Sub

 

[Josh Sale]

Correct, I create the commandbars in the add-in's Workbook_Open event
handler and delete them in its Workbook_BeforeClose handler. The new
commandbars are created as temporary (4th argument to .Add is True) so that
they disappear in the event the code doesn't shutdown normally. I do this
because some of our users have multiple shortcuts that let them launch Excel
with or without this add-in installed. If they launch without the add-in I
don't want them to ever see my commandbars.

So a solution that requires that the commandbars be permanent doesn't sound
very good to me.

I'm a little fuzzy when the AddinInstall/AddinUninstall events are raised as
opposed to Workbook_Open/Workbook_BeforeClose.

josh

 

[Jim Thomlinson]

The install/unistall events are raised when the Tools-> Addins -> check or
uncheck is done. In this way you are not creating the toolbar every time
excel is opened. I was wondering if you might have a conflict if Two copies
of excel are opened almost simultaneously and the xla file is by the other
instance to create the toolbars. Like I said I don't have trouble with
multiple instances so this is a bit of a shot in the dark.

 

[Josh Sale]

Thanks for the suggestion.

Here's our latest theory ... one of my colleagues noticed that this problem
seemed to be related to running these processes on the server in a Remote
Desktop session. Last night we ran over 1,000 messages through this
mechanism from the server's local console with no failures.

We're in the process of retesting this theory now.

josh

 

[Jim Thomlinson]

I love a good theory...

 

[Tom Ogilvy]

why do you need custom toolbars if no one is there to use them?

 

[Josh Sale]

Well that is the million dollar question!

Clearly in general they aren't needed.

Unfortunately the add-in contains code that depending on what the user is
doing, makes certain comandbars visible or invisible as appropriate. The
code which does this assumes that these commandbars exist and will abort if
they don't and this code could be stumbled into by the batch activity.

I've created an experimental version of the code that creates all of the
commandbars but skips the creation of all of the commandbuttons on those
bars when operating in batch mode and that seems to work around the
immediate problem. But then we run into a different not obviously related
problem that I'm still investigating.

Tom, you seem to know everything about Excel. Have you ever heard of this
kind of problem? Any thoughts about it?

Another theory I had was that there was some kind of contention over the
user's .xlb file. I don't know if the .xlb file is updated for temporary
commandbars or not. I asked for another test to be run where each process
ran under its own Windows ID (and would thus have their own .xlb file) and
was told that the problem still occurred ... but I'm not sure I trust that
test result yet.

josh

 

[Tom Ogilvy]

I would never claim to know everything and that has been proven many times


I really had a similar question when I was reading this. How do changes to
commandbars in separate instances of excel in the same user id affect the
xlb file. I suspect the file isn't updated until the workbook is closed or
excel is exited, but I don't actually know.

Under the above impression, I wouldn't see temp toolbars updating the file.
I guess a way to test would be to create some temp toolbars, then crash
excel (maybe a permanent one also). I suspect when reopened, they would be
gone.

 

[Josh Sale]

I deleted my .xlb file, started Excel with my add-in and then crashed Excel
and as you suggested, I still had no .xlb file. However, if I close Excel
normally, I get a new .xlb file.

There is no obvious indication of an existing .xlb file being updated at
Excel/add-in startup (which might support the idea of .xlb contention).
That is, the timestamp doesn't change until a normal Excel close.

So if both instances are running under the same Windows ID and process 1
starts up, reads the .xlb file, process 2 starts up, reads the .xlb file,
process 1 wraps up and updates the .xlb file and then process 2 wraps up and
over-writes the .xlb file does this explain my abort? It doesn't seem like
it. All of our aborts seem to occur during initialization which doesn't
seem like a point of .xlb contention.

josh