作業日報 | 【VBA】Excelでイベントハンドラを共通化

2010.01.29 Friday

【VBA】Excelでイベントハンドラを共通化

　Excel-vbaですが、コマンドボタンを沢山配置したシートでボタン押下処理などのイベント関数を共通化したい場合がありますが、シートにWithEventsを付与したクラス変数を実装することで可能です。

・「設定ボタン」で全てのコマンドボタンにClass1:btn_click()関数を実行

サンプルダウンロード→excel_shared_event.xls

・Sheet1

Option Explicit
'イベントフック用クラス
Dim mybutton(50) As Class1

Private Sub CommandButton1_Click()
  Dim btn As CommandButton
  Dim i As Integer
  
  MsgBox ActiveSheet.OLEObjects.Count & " 個のオブジェクトがあります"
  
  For i = 1 To ActiveSheet.OLEObjects.Count
    '参照を取得
    Set btn = ActiveSheet.OLEObjects(i).Object
    
    '設定ボタンのイベントは書き換えません
    If btn.Caption <> "設定" Then
      btn.BackColor = CommandButton1.BackColor
      
      'インスタンスを生成します
      Set mybutton(i) = New Class1
      'クラスのプロパティに代入
      Set mybutton(i).btn = btn
    End If
  Next

End Sub

・Class1

Option Explicit

'コマンドボタン実体のプロパティ（イベント取り用）
Public WithEvents btn As CommandButton

'実際のbtnの共通ルーチン
Private Sub btn_Click()

  'MsgBox btn.Name & " - " & btn.Caption
  '色でも変えてみるかな、、
  btn.BackColor = RGB(120 + Rnd() * 125, 120 + Rnd() * 125, 120 + Rnd() * 125)
  
End Sub

　実際にはボタン自体もクラスコンストラクタで動的に生成したほうがスマートかもしれません、その場合OLEObjectsを利用します、この辺はVBヘルプに詳しく書いてあります。
　ただエクセルＶＢのクラスはコンストラクタに引数が渡せないしょぼい仕様だったような、、