メニュー登録のマクロの書き方

質問

エクセルを開いた時のツールバーで、
「ファイル」を選択すると「新規作成」などのメニューが出ますよね。
その中に「印刷範囲」というメニューがあり、
これにカーソルを合わせると更に右に「印刷範囲の設定」「印刷範囲のクリア」というメニューが出てきます。
この、２階層持つメニューをマクロで作成したいのですが、可能でしょうか。
１階層だけであれば、

Set menu1 = Application.CommandBars("worksheet menu bar"). _
Controls.Add(Type:=msoControlPopup, Temporary:=True)
menu1.Caption = "ツールバーに表示させるメニュー名"

.Controls.Add Type:=msoControlButton
With .Controls(1)
.Caption = "メニュー１"
.OnAction = "メニュー１のマクロの名前？"
End With
という記述で実現できました。

回答No.2

ForestRain · ベストアンサー

こんにちは。

メニューバーに追加したコントロールに、さらにもう一つ
Type:=msoControlPopup のコントロールを追加し、
そこにコマンドバーボタンを2個配置します。

Sub Sample1()

Dim Menu1 As CommandBarControl
Dim Ctrl1 As CommandBarControl

On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("ツールバーに表示させるメニュー名").Delete
On Error GoTo 0

Set Menu1 = Application.CommandBars("Worksheet Menu Bar").Controls.Add _
(Type:=msoControlPopup, Temporary:=True)
Menu1.Caption = "ツールバーに表示させるメニュー名"

With Menu1.Controls.Add(Type:=msoControlPopup, Temporary:=True)
　.Caption = "印刷範囲"

　Set Ctrl1 = .Controls.Add(Type:=msoControlButton, Temporary:=True)
　With Ctrl1
　　.Caption = "印刷範囲の設定"
　　.OnAction = "マクロの名前1"
　End With

　Set Ctrl1 = .Controls.Add(Type:=msoControlButton, Temporary:=True)
　With Ctrl1
　　.Caption = "印刷範囲のクリア"
　　.OnAction = "マクロの名前2"
　End With
End With

Set Menu1 = Nothing
Set Ctrl1 = Nothing

End Sub

お礼

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

無事出来ました！
2階層項目を複数作りたかったので、
With Menu1～End Withまでをまるまるコピーしてすぐ後ろに貼り付けてみたのですが、
それだけであっさりできてしまい、
なんとなく釈然としないです…。
変数名とかかぶってることにならないのか、と。
(いや、すんなり進んだのだから喜ぶとこですけど)

回答No.4

Wendy02

こんにちは。
#1です。

>85年代のコードですね、と言われてしまいました（笑）。
私も書いた覚えがあるのですが、別の方だったと思います。（^^;

>今はwithとやらは使わないのでしょうか。

一般的には、VB系では、With ステートメントは、使うことを奨励しています。

#3のKenKen_SPさんもおっしゃってはいますが、いずれにしても、メニューを作るときは、掘り下げていくような形になってしまいます。そして、CommandBarControlは、一旦、オブジェクトを取った後に、必ず、付け加えるようなスタイルにします。

同じようなものをいくつも作っていき、それぞれの内容が違ってきますね。そうすると、デバッグのときに、With の部分では、オブジェクトの内容のチェックもできませんので、やりにくいのです。もちろん、見易さもあります。

お礼

>私も書いた覚えがあるのですが、別の方だったと思います。（^^;
すみません、それ私です…、なんか恥ずかしい（苦笑
その節はお世話になりました。
未だに同じコードをいじっているもので、
また古い書き方だったのかと早とちりしてしまいました(余計なこと書かなければよかったｗ＜85)。

Withに関してはなんとなく理解できたように思います。
ありがとうございました。

回答No.3

KenKen_SP

> 今はwithとやらは使わないのでしょうか。

いえ、使いますよ。ただ、今回のようなコードでは With を深くネスト
することになってしまいますので、コードが読みにくくなってしまうのです。

With によって現在キャッシュされているオブジェクトは何か？

つまり、ピリオドの前にくるオブジェクトは何かを下記のコードでは
理解しにくいですね。（インデントしてあるので多少マシですが...）

適所で Set ステートメントで参照しておいた方がコードがすっきり
してメンテナンスが容易になります。

' // With のみのコード（読みにくいコード）
Sub Sample()

　　With Application.CommandBars("worksheet menu bar"). _
　　　　 Controls.Add(Type:=msoControlPopup, Temporary:=True)
　　　　.Caption = "メニュー名"
　　　　With .Controls.Add(Type:=msoControlPopup)
　　　　　　.Caption = "サブメニュー名"
　　　　　　With .Controls.Add(Type:=msoControlButton)
　　　　　　　　.Caption = "コマンド名1"
　　　　　　　　.OnAction = "実行するマクロ名1"
　　　　　　End With
　　　　　　With .Controls.Add(Type:=msoControlButton)
　　　　　　　　.Caption = "コマンド名2"
　　　　　　　　.OnAction = "実行するマクロ名2"
　　　　　　End With
　　　　End With
　　End With

End Sub

With ステートメント自体は、機能的・効率的なコーディングには
欠かせませんが、要は「使い方」の問題になってしまいそうです。

お礼

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

よくわかりました。
Setは省略できるみたいですね。
参考にしたサイトの例文にも乗ってなかったのですが、
単にその例文が１個のWithしかつかってなかったからみたいです。
VBAに慣れない今は、とにかく以下のような長いコード（一文）が怖いので、
Controls.Add(Type:=msoControlPopup, Temporary:=True)
消してしまいたいところですが、書くようにします。

回答No.1

Wendy02

こんにちは。

たぶん、ご質問のコードではエラーが出るように思いますが、この種のマクロは、必ず、一旦変数にとってから実行するようにしたほうがよいです。With ステートメントではうまくいかないことがあります。

Sub TestSample()
Dim Menu1 As CommandBarControl
Dim MenuCntrl1 As CommandBarButton
Dim MenuCntrl2 As CommandBarButton
　　On Error Resume Next
　　'二重にメニューを作らないおまじない
　　 Application.CommandBars("Worksheet Menu Bar").Controls("ツールバーに表示させるメニュー名").Delete
　　On Error GoTo 0
　　Set Menu1 = Application.CommandBars("Worksheet Menu Bar").Controls.Add _
　　(Type:=msoControlPopup, Temporary:=True)

　　Menu1.Caption = "ツールバーに表示させるメニュー名"

　　Set MenuCntrl1 = Menu1.Controls.Add( _
　　　 Type:=msoControlButton, _
　　　 Temporary:=True)

　　With MenuCntrl1
　　　　.Caption = "メニュー1"
　　　　.OnAction = "メニュー１のマクロの名前？"
　　End With

　　Set MenuCntrl2 = Menu1.Controls.Add( _
　　　 Type:=msoControlButton, _
　　　 Temporary:=True)

　　With MenuCntrl2
　　　　.Caption = "メニュー2"
　　　　.OnAction = "メニュー2のマクロの名前？"
　　End With
　　Set MenuCntrl1 = Nothing
　　Set MenuCntrl2 = Nothing
　　Set Menu1 = Nothing
End Sub

お礼

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

今回提示したコードは、他人が昔作ったマクロの抜粋です。
以前別の箇所で、同じようにコードを抜粋して質問した時も、
85年代のコードですね、と言われてしまいました（笑）。
今はwithとやらは使わないのでしょうか。

今後は挙げていただいたコードを参考に、書き換えていこうと思います。