ExcelVBA AddinでOnAction

ツールバーにボタンを作って、そのボタンをクリックしたら OnActionプロパティ
で指定したマクロを実行するというワークブックを作りアドイン化しました。
以下、アドインのマクロ。
ThisWorkbookに
Private Sub Workbook_Open()
' ツールバーを削除
On Error Resume Next
Application.CommandBars("MyMenu1").Delete
On Error GoTo 0

With Application.CommandBars.Add(Name:="MyMenu1")
.Visible = True
.Position = msoBarBottom
.RowIndex = 1

.Controls.Add (msoControlButton)
With .Controls.Item(1)
.Caption = "test1"
.OnAction = "a"
.FaceId = 59
End With
End With
End Sub

標準モジュールに
Public Sub a()
MsgBox "アドイン"
End Sub
としました。

これでこのアドインをExcelに登録すれば、どのファイルを開いた時でも
ツールバーのボタンをクリックすれば、アドインのマクロが実行されます。
が、上記OnActionに記述したのと同じ名前のサブルーチンが、
今アクティブになっているワークブックにあった場合には、アドイン側の
サブルーチンではなく、ワークブック側のが実行されてしまいます。

色々検索した結果、
OnActionの指定を「.OnAction = Thiswokbook.FullName & "!a"」とすればよい
という記述を目にし、その通りにしましたが効果ありませんでした。

なお、Excel2000または2003で起きました。2010では通常にアドイン側のサブルーチンが
起動しています。
取りあえず、誰もつけそうにない名前をアドイン側のサブルーチンにつけていますが、
これしか手立てはないものでしょうか？

No.3 回答者： kybo 回答日時： 2011/09/07 05:46

>５．で開いたサブルーチンには、Module1にサブルーチンa があるのみです。

>.OnAction = "XXX.a" と変更したところで、アクティブになっているワークブックの
>モジュールXXXにサブルーチンaがあれば、こっちの方を実行してしまうのは
>変わりませんでした。

XXXはオブジェクト名で変更できるので、固有のモジュール名に変更しておくのが無難です。


>アドイン側のモジュール名なり、サブルーチン名なりを工夫して衝突しないような
>名前を工夫するしかないのでしょうか？

通常はアドイン登録時のモジュールを常に参照しつづけるはずなのですがね。
何らかの原因で書き換えられていると考えられます。

この回答へのお礼

回答ありがとうございます。

実は http://fireball.loafer.jp/kes/vba/article_182.html というWebページを
見つけました。このページに従い、OnActionプロパティを使用する方法に見切りをつけ、
Clickイベントで処理する方法に切り替えました。
結果、成功です。きちんと、アドイン側のサブルーチンを実行してくれるようになりました。

プログラムはこんな感じになりました。

アドインのThisWorkbook
Private WithEvents m_Button As CommandBarButton

Private Sub m_Button_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
Call a
End Sub

Private Sub Workbook_AddinInstall()
Dim strFilename As String

' ツールバーを削除
On Error Resume Next
Application.CommandBars("MyMenu1").Delete
On Error GoTo 0

Debug.Print ThisWorkbook.Name & " AddinInstall"
With Application.CommandBars.Add(Name:="MyMenu1")
.Visible = True
.Position = msoBarBottom
.RowIndex = 1

Set m_Button = .Controls.Add(msoControlButton)
With m_Button
.Caption = "test1"
.Tag = "Test1"
.FaceId = 59
End With
End With
End Sub

Private Sub Workbook_AddinUninstall()
Debug.Print ThisWorkbook.Name & " AddinUninstall"
' ツールバーを削除
On Error Resume Next
Application.CommandBars("MyMenu1").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
Set m_Button = Application.CommandBars("MyMenu1").FindControl( _
Type:=msoControlButton, Tag:="Test1")
End Sub

標準モジュール
Sub a()
MsgBox "アドイン"
End Sub

以上

お礼日時：2011/09/07 22:03

No.2 回答者： kybo 回答日時： 2011/09/06 05:55

.OnAction = "a"

を
.OnAction = "Module1.a"

とモジュール名も記載する様にしたほうが無難です。
Module1といったありきたりものではなく、アドインのものはあらかじめ変更しておくことをお勧めします。



>５．で開いたワークブックのサブルーチン

５．で開いたワークブックにはツールバーを作成するような記述はないのでしょうか？
記述があるならそこが問題の可能性もあります。

この回答への補足

回答ありがとうございます。
５．で開いたサブルーチンには、Module1にサブルーチンa があるのみです。

.OnAction = "XXX.a" と変更したところで、アクティブになっているワークブックの
モジュールXXXにサブルーチンaがあれば、こっちの方を実行してしまうのは
変わりませんでした。でも、名前が衝突する確率はぐっと減りそうです。

アドイン側のモジュール名なり、サブルーチン名なりを工夫して衝突しないような
名前を工夫するしかないのでしょうか？

補足日時：2011/09/06 22:57

No.1 回答者： kybo 回答日時： 2011/09/04 19:10

せっかくアドインにしているのだから、Open時に毎回ツールバーを作成せずに、

アドイン登録時にツールバー作成、アドイン解除時に、ツールバー削除
の様にすれば、問題ないです。
（アドイン登録する際は、ほかに競合するマクロがない状態で行います）

Private Sub Workbook_AddinInstall()
With Application.CommandBars.Add(Name:="MyMenu1")
.Visible = True
.Position = msoBarBottom
.RowIndex = 1
.Controls.Add (msoControlButton)
With .Controls.Item(1)
.Caption = "test1"
.OnAction = "a"
.FaceId = 59
End With
End With
End Sub

Private Sub Workbook_AddinUninstall()
' ツールバーを削除
On Error Resume Next
Application.CommandBars("MyMenu1").Delete
On Error GoTo 0
End Sub

この回答への補足

回答ありがとうございます。
AddinInstallイベントって今一つどういう時に使用するのかわかってませんでした。
「ツール」→「アドイン．．．」で、チェックをOnにすると実行されるようですね。
確かにこの方法なら、アドインをOpenする度にツールバーを作成することがないので
動作が少しは軽くなりそうですが、問題は解決しませんでした。

一応、こちらのやったことを書いておきます。
１．アドインのThisWorkbookにkyboさんのマクロを書いて、保存
　　もちろん、Workbook_Openは消しました。
２．サブルーチンaを含むワークブックを開いていないか確認
３．「ツール」→「アドイン．．．」を選択し、アドインをOff、Onする
４．ツールバーのボタンをクリックし、アドインのサブルーチンが動作しているのを確認
５．サブルーチンaを含むワークブックを開く
６．再びツールバーのボタンをクリックするが、５．で開いたワークブックのサブルーチン
　　が動作した。
手順はあっているでしょうか？

なお、上記はExcel2003で行いました。

補足日時：2011/09/04 23:46