Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
AddIn Name Resolution
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
AddIn Name Resolution
William,
Try changing... .OnAction = "ThisWorkbook.MySub" To... .OnAction = ThisWorkbook.Name & "!MySub" Regards, Jim Cone San Francisco, USA "William Barnes" 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
AddIn Name Resolution
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" wrote in message ... William, Try changing... .OnAction = "ThisWorkbook.MySub" To... .OnAction = ThisWorkbook.Name & "!MySub" Regards, Jim Cone San Francisco, USA "William Barnes" 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
AddIn Name Resolution
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" 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. "Jim Cone" wrote in message ... William, Try changing... .OnAction = "ThisWorkbook.MySub" To... .OnAction = ThisWorkbook.Name & "!MySub" Regards, Jim Cone San Francisco, USA |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
AddIn Name Resolution
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. -- Regards, Tom Ogilvy "Jim Cone" wrote in message ... 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" 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. "Jim Cone" wrote in message ... William, Try changing... .OnAction = "ThisWorkbook.MySub" To... .OnAction = ThisWorkbook.Name & "!MySub" Regards, Jim Cone San Francisco, USA |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
AddIn Name Resolution
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 "Tom Ogilvy" wrote in message ... 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. -- Regards, Tom Ogilvy "Jim Cone" wrote in message ... 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" 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. "Jim Cone" wrote in message ... William, Try changing... .OnAction = "ThisWorkbook.MySub" To... .OnAction = ThisWorkbook.Name & "!MySub" Regards, Jim Cone San Francisco, USA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Screen Resolution | Excel Programming | |||
Screen Resolution | Excel Programming | |||
xlVeryHidden Resolution | Excel Programming | |||
Screen resolution | Excel Programming | |||
Remove Excel AddIn from AddIn List !! Help | Excel Programming |