【ExcelVBA】Excelメニューからマクロを実行しよう!【栄養計算ソフト】

Excel

みなさん,こんにちは.
シンノユウキ(@y_stadio)です.

今回から「栄養計算ソフトに機能を追加しよう」の連載をはじめます。

以前の連載で、Excelで栄養計算ソフトを自作し、それにユーザーフォームを導入していきました。今回からはその続編として、ExcelVBAを活用し、栄養計算ソフトに様々な機能を追加していきたいと思います。

少し難易度は向上しますが、最終的には非常に使いやすい栄養計算ソフトが作成できるかと思います。

では行きましょう。

 

初めての方へ

今回の連載では、前回までの連載で作成した栄養計算ソフトを用います。

以前の連載をご覧になったことがない、という方は、以下の連載から読み始めることをおすすめします。これらの連載を通して、ExcelやExcelVBA、ユーザーフォームの基礎知識を習得していきましょう。

【連載】Excelで栄養計算ソフトを自作しよう!

【連載】管理栄養士のためのVBA入門

【連載】栄養計算ソフトにユーザーフォームを導入しよう!

 

また、そういった時間がない、という方は、以下の記事から作成済みの栄養計算ソフトをダウンロードすることができますので、そちらを使用して以下の連載をご覧ください。

【ExcelVBA】栄養計算ソフト+ユーザーフォームの完成!DLも可【ユーザーフォーム】

 

今回したいこと!

メニューバーからフォームを起動したい!

今回はメニューバーからフォームを起動する処理を実装したいと思います。現在の栄養計算ソフトでは、以下の画像のように、シート上に設置しているボタンからフォームを起動しています。

しかし、これでは少し不便です。シートの一部をボタンに占領されることになりまし、何となくスマートではありません。また、今後はこの栄養計算ソフトのアドイン化も目指しますので、そういった面でも、ボタンからフォームを起動する処理は避けたいところです。。

なので、こういったフォームを起動する処理を、Excelのメニューから行えるようにしていきます。

メニューにマクロを追加してみよう!

コードはこんな感じ!

では、メニューにマクロを追加するするコードを紹介します。以下のようになります。

これを、ThisWorkbookのコード部に記述し、実行してみてください。そうすると、以下のようなメニューがアドインタブ内に追加されます。

実際にメニューのボタンをクリックしてみてください。それぞれのフォームが表示されることが確認できるかと思います。

 

ではコードを解説していきましょう。

変数の意味はこんな感じ!

コードの冒頭で変数を宣言しています。その意味は以下の通りです。

変数名 意味
bar
コマンドバー(一般的にExcelのメニューバーと呼ばれる部分)
menu
今回追加するメニューのオブジェクト
btnShowFoodGroup
メニュー内に設置するfrmFoodGroupを表示するボタン
btnShowSearch
メニュー内に設置するfrmSearchを表示するボタン

そして、Excelのメニューバーをbarにsetしています。Excelのメニューバーは、Application.CommandBars("Worksheet Menu Bar")で取得できます。

最初からあるmenuは削除!

今回のコードの場合、処理を実行するたびにメニューがどんどん増えていきます。それだと困ってしまいますよね。なので、barにすでにmenuが存在する場合に、それを削除するという処理が以下のものです。

コマンドバーにmenuを追加しよう!

この部分で、コマンドバーにmenuを追加しています。Captionがメニューの表示名になります。

ボタンを設置→実行するマクロを指定

メニューにボタンを設置し、そのCaptionとonActionを設定しています。onActionで指定したマクロが、クリック時に実行されます。実行するマクロはダブルクオーテーションで囲っておきましょう。

なお、ここで指定したマクロは、後ほど標準モジュールに記述していきます。

 

コードやシートを修正しよう!

標準モジュールにonActionの処理を入力しよう!

では、onActionに設定したマクロを標準モジュールに入力しておきます。以下のコードを入力してください。

それぞれ、フォームをモードレスで開く処理を記述しています。

シート上のボタンを削除しよう!

これに伴い、従来からシート上に設置していたボタンは不要になりますので、削除しておきましょう。また、シートにもそのボタンをクリックした際の処理が残っていますので、それも一緒に削除しておきます。

ブックを閉じた際の処理を記述しよう!

実は、現状のままだと、ブックを閉じた際に、メニューが残ったままになってしまいます。それでは困りますのでブックを閉じた際にメニューを削除する処理を記述しましょう。これは、WorkbookのBeforeCloseイベントとして記述します。以下のようになればOKです。

これで、終了です。

まとめ

今回は、「栄養計算ソフトに機能を追加しよう」の連載初回として、Excelメニューからマクロを実行する方法を紹介しました。

また次回もがんばりましょう。

 

連載目次

  1. Excelメニューからマクロを実行しよう!
  2. クラスモジュールを使用してイベント処理を一元化しよう!
  3. チェックボックスに含まれる情報をシートに転記してみよう!