お道具箱を作ってみよう

前回記事を更新したときは夏真っ盛りだと思ってたらいつの間にか冬ですねぇ。。
うっかりするともう春が・・こんちくわ壁|ω・)ﾉ

　
さて本日の記事は、エクセル作業で使えるツールを右クリックで呼び出せるようにしよう～なおハナシです。

例えばこんな感じのやつですね

f:id:FukuCyndiP:20210131012046g:plain:w450

でわいきます￣▽￣)ﾉ

Contents

お道具箱を作ってみよう

なぜこんなことをやろうと思ったのか・・

さて日々のエクセル作業で、ちょっとめんどくさいお馴染みの作業に遭遇しちゃったら、

　うんうん、この操作がめんどくさいからこの間コード書いたんだよねぇ～
　　　ﾄｺﾄｺ((((*´・ω・)

　　　Alt+F11 ぃ～
　　　目的のコード探してぇ～
　　　F5　　えいっ!

　　　ハイ！できた～

ってやることって、ないですか？
私はよくあります。

ところが、ある日気が付いてしまったのです。。

いちいち VBE起動して、目的のコード探して、実行する操作がたるい・・・
すぐ見つからんときもあるし・・・

すると誰かが耳元で囁きます

　　"めんどくさい" は効率化の母である (詠み人知らず)　

・・・思いつきました

セルを右クリックしたときに出るアレにマクロを登録できたら、呼び出し楽ぢゃん！！

＜セルを右クリックすると出てくるアレ＞

f:id:FukuCyndiP:20210131012816p:plain

こいつにマクロを登録するのだ!!

コンテキストメニューにマクロを登録する

エクセルVBAエキスパートスタンダードの教科書(古い方ね)に書いてあるのですが、右クリで出てくるアレ(別名コンテキストメニューというそうです)は、CommandBarsコレクションの "Cells" アイテムとして取得することができます。で、セル右クリのアレにマクロを登録するには以下のコマンドを使います

Application.CommandBars("Cell").Controls.Add([Type], [Id], [Parameter], [Before], [Temporary])

Addメソッドの各引数の内容は以下の通り (Microsoft公式)

名前	データ型	説明
Type	Variant	指定したコマンドバーに追加するコントロールの種類を指定します。使用できる定数は、MsoControl クラスの msoControlButton、msoControlEdit、msoControlDropdown、msoControlComboBox、msoControlPopup のいずれかです。
Id	Variant	組み込みのコントロールを表す整数を指定します。この引数を 1 に設定するか省略すると、指定した種類の空白のカスタムコントロールがコマンドバーに追加されます。
Parameter	Variant	組み込みのコントロールの場合、この引数はコンテナーアプリケーションでコマンドを実行するときに使用されます。カスタムコントロールの場合、この引数を使用して、Visual Basic のプロシージャに情報を渡したり、Tag プロパティの 2 番目の値のようなコントロールの情報を格納することができます。
Before	Variant	コマンドバーにおける新しいコントロールの位置を表す数字を指定します。新しいコントロールは、指定した位置にあるコントロールの直前に挿入されます。この引数を省略すると、コントロールは指定したコマンドバーの末尾に追加されます。
Temporary	Variant	True を指定すると、新しいコントロールが一時的なものになります。コンテナーアプリケーションが閉じられると、コントロールは自動的に削除されます。既定値は False です。

(全ての引数が省略可能)

で、細かいことはいいからどう書くの？
という声が聞こえてきそうですですので、下にコード例を記述してみました

これは右クリコマンドバーの一番上に "Greeting" というコードを実行するコマンド "こんにちわ" を追加するコードです

'-----------------------------------------------------------------
Sub コマンドバーにマクロを追加()
  With Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, before:=1)
    .Caption = "こんにちわ" 　　　'メニューに表示する文字列
    .OnAction = "Greeting"　 '実行するプロシージャ名
    .Tag = "追加コマンド"　　 'コマンドの説明
  End With
End Sub

'-----------------------------------------------------------------
Sub Greeting()
  Call MsgBox("こんちくわ～")
End Sub

これを実行すると、

f:id:FukuCyndiP:20210131012952p:plain

このように右クリメニューに "こんにちわ" が追加されました。

コマンドを複数追加したい場合は、同様のコードを並べます

'----------------------------------------------------------------
Sub コマンドバーにマクロを追加()
  With Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, before:=1)
    .Caption = "こんにちわ"
    .OnAction = "Greeting"
    .Tag = "追加コマンド"
  End With
      
  With Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, before:=1)
    .Caption = "さようなら"
    .OnAction = "ByeBye"
    .Tag = "追加コマンド"
  End With
End Sub
'----------------------------------------------------------------

Sub Greeting()
  Call MsgBox("こんちくわ～")
End Sub

Sub ByeBye()
  Call MsgBox("さようなら")
End Sub

実行すると、

f:id:FukuCyndiP:20210131013039p:plain

めでたく、このように2つのコマンドが追加されました。

お道具箱を作る

さて、こうして皆さんは右クリコマンドバーに好きなコードを実行するコマンドを追加する術をゲットしました。けれども、調子に乗ってどんどん追加していくと右クリコマンドバーが新しいコマンドで埋め尽くされてしまって、もとからあるコマンドが使いにくくなってしまいそうです。

Application.CommandBars("Cell").Controls.Add()の Before引数でリストの位置をコントロールしたり、BeginGroupプロパティ(as boolean)で仕切り線を付けたりできたりはするのですが、右クリコマンドバーがビローンと長くなってしまうのはイケてないですよね。

そこで、コマンドバーに階層を作って自作コマンドはそこにまとめてしまえば、より使いやすくなります。

というわけで気になるコードは、以下のように記述します

Sub お道具箱にまとめる()
     
    With Application.CommandBars("Cell").Controls.Add(Type:=msoControlPopup, before:=1)
        .Caption = "お道具箱" 　　'ポップアップタイトルに"お道具箱"を指定

        With .Controls.Add(Type:=msoControlButton, before:=1)
            .Caption = "こんにちわ"
            .OnAction = "Greeting"
            .Tag = "追加コマンド"
        End With
    
        With .Controls.Add(Type:=msoControlButton, before:=1)
            .Caption = "さようなら"
            .OnAction = "ByeBye"
            .Tag = "追加コマンド"
        End With

    End With
    
End Sub

Application.CommandBars("Cell").Controls.Add()の引数 TypeにmsoControlPopup を指定して、.Caption＝”お道具箱" と命名します。
さらに、その "お道具箱" に2つのコマンドを Control.Add() することで、階層構造が作成できます。

このコードを実行すると、

f:id:FukuCyndiP:20210131013938g:plain

お道具箱に2つのコマンドがまとめられましたね～ｗ。

まとめ

今回の記事ではワークブック上のセル右クリで出現するアレに好きなマクロを登録する方法と、さらにそれを呼び出しやすくまとめる方法を記載しました。最初にも書きましたが、日々のエクセル作業では地味にめんどくさい操作がちょいちょい発生するのですが、積み重なるとこれが結構ボディーブローのように効いてくるんですよね。

だからこそ、ちっちゃい作業でもコードにしてココロの救済を図るわけですけれども、それでも積み重なるとコード呼び出す作業でも、めんどくさくなってしまうものです(当社比)。

マクロ実行ボタンをワークシートに作っておくというのもひとつの解決策ではありますが、毎回新しいワークブックにボタンを複数作るというのも結構手間だったりしますよね。その点、右クリで出現するアレはエクセルに組み込まれているので一度登録してしまえば何個でも見やすい形でコマンドを呼び出すことができるのです。そういう意味では右クリ呼び出しサイコーなのです。

　

ん？

さて、ここで聡明なあなたは気が付いたかもしれません。

　　一度登録してしまえば？？

　　ひょっとして、新しくエクセルを起動する度に、右クリコマンドバー登録のコードを実行しなきゃってことか？

ハイ。

何度も書きました。　呼び出す作業がめんどくさいと。。

たとえエクセル起動後の1回であっても、それを毎日何度も繰り返しているとめんどくさくなってくるんですよねぇ。

怠惰な生き物である筆者はそこすら何とかしたいと考え、ある解決策に到達したのでした。次回の記事では右クリコマンドバー呼び出しの真髄ともいえる(当社比)その秘密を恥ずかしげもなく大公開したいと思います。

でわまた～￣▽￣)ﾉｼ

因みに・・・

上に記述しました2つのマクロを登録するコードを見ると、下のようにWith ステートメントで括りたくなりませんか？
でも残念ながらこのコードを実行しても結果的に "さようなら" のコマンドしか登録されませんご注意ください。

Sub コマンドバーにマクロを追加()
  With Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, before:=1)
    .Caption = "こんにちわ"
    .OnAction = "Greeting"
    .Tag = "追加コマンド"

    .Caption = "さようなら"
    .OnAction = "ByeBye"
    .Tag = "追加コマンド"
  End With
End Sub

これは、

　　Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, before:=1)

という記述で'追加された1つのコマンドに対して、2つのコマンドを順に登録する'という操作になるためです。
コードをステップ実行すると、メニューバーの一番上に最初に "こんにちわ" が登録されて、そのあとに "さようなら" が上書きされることがわかります。気になった方は一度試してみてくださいね～。　