エクセルVBA アドイン メニューの作成方法

質問

OS：windowsXP　HomeEdition
ソフト：Excel2003

アドインを作成し配布したいのですが、xla形式で保存しエクセル上でアドインさせ、
作成したメニューからテスト起動させてみると、アドインの元のファイル（拡張子xls）が
展開してしまいます。展開させずにアドインプロシージャを起動させたいのですが
何が原因か調べてもわかりませんでした。知っている方、ご助言御願い致します。

以下に、私の作成したマクロを記述します。
マクロ解説HPで勉強しつつ作成致しました。

～～～～～～～～～～～～～～～～～～～～～～～～～
＜ThisWorkBookへの記述＞

Dim mySubMenu As CommandBarControl

Sub MakeSubMenu()
Dim myMenu As CommandBar
Dim cmdSubMenu As CommandBarControl
'すでにサブメニューがあれば削除する
DelSubMenu

Set myMenu = Application.CommandBars("worksheet Menu Bar")
Set mySubMenu = myMenu.Controls.Add(Type:=msoControlPopup)
mySubMenu.Caption = "メニュー"

Set cmdSubMenu = myMenu.Controls("メニュー").Controls.Add(Type:=msoControlButton)
cmdSubMenu.Caption = "コマンド1"
'サブメニューでコマンド1を選択したとき cmd1() を呼び出す
cmdSubMenu.OnAction = "cmd1"

Set cmdSubMenu = myMenu.Controls("メニュー").Controls.Add(Type:=msoControlButton)
cmdSubMenu.Caption = "コマンド2"
'サブメニューでコマンド2を選択したとき cmd2() を呼び出す
cmdSubMenu.OnAction = "cmd2"
End Sub

Sub DelSubMenu()
'メニューバーのサブメニューを削除する
On Error Resume Next
mySubMenu.Delete
End Sub

＜標準モジュールへの記述＞

Private Sub cmd1()
MsgBox "コマンド1を選択しました"
End Sub

Private Sub cmd2()
MsgBox "コマンド2を選択しました"
End Sub
～～～～～～～～～～～～～～～～～～～～～～～

尚、マクロは簡略化し、のせております。
よろしく御願い致します。

回答No.2

Wendy02 · ベストアンサー

>マクロは簡略化し、のせております。
とはいっても、出来る限り、イベントのひとつぐらいは省略しないでほしいです。コードが読めなくなります。

問題点は、片方のプロシーシャを、ThisWorkbook モジュールに入れておいて、
標準モジュールのPrivate キーワードを付けたプロシージャは、ThisWorkbookから、呼び出せませんね。

これを、同じThisWorkbookに入れるなら、そのままでも可能です。

>Private Sub cmd1()
>　　　MsgBox "コマンド1を選択しました"
>End Sub

>Dim mySubMenu As CommandBarControl
それと、これは、モジュール・スコープの変数にする必要はありませんね。

DelSubMenu の代わりに

Private Sub Workbook_Open()
Dim myMenu As CommandBar
Dim cmdSubMenu As CommandBarControl
　　'すでにサブメニューがあれば削除する
　　''DelSubMenu　'←ここは不要
　　Set myMenu = Application.CommandBars("Worksheet Menu Bar")
　　On Error Resume Next
　　myMenu.Controls("メニュー").Delete
　　On Error GoTo 0
　　Set mySubMenu = myMenu.Controls.Add(Type:=msoControlPopup, Temporary:=True)
　　mySubMenu.Caption = "メニュー"

ユーザーメニューを加える時は、Temporary:=True は、必ず入れてください。
VBAが失敗しても、再起動すれば、ユーザー・メニューは消えています。

すでにサブメニューがあれば削除するということで、サブルーチンで、
　mySubMenu.Delete
を元のコードから供給するのでは、ほとんど意味がありません。しかし、これは、コーディング・マナーというのか、あくまでも、削除するのものは自分のマクロのみにします。Reset を使うものもありますが、数多くなると、Reset は使いません。

>マクロ解説HPで勉強しつつ作成致しました。
本当にVBAを勉強したいのでしたら、Webサイトでは、よろしくありません。体系的な知識を身につけにくく、知識が断片的になりやすいのと、いろんな寄せ集めて、統合化しにくいのです。コーディング・スタイルというものも、人それぞれ、ないようでいてありますから、そういうものを混ぜてしまうと、読みにくくなります。

お礼

詳しくご回答頂きありがとうございます。
解決できました。

回答No.1

kybo

展開してしまうのは、以下の部分のOnActionで「xls」ファイルにある「cmd1」をマクロ登録されてしまっている為、だと考えられます。

cmdSubMenu.OnAction = "cmd1"

つまり、「xls」が閉じられた状態で、開いているファイルのどこにも同じプロシージャ名が存在しない状態で、メニューが作成されれば、「xla」ファイルに対してマクロ登録されるはずです。
なので、開いている全てのファイルの中に、同じプロシージャ名が存在しない状態（無論、個人用マクロブックにも存在しない状態）にして、「MakeSubMenu」を実行すればいいです。

まぁ、以下の様にファイル名も指定して登録してもいいかもしれません。

cmdSubMenu.OnAction = ThisWorkbook.Name & "!cmd1"

お礼

ありがとうございます。
解決できました