初心者のためのExcel エクセルマクロVBA入門：ツールバーメニューを作成する
通常はシートにUIを作成するのが一般的！？
今回はエクセルのメニューバーに独自のマクロメニューを作ってしまいましょう！ということです。前回のAuto_OpenとAuto_Closeメソッドが大活躍します。これを使うと、マクロを開くとシート上にボタンがあって・・・という感じにはなりません。とってもスマートにエクセルのメニューの場所にツールバーが表示されるのです！
ちょっとかっこいいですが、UI的にほんとにわかりやすいか？とかいう議論は今回はちょっとおいといてｗこんなこともできるよー！ということを見てもらいます。
今だとタブとリボンだが・・・
確かにリボンとタブを独自で作るにはちょっとハードルが高いです。XMLの知識とか、Office Ribbon Editor というリボンエディタを利用したりしなければいけないので若干上級者向けです。（使い方がわかればそんなに難しい話ではないですが・・・）ですので今回はアドインのツールバーをVBAだけで完結させるということを主眼に置いてます。ツールバーの場合、少しオブジェクトの構成が複雑ですが、ちょっと頑張って理解してみてくださいね。
ツールバーってどうやって作るの？
手の付けようがない感じのお話のように見えますが、実はそうでもないんです。なぜか？いつも言ってます。
オブジェクトを正しく指定すればマクロは超簡単！
です。ここでも同じことが言えます。つまり、メニューバーってどのオブジェクトが持ってるの？ということが分かれば、後はそのプロパティとかメソッドを使ってあげればいいのです。簡単ですよね？では、見てみましょう。ツールバーはやっぱりツールバーオブジェクトというのがあります。じゃあツールバーオブジェクトってどこにあるの？ですよね？シートオブジェクトがー・・・
ApplicationオブジェクトのWorkbooksオブジェクトの中にある
のと同じでツールバーオブジェクトもApplcationオブジェクトからたどれば、必ずどこかにあるはずです。っという感じで調べてみるとありました。その名も・・・
CommandBarsコレクションオブジェクト
CommandBarsオブジェクトはコマンドバーをたくさん持っているオブジェクトで、Addメソッドで新しいコマンドバーを作ることができます。後は作成したコマンドバーにメニューのボタンとかを追加してやればよいと・・・
あれ？( ﾟдﾟ)
メニューのボタンってこれもオブジェクトか？ということですがこれは「YES」です。最初にも言いましたが、この独自のメニューバーオブジェクト、実は色々なオブジェクトの集合体として出来ています。つまりどういうことかというと・・・
CommandBarsコレクションオブジェクト⇒ComandBarオブジェクト⇒CommandBarControlsコレクションオブジェクト⇒CommandBarControlオブジェクト⇒各種コマンドバーオブジェクト⇒各種イベントやプロパティの設定
というような構成になっています。
( ﾟдﾟ)？
ですよね・・・ちょっと図にしますと・・・
こんな感じになります。つまり、バーの枠があって、その中にボタンの集合があって、その中に各種ボタンやポップアップのメニューとかが存在しています。で、バーの枠自体は他のバーの集合体の中にあるというわけです。ですからメニューバーを作りたい場合は・・・
・まずバーの枠を追加して・・・
・追加したバーの枠にボタンやポップアップを追加して・・・
・そのボタンの名前とか押したときの動きとかを設定する
ということを行えばいいということになります。当たり前ですが、いきなりボタンを作っても、枠がないので、作れません。このように順番にオブジェクトを操作して作っていくことになります。ちょっと難しいですかね。。。汗・・・確かにちょっと構成が難しい感じになっていますが、基本は簡単で、Applicationオブジェクトがあってそこからツールバーオブジェクトがあって、コマンドボタンオブジェクトがあって・・・というようにちゃんとオブジェクトを操作してあげれば作ることはできるのです。
ファイルが開いたらツールバーを作りたい。
ちょっとやってみましょう。ファイルを開いた瞬間にツールバーを作成します。こうするとファイルを開けばメニューが自動的に作成されるというわけです。開いたら実行するメソッドは前回やりましたね。Auto_Openメソッドを使います。
最初と最後書いて・・・
'''''''''''''''''''''''''''''''''''''''''''''''' 'ToolBar_Open() 'ツールバーを作成する. 'このワークブックを立ち上げたときに自動的に追加される '''''''''''''''''''''''''''''''''''''''''''''''' Private Sub Auto_Open() End Sub
必要なオブジェクトを宣言します。今回はCommandBarオブジェクトとCommandBarControlオブジェクトと、CommandBarButtonボタンオブジェクトです。
'''''''''''''''''''''''''''''''''''''''''''''''' 'ToolBar_Open() 'ツールバーを作成する. 'このワークブックを立ち上げたときに自動的に追加される '''''''''''''''''''''''''''''''''''''''''''''''' Private Sub Auto_Open() ' 必要なオブジェクトを作成 Dim objTlBar As CommandBar Dim objTlBarCnt As CommandBarControl Dim objTlBarBtn As CommandBarButton End Sub
やることを日本語で書いて行きます。順番に作っていくわけです。
'''''''''''''''''''''''''''''''''''''''''''''''' 'ToolBar_Open() 'ツールバーを作成する. 'このワークブックを立ち上げたときに自動的に追加される '''''''''''''''''''''''''''''''''''''''''''''''' Private Sub Auto_Open() ' 必要なオブジェクトを作成 Dim objTlBar As CommandBar Dim objTlBarCnt As CommandBarControl Dim objTlBarBtn As CommandBarButton ' バーの枠を作る（CommandBarオブジェクトを作る） ' ボタンやメニューを追加するオブジェクトを作る（CommandBarControlオブジェクト) ' ボタンのオブジェクトを作る ' ボタンに名前やクリックしたときに動かすイベントプロシージャなどを設定する ' こうしてできたツールバーオブジェクトを実際に表示する ' ツールバーを非表示にできなくする End Sub
こんな感じになります。順番通りです。最後に、0から作ったオブジェクトをエクセルに表示させる。ということころまで書いています。後はVBAに翻訳です。
'''''''''''''''''''''''''''''''''''''''''''''''' 'ToolBar_Open() 'ツールバーを作成する. 'このワークブックを立ち上げたときに自動的に追加される '''''''''''''''''''''''''''''''''''''''''''''''' Private Sub Auto_Open() ' 必要なオブジェクトを作成 Dim objTlBar As CommandBar Dim objTlBarCnt As CommandBarControl Dim objTlBarBtn As CommandBarButton ' バーの枠を作る（CommandBarオブジェクトを作る） Set objTlBar = Application.CommandBars.Add(Name:=g_cnsTITLE, Position:=msoBarTop) ' ボタンやメニューを追加するオブジェクトを作る（CommandBarControlオブジェクト) Set objTlBarCnt = objTlBar.Controls.Add(Type:=msoControlButton) ' ボタンのオブジェクトを作る Set objTlBarBtn = objTlBarCnt ' ボタンに名前やクリックしたときに動かすイベントプロシージャなどを設定する objTlBarBtn.Style = msoButtonCaption 'ボタンのスタイル(今回はテキストタイプ) objTlBarBtn.Caption = 'ボタン１' 'ボタンのキャプション(ボタンに書かれる文字) objTlBarBtn.TooltipText = 'ボタン1です' 'ボタンにマウスを当てたときのテキスト objTlBarBtn.OnAction = 'button1Clicked' 'ボタンをクリックした時の動作 ' こうしてできたツールバーオブジェクトを実際に表示する objTlBar.Visible = True 'ツールバーを非表示にできなくする objTlBar.Protection = msoBarNoChangeVisible ActiveWindow.ScrollRow = 1 '表示後、オブジェクトを廃棄 Set objTlBarBtn = Nothing Set objTlBarCnt = Nothing Set objTlBar = Nothing End Sub
これでOKです。オブジェクトを作る順番というのを意識してみてください。また、複数のメニューを作りたい場合は、同じ記述を、「繰り返す」ことになるのでFor文などを上手く使って記述しましょう。
また、Popupを利用するとメニューをグループ化することもできるので、その場合はさらに、オブジェクト階層が1つ深くなるので注意してください。ちなみに、結構がっつりなメニューを作成すると、こんな感じ。
参考程度に見てください。ポップアップメニューとボタンメニューの複合のツールバーです。
Private Const g_cnsTITLE = 'メニューサンプル' '''''''''''''''''''''''''''''''''''''''''''''''' 'ToolBar_Open() 'ツールバーを作成する. 'このワークブックを立ち上げたときに自動的に追加される '''''''''''''''''''''''''''''''''''''''''''''''' Private Sub Auto_Open() Dim xlAPP As Application Dim objTlBar As CommandBar Dim objTlBarCnt As CommandBarControl Dim objTlBarBtn As CommandBarButton Dim objPopUp As CommandBarPopup Dim vntCaption As Variant Dim vntTipText As Variant Dim vntOnAction As Variant Dim i As Integer Dim blnIsTrue As Boolean 'アプリケーションオブジェクトを作成 Set xlAPP = Application 'ツールバーのボタンの名前、Tips、実行プロシージャを設定する vntCaption = Array("データ読込1", "データ読込2", "データ読込3") vntCaption2 = Array("[実行1]", "[実行2]", "[実行3]", _ "[実行4]", "[実行5]", "[実行6]") vntCaption3 = Array("ボタン1", "ボタン2", "ボタン3", "Help") vntTipText = Array("説明１", _ "説明２", _ "説明３") vntTipText2 = Array("読み込み1の説明", _ "読み込み2の説明", _ "読み込み3の説明", _ "読み込み4の説明", _ "読み込み5の説明", _ "読み込み6の説明") vntTipText3 = Array("ボタン1の説明", _ "ボタン2の説明", _ "ボタン3の説明", "ヘルプ情報") vntOnAction = Array("ReadData1", _ "ReadData2", _ "ReadData3") vntOnAction2 = Array("Procedure1", _ "Procedure2", _ "Procedure3", _ "Procedure4", _ "Procedure5", _ "Procedure6") vntOnAction3 = Array("Button1Click", _ "Button2Click", "Button3Click", "Help") 'ツールバーオブジェクト作成 Set objTlBar = xlAPP.CommandBars.Add(Name:=g_cnsTITLE, Position:=msoBarTop) blnIsTrue = False 'ツールバーにポップアップメニューを追加する Set objTlBarCnt = objTlBar.Controls.Add(Type:=msoControlPopup) objTlBarCnt.BeginGroup = True Set objPopUp = objTlBarCnt objPopUp.Caption = "Data読み込み" 'ポップアップメニューにボタンを追加する For i = 0 To 2 Set objTlBarCnt = objPopUp.Controls.Add(Type:=msoControlButton) objTlBarCnt.BeginGroup = blnIsTrue Set objTlBarBtn = objTlBarCnt 'ボタンのプロパティを設定する objTlBarBtn.Style = msoButtonCaption 'ボタンのスタイル(今回はテキストタイプ) objTlBarBtn.Caption = vntCaption(i) 'ボタンのキャプション(ボタンに書かれる文字) objTlBarBtn.TooltipText = vntTipText(i) 'ボタンにマウスを当てたときのテキスト objTlBarBtn.OnAction = vntOnAction(i) 'ボタンをクリックした時の動作 ' 2番目以降はボタンの境界を設定 blnIsTrue = True Next blnIsTrue = False 'もうひとつポップアップメニュを追加 'ツールバーにポップアップメニューを追加する Set objTlBarCnt = objTlBar.Controls.Add(Type:=msoControlPopup) objTlBarCnt.BeginGroup = True Set objPopUp = objTlBarCnt objPopUp.Caption = "ポップアップ１" 'ポップアップメニューにボタンを追加する For i = 0 To 5 Set objTlBarCnt = objPopUp.Controls.Add(Type:=msoControlButton) objTlBarCnt.BeginGroup = blnIsTrue Set objTlBarBtn = objTlBarCnt 'ボタンのプロパティを設定する objTlBarBtn.Style = msoButtonCaption 'ボタンのスタイル(今回はテキストタイプ) objTlBarBtn.Caption = vntCaption2(i) 'ボタンのキャプション(ボタンに書かれる文字) objTlBarBtn.TooltipText = vntTipText2(i) 'ボタンにマウスを当てたときのテキスト objTlBarBtn.OnAction = vntOnAction2(i) 'ボタンをクリックした時の動作 ' 2番目以降はボタンの境界を設定 blnIsTrue = True Next For i = 0 To 3 'ボタンをツールバーコントロールオブジェクトに追加する Set objTlBarCnt = objTlBar.Controls.Add(Type:=msoControlButton) 'ボタンの境界を設定 objTlBarCnt.BeginGroup = blnIsTrue 'ボタンのオブジェクトを作成 Set objTlBarBtn = objTlBarCnt 'ボタンのプロパティを設定する objTlBarBtn.Style = msoButtonCaption 'ボタンのスタイル(今回はテキストタイプ) objTlBarBtn.Caption = vntCaption3(i) 'ボタンのキャプション(ボタンに書かれる文字) objTlBarBtn.TooltipText = vntTipText3(i) 'ボタンにマウスを当てたときのテキスト objTlBarBtn.OnAction = vntOnAction3(i) 'ボタンをクリックした時の動作 'ボタンの境界は2個目以降はつける blnIsTrue = True Next '↑ここまでがツールバーの設定実際に表示するのは↓ここから 'ツールバーを表示する objTlBar.Visible = True 'ツールバーを非表示にできなくする objTlBar.Protection = msoBarNoChangeVisible ActiveWindow.ScrollRow = 1 '表示後、オブジェクトを廃棄 Set objTlBarBtn = Nothing Set objTlBarCnt = Nothing Set objTlBar = Nothing End Sub
最初にボタンに設定する文言やクリック時のイベントメソッドなどを配列にまとめておいて、後でFor文を使って一気に作っています。
ファイルを閉じたらメニューも削除
忘れずに書いておかないといけないのが、ファイルを閉じたらメニューは削除しなければいけません。そうしないと常に他のファイルでも出続けてしまいます。これを防ぐためにも、ちゃんとファイルを閉じたらメニューは削除しましょう。
'''''''''''''''''''''''''''''''''''''''''''''''' 'ToolBar_Close() 'ツールバーを削除する 'このワークブックを閉じたときにに自動的に削除する '''''''''''''''''''''''''''''''''''''''''''''''' Private Sub Auto_Close() Dim objBar As CommandBar ' ツールバーオブジェクトを取得する Set objBar = Application.CommandBars(g_cnsTITLE) ' ツールバーを削除する objBar.Delete ' オブジェクトを廃棄 Set objBar = Nothing End Sub
いかがでしょうか？ちょっと難しいかもしれませんが、是非挑戦してみてくださいね。
今日はここまで！！
かしこ
次でエクセルマクロの記事は100回目かぁ・・・
