個人用マクロブックを使って自分だけの右クリックメニューを作る【VBA/CommandBarControl】

Excel/VBA

2021.06.02

ちょっとした操作のマクロで頻繁に使う関数はPERSONALに保存していつでも呼べる様にしているのですが、もっと簡単に呼べないかな～と思い右クリックからのメニューを自作してみた話し。

個人用マクロブックを作る
CommandBarControlでメニューの枠を作る
メニューから呼び出す関数を作る
実際の動き

個人用マクロブックを作る

※既に作成済みの方は読み飛ばして下さい
個人用マクロブックとは、Excelが起動した時に常に開かれるマクロ群です。
なので良く使う関数等をここに入れて置けばどんなエクセルを開いても呼べちゃうので、
まだ未作成ならばこの機会に是非作成しときましょう。作業スピードがダンチです。

作り方はたったの3STEP

【開発】タブから【マクロの記録】をクリック
【マクロの保存先】を【個人用マクロブック】にしてOKをクリック。
記録終了ボタンをクリックする。これだけで側は出来ちゃいます。

【Alt+F11】でエディタを確認するとこんなのが出来ているはず↓

で、今回はここに使用頻度の高い関数群を貯め込み且つ、右クリックメニューにも追加して
ばしばし呼び出しちゃおうってお話です。
参照の追加等も特に不要なのでまるまるコピペでOK。

CommandBarControlでメニューの枠を作る

無事個人用マクロブックが出来上がったら、Modelue1に下記を追加します。

Sub Menu()

  Dim CMDParent As CommandBarControl   ' 親メニュー
  Dim CMDChild As CommandBarControl    ' 子メニュー
  ' 初期化
  Application.CommandBars("Cell").Reset
  ' 入れ子にする場合はType:=msoControlPopup
  Set CMDParent = Application.CommandBars("Cell").Controls.Add(Type:=msoControlPopup)
  ' キャプション
  CMDParent.Caption = "CMD"
  ' 区切り線を付ける
  CMDParent.BeginGroup = True
  
  ' 子①
  Set CMDChild = CMDParent.Controls.Add
  CMDChild.Caption = "選択範囲の頭に0を付ける"    ' キャプション
  CMDChild.OnAction = "Set0"                     ' 関数名
  CMDChild.FaceId = 712                          ' アイコン(省略可)
  
  ' 子②
  Set CMDChild = CMDParent.Controls.Add
  CMDChild.Caption = "背景色取得"                 ' キャプション
  CMDChild.OnAction = "GetColor"                  ' 関数名
  CMDChild.FaceId = 1763                          ' アイコン(省略可)
  
End Sub

入れ子にしない場合は.Add()だけにして配下の子供たちを作らないでOK。でもたくさん仕込みたい場合は見づらくなっちゃうので、○○系とかで分けてやると見易くてGood。やりがちな間違いですが関数名の所に()は不要です。
※ちなみにFaceIdについてはこちらで一覧紹介されてます。

メニューから呼び出す関数を作る

こちらも同じくModelue1に追加します。

Sub Set0()

    Dim arr As Variant
    Dim nRowCnt As Long
    Dim nColCnt As Long
    arr = Selection.Value
    
    ' 空の場合
    If IsEmpty(arr) = True Then
       Exit Sub
    End If
    ' 一か所のみの選択の場合
    If IsArray(arr) = False Then
       Selection.NumberFormatLocal = "@"
       Selection.Value = "0" ＆ arr
       Exit Sub
    End If
    ' 配列分ループ
    For nRowCnt = 1 To UBound(arr)
        For nColCnt = 1 To UBound(arr, 2)
　　　　　　　　' N/A#等のエラーセル以外
              If IsError(arr(nRowCnt, nColCnt)) = False Then
                 arr(nRowCnt, nColCnt) = "0" ＆ arr(nRowCnt, nColCnt)
              End If
        Next
    Next
    ' 書式設定
    Selection.NumberFormatLocal = "@"
    Selection.Value = arr

End Sub

今回TEST用に用意したのがこちらのSet0関数。
電話番号が含まれているCSVをエクセルで開いて上書き保存するみたいな場面ありません？
そのまま保存しちゃうと頭の0が消し飛ぶので、選択範囲の頭に0を付与して文字列形式にする関数です。
※いや、そもそも…って話しなんですけどね

実際の動き

上記コードをコピペすると右クリックメニュー一番下にCMDが追加され、
そのサブメニューに【選択範囲の頭に0を付ける】が追加されます。
※gif動画です。動かない場合はクリックして下さい。

作った関数の是非は取り敢えず置いといて、、、CommandBarControlと個人用マクロブックは超絶便利この上ないので、是非組み合わせたオリジナルメニューで作業短縮しちゃいましょう～(ᵔᴥᵔ)