【ExcelVBA】クラスモジュールを使用してイベント処理を一元化しよう！【栄養計算ソフト】

みなさん，こんにちは。
シンノユウキ（shinno1993）です。

今回は「栄養計算ソフトに機能を追加しよう」の連載第2回。以下の記事の続きです。

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

「栄養計算ソフトに機能を追加しよう」の連載第1回。今回は栄養計算ソフトに独自のメニューを追加し、そのメニューからユーザーフォームを表示する機能を追加していきます。

www.y-shinno.com

2018.05.05

前回はExcelのメニューからマクロを実行できるように項目を追加しました。

今回はさらに使いやすさを向上させるために、クラスモジュールを利用して、オプションボタンを変更した際のイベント処理を一元化して簡単に記述できるようにしていきます。

ではいきましょう！

オプションボタン変更→反映ボタンクリックは面倒…

現在の栄養計算ソフトでは、食品群から食品を入力する際に、オプションボタンで食品群を選択した後に、反映ボタンをクリックし、選択した食品群に合致する食品のみをリストに追加しています。つまり、ただリストに食品を追加するだけの作業に、

• オプションボタンの選択
• 反映ボタンのクリック

という二手間がかかっているのです。どうにかこれを、オプションボタンをクリックするだけでリストボックスに自動で食品が入力できるようにしたい。つまり反映ボタンクリックの手間をなくしたい！というのが今回行いたいことです。

 

何個もイベント処理を書くのは面倒…

こういった処理を行いたい場合、イベント処理を用います。オプションボタンが変更された際に発火するイベント処理として、リストボックスに追加する処理を記述します。

ただ、ExcelVBAのユーザーフォームの場合、この処理をオプションボタンごとに記述しなければいけません。つまり、今回は18個のオプションボタンがあるので、18個のイベント処理を記述する必要があるのです。つまり、以下のような感じで、助長で面倒なコードを書かなければいけないのですね。

まぁ、これでも問題なく動くのですが、なんとなくスマートではないですよね。できることならもっと簡単に後々の変更もしやすいようにコードを書きたいわけです。

 

クラスモジュールを追加しよう！

そのためには、クラスモジュールを上手く活用する必要があります。

ExcelVBAでは、「クラス」という概念はあまり使用されません。必須というわけではありませんし、使うことでむしろ無用な複雑性をもたらす場合もあります。しかし、使い方によっては便利ですし、今回の場合も、それを使うことでイベント処理を一元化できます。

まずは「挿入」→「クラスモジュール」から追加してください。

そして以下のコードをクラスモジュールに追加しましょう。

Private WithEvents Target As MSforms.OptionButton

Public Sub setOpt(new_opt As MSforms.OptionButton)
  Set Target = new_opt
End Sub

Private Sub Target_Change()
  'オプションボタンが変更された際の処理
End Sub

 

コードの解説は後々するとして、このクラスモジュールを使用するために必要なコードを、フォーム側にも記述します。

まずは、ユーザーフォーム開始時の処理を以下のように変更しましょう。

Private Sub UserForm_Initialize()
    Dim i As Long
    For i = FG_START_INDEX To FG_END_INDEX
        opt(i).setOpt Controls("OptionButton" & i)
        Controls("OptionButton" & i).Caption = Worksheets(WSNAME_STG).Cells(i, 1).Value
    Next i
End Sub

 

そして、冒頭に以下のコードを追加しましょう。

Private opt(FG_START_INDEX To FG_END_INDEX) As New Class1

 

では順番に見ていきましょう。

ユーザーフォーム開始時の処理として、以下のコードを追加しました。

opt(i).setOpt Controls("OptionButton" & <> i)

 

このopt(i)というのは、フォームの冒頭で指定したクラスのインスタンスです。これに、オプションボタンをセットする処理を行っています。setOptメソッドで、Controls("OptionButton" & <> i)をopt(i)にセットしています。

ちなみに、setOptはクラスモジュールで以下のように記述しましたね。

Public Sub setOpt(new_opt As MSforms.OptionButton)
  Set Target = new_opt
End Sub

 

また、クラスモジュールの冒頭には、以下のコードを記述しました。

Private WithEvents Target As MSforms.OptionButton

 

このコードを記述することで、それが書かれたクラスに対して発生するイベント処理を、すべてこれで拾うことができます。ユーザーフォーム開始時の処理として、クラスのインスタンスとしてオプションボタンを設置しましたよね。なので、このオプションボタンのイベント処理を、すべてここで拾えるというわけなのです。

 

 

イベント発生時の処理を記述しよう

オプションボタンのイベントをすべて拾うことができるようになりました。では、実際のイベント処理を記述していきます。

いまのところ、反映ボタンをクリックした際に、リストボックスに追加されるようになっていますよね。その処理は以下のようになっています。

Private Sub btnRef_Click()
  Dim i As Long
  Dim fg As Long
  For i = FG_START_INDEX To FG_END_INDEX
    If Controls("OptionButton" <> i).Value = True Then
      fg = Val(Left(Controls("OptionButton" & <> i).Caption, 2)) 'オプションボタンの先頭2文字が食品番号。それを取得し数値に変更 例）01→1など
    End If
  Next i
  lstFood.Clear
  Dim mainLastRow As Long
  mainLastRow = Worksheets(WSNAME_MAIN).Cells(Rows.Count, 1).End(xlUp).Row
  For i = MAIN_START_ROW To mainLastRow
    With Worksheets(WSNAME_MAIN)
      If .Cells(i, 1).Value = fg Then
        lstFood.AddItem (Format(.Cells(i, MAIM_FOODNUM_CLM).Value, "00000") & <> " " <> .Cells(i, MAIN_FOODNAME_CLM).Value)
      End If
    End With
  Next i
End Sub

 

これをClassモジュールの方に移動させ、オプションボタンのチェンジイベントが発生した際に発火するように変更しましょう。この処理は、クラスモジュールの以下の部分に記述していきます。

Private Sub Target_Change()
    'オプションボタンが変更された際の処理
End Sub

 

基本的には、反映ボタンをクリックした際の処理をコピペすれば良いのですが、ただコピペするだけでは使用できませんので、何点か修正が必要になります。

まず、For~Nextステートメントですべてのオプションボタンを調べていましたが、変更されたオプションボタンが変数：Targetに格納されるようになっていますので、これが不要です。削除しましょう。これに伴い、Controls()でコントロールを指定していた部分もTargetに変更します。

さらに、このコードはクラスモジュールに記述されますので、Target以外のユーザーフォームのコントロールを指定する際には、フォームオブジェクトを指定しなければなりません。今回の場合だと、リストボックス：LstFoodの親オブジェクトとして、frmFoodGroupを追加しましょう。

以上を変更しますと、以下のようになります。

Private Sub Target_Change()
    'オプションボタンが変更された際の処理
    Dim i As Long
    Dim fg As Long
    If Target.Value = True Then
        fg = Val(Left(Target.Caption, 2))  'オプションボタンの先頭2文字が食品番号。それを取得し数値に変更 例）01→1など
    End If
    frmFoodGroup.lstFood.Clear
    Dim mainLastRow As Long
    mainLastRow = Worksheets(WSNAME_MAIN).Cells(Rows.Count, 1).End(xlUp).Row
    For i = MAIN_START_ROW To mainLastRow
        With Worksheets(WSNAME_MAIN)
            If .Cells(i, 1).Value = fg Then
                frmFoodGroup.lstFood.AddItem (Format(.Cells(i, MAIM_FOODNUM_CLM).Value, "00000") & " " & .Cells(i, MAIN_FOODNAME_CLM).Value)
            End If
        End With
    Next i
End Sub

これで、オプションボタンを変更した際のイベントをたくさん記述することなく、簡単にイベント処理を行うことができます。

 

まとめ

今回は、「栄養計算ソフトに機能を追加しよう」の連載第2回として、クラスモジュールを用い、イベント処理を一元化する方法を紹介しました。

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

 

連載目次

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