みなさん，こんにちは。
シンノユウキ（shinno1993）です。
今回から「栄養計算ソフトに機能を追加しよう」の連載をはじめます。
以前の連載で、Excelで栄養計算ソフトを自作し、それにユーザーフォームを導入していきました。今回からはその続編として、ExcelVBAを活用し、栄養計算ソフトに様々な機能を追加していきたいと思います。
少し難易度は向上しますが、最終的には非常に使いやすい栄養計算ソフトが作成できるかと思います。
では行きましょう。
初めての方へ
今回の連載では、前回までの連載で作成した栄養計算ソフトを用います。
以前の連載をご覧になったことがない、という方は、以下の連載から読み始めることをおすすめします。これらの連載を通して、ExcelやExcelVBA、ユーザーフォームの基礎知識を習得していきましょう。
また、そういった時間がない、という方は、以下の記事から作成済みの栄養計算ソフトをダウンロードすることができますので、そちらを使用して以下の連載をご覧ください。
【ExcelVBA】栄養計算ソフト＋ユーザーフォームの完成！DLも可【ユーザーフォーム】
今回したいこと！
メニューバーからフォームを起動したい！
今回はメニューバーからフォームを起動する処理を実装したいと思います。現在の栄養計算ソフトでは、以下の画像のように、シート上に設置しているボタンからフォームを起動しています。
しかし、これでは少し不便です。シートの一部をボタンに占領されることになりまし、何となくスマートではありません。また、今後はこの栄養計算ソフトのアドイン化も目指しますので、そういった面でも、ボタンからフォームを起動する処理は避けたいところです。。
なので、こういったフォームを起動する処理を、Excelのメニューから行えるようにしていきます。
メニューにマクロを追加してみよう！
コードはこんな感じ！
では、メニューにマクロを追加するするコードを紹介します。以下のようになります。
Private Sub Workbook_Open()
Dim bar As CommandBar
Dim menu As CommandBarPopup
Dim btnShowFoodGroup As CommandBarButton, btnShowSearch As CommandBarButton
'Excelのメニューバーを指定
Set bar = Application.CommandBars("Worksheet Menu Bar")
'すでにコマンドバーにメニューがある場合は削除
For Each menu In bar.Controls
menu.Delete
Next menu
'コマンドバーにメニューを追加
Set menu = bar.Controls.Add(Type:=msoControlPopup)
menu.Caption = "栄養計算ソフト"
'それぞれのボタンを設置→キャプションと実行するマクロを指定
Set btnShowFoodGroup = menu.Controls.Add(Type:=msoControlButton)
With btnShowFoodGroup
.Caption = "食品群から入力"
.OnAction = "showFormFoodGroup"
End With
Set btnShowSearch = menu.Controls.Add(Type:=msoControlButton)
With btnShowSearch
.Caption = "検索して入力"
.OnAction = "showFormSearch"
End With
menu.Visible = True
End Sub
これを、ThisWorkbookのコード部に記述し、実行してみてください。そうすると、以下のようなメニューがアドインタブ内に追加されます。
実際にメニューのボタンをクリックしてみてください。それぞれのフォームが表示されることが確認できるかと思います。
ではコードを解説していきましょう。
変数の意味はこんな感じ！
コードの冒頭で変数を宣言しています。その意味は以下の通りです。
| 変数名 | 意味 |
|---|---|
| bar |
コマンドバー（一般的にExcelのメニューバーと呼ばれる部分）
|
| menu |
今回追加するメニューのオブジェクト
|
| btnShowFoodGroup |
メニュー内に設置するfrmFoodGroupを表示するボタン
|
| btnShowSearch |
メニュー内に設置するfrmSearchを表示するボタン
|
そして、Excelのメニューバーをbarにsetしています。Excelのメニューバーは、Application.CommandBars("Worksheet Menu Bar")で取得できます。
最初からあるmenuは削除！
今回のコードの場合、処理を実行するたびにメニューがどんどん増えていきます。それだと困ってしまいますよね。なので、barにすでにmenuが存在する場合に、それを削除するという処理が以下のものです。
'すでにコマンドバーにメニューがある場合は削除
For Each menu In bar.Controls
menu.Delete
Next menu
コマンドバーにmenuを追加しよう！
この部分で、コマンドバーにmenuを追加しています。Captionがメニューの表示名になります。
'コマンドバーにメニューを追加
Set menu = bar.Controls.Add(Type:=msoControlPopup)
menu.Caption = "栄養計算ソフト"
ボタンを設置→実行するマクロを指定
メニューにボタンを設置し、そのCaptionとonActionを設定しています。onActionで指定したマクロが、クリック時に実行されます。実行するマクロはダブルクオーテーションで囲っておきましょう。
なお、ここで指定したマクロは、後ほど標準モジュールに記述していきます。
'それぞれのボタンを設置→キャプションと実行するマクロを指定
Set btnShowFoodGroup = menu.Controls.Add(Type:=msoControlButton)
With btnShowFoodGroup
.Caption = "食品群から入力"
.OnAction = "showFormFoodGroup"
End With
Set btnShowSearch = menu.Controls.Add(Type:=msoControlButton)
With btnShowSearch
.Caption = "検索して入力"
.OnAction = "showFormSearch"
End With
コードやシートを修正しよう！
標準モジュールにonActionの処理を入力しよう！
では、onActionに設定したマクロを標準モジュールに入力しておきます。以下のコードを入力してください。
Sub showFormFoodGroup()
frmFoodGroup.Show vbModeless
End Sub
Sub showFormSearch()
frmSearch.Show vbModeless
End Sub
それぞれ、フォームをモードレスで開く処理を記述しています。
シート上のボタンを削除しよう！
これに伴い、従来からシート上に設置していたボタンは不要になりますので、削除しておきましょう。また、シートにもそのボタンをクリックした際の処理が残っていますので、それも一緒に削除しておきます。
ブックを閉じた際の処理を記述しよう！
実は、現状のままだと、ブックを閉じた際に、メニューが残ったままになってしまいます。それでは困りますのでブックを閉じた際にメニューを削除する処理を記述しましょう。これは、WorkbookのBeforeCloseイベントとして記述します。以下のようになればOKです。
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim menu As CommandBarPopup
For Each menu In Application.CommandBars("Worksheet Menu Bar").Controls
menu.Delete
Next menu
End Sub
これで、終了です。
まとめ
今回は、「栄養計算ソフトに機能を追加しよう」の連載初回として、Excelメニューからマクロを実行する方法を紹介しました。
また次回もがんばりましょう。