AddIn Name Resolution

[William Barnes]

Using Excel 2003 under Win XP Pro; all current updates installed for both
products.

I've encountered unexpected behavior with respect to name resolution between
an AddIn and a Workbook. To test Excel's behavior, I created a simple
example. I created a Workbook named MySub.xls, saved it, and then saved it
as an AddIn named MySub.xla. I then added some code to the AddIn to create a
toolbar. The entire code for both is presented. All code resides within the
respective ThisWorkbook module:

For the Workbook MySub.xls:

Public Sub MySub()
MsgBox "Workbook::MySub()"
End Sub


For the AddIn MySub.xla:

Public Sub MySub()
MsgBox "AddIn::MySub()"
End Sub

Private Sub MakeToolbar()
Dim tb As CommandBar
Dim btn As CommandBarButton

Set tb = Application.CommandBars.Add("MySub", msoBarTop)
tb.Visible = True

Set btn = tb.Controls.Add(Type:=msoControlButton)
With btn
.FaceId = 59
.OnAction = "ThisWorkbook.MySub"
.TooltipText = "MySub"
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("MySub").Delete
End Sub

Private Sub Workbook_Open()
MakeToolbar
End Sub

As you can see, both the AddIn and the Workbook have identical Subs named
MySub. However, when I click on the tollbutton in the AddIn's toolbar, it
executes not the AddIn's version but rather the Workbook's! Thus if I write
an AddIn and a user's workbook just happens to have a Sub with the same
signature my code would not execute properly. This seems like a bona fide
bug in VBA to me. Am I missing something?

 

[Jim Cone]

William,

Try changing...
.OnAction = "ThisWorkbook.MySub"
To...
.OnAction = ThisWorkbook.Name & "!MySub"

Regards,
Jim Cone
San Francisco, USA


"William Barnes" <[email protected]>
wrote in message
Using Excel 2003 under Win XP Pro; all current updates installed for both
products.

I've encountered unexpected behavior with respect to name resolution between
an AddIn and a Workbook. To test Excel's behavior, I created a simple
example. I created a Workbook named MySub.xls, saved it, and then saved it
as an AddIn named MySub.xla. I then added some code to the AddIn to create a
toolbar. The entire code for both is presented. All code resides within the
respective ThisWorkbook module:

For the Workbook MySub.xls:

Public Sub MySub()
MsgBox "Workbook::MySub()"
End Sub


For the AddIn MySub.xla:

Public Sub MySub()
MsgBox "AddIn::MySub()"
End Sub

Private Sub MakeToolbar()
Dim tb As CommandBar
Dim btn As CommandBarButton

Set tb = Application.CommandBars.Add("MySub", msoBarTop)
tb.Visible = True

Set btn = tb.Controls.Add(Type:=msoControlButton)
With btn
.FaceId = 59
.OnAction = "ThisWorkbook.MySub"
.TooltipText = "MySub"
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("MySub").Delete
End Sub

Private Sub Workbook_Open()
MakeToolbar
End Sub

As you can see, both the AddIn and the Workbook have identical Subs named
MySub. However, when I click on the tollbutton in the AddIn's toolbar, it
executes not the AddIn's version but rather the Workbook's! Thus if I write
an AddIn and a user's workbook just happens to have a Sub with the same
signature my code would not execute properly. This seems like a bona fide
bug in VBA to me. Am I missing something?

 

[William Barnes]

Thanks for your prompt response, Jim.

Your suggestion looked promising, but it didn't seem to affect things. Let
me be certain that I changed things as you intended. In the AddIn
MakeToolbar() Sub, I changed

.OnAction = "ThisWorkbook.MySub"

to

.OnAction = "MySub.xla!ThisWorkbook.MySub"

I then saved the AddIn, unloaded it, and after reloading it Excel still
executed the Workbook version. I even tried restarting Excel without
success.

 

[Jim Cone]

William,

Make the change exactly as I show. Also, to emphasize,
the quote marks should be exactly as I show.

"MySub.xla!MySub" (with the quote marks) should
also work, but if you change the add-in name then it will fail.

Jim Cone


"William Barnes" <[email protected]>
wrote in message
Thanks for your prompt response, Jim.
Your suggestion looked promising, but it didn't seem to affect things. Let
me be certain that I changed things as you intended. In the AddIn
MakeToolbar() Sub, I changed
.OnAction = "ThisWorkbook.MySub"
to
.OnAction = "MySub.xla!ThisWorkbook.MySub"
I then saved the AddIn, unloaded it, and after reloading it Excel still
executed the Workbook version. I even tried restarting Excel without
success.

 

[Tom Ogilvy]

I tested your suggestion and I get a message that the sub MySub can't be
found. This makes sense because he has the MySub macro in the ThisWorkbook
Module.

William, I think your solution is to use unique names.

instead of MySub

MyAddinName835_MySub

Whether this is a bug or not would depend on MS's design for evaluating what
macro to run. It the workbook version of William is not the active
workbook, then it runs fine. So it looks like a situation similar to where
a local variable screens out a global variabe. Because of the way the macro
is assigned, I don't think there is a way to differentiate like you can
with the variables. So using the unique name would be the answer.

 

[William Barnes]

Thanks Tom. I guess I would say that from a design point of view, if an
AddIn qualifies a call with ThisWorkbook then the name should reslove to the
AddIn's routine rather than any other open workbook, kind of like a
namespace concept. If I were Microsoft, I would build VBA to do just that.
Hope they read this.

William