[Excel]VBAでコントロール配列

 

 

こんにちは。ナガオカ(@boot_kt)です。

 


VBAでコントロール配列です

VBやC#だとコントロール配列が使えるんですけど、VBAは無いんですよね

 

が、しかしっ!!!

 

クラスを使えば実現できるんすよ~!!!

 

サンプルをアップしますので、ぜひ参考にしてください!

この記事のザックリした内容
◆対象読者

  • VBAでコントロール配列を実現したい人

 

◆解決できるかも知れないお悩み

  • シート上に貼り付けた部品でも実現可能だけど、結構面倒なので要注意!!
  • 基本的にはユーザーフォームを使用する方がいいでしょう

 

◆どうやって解決するか

  • 標準機能としてはVBAにコントロール配列はありません
  • しかし、クラスとWithEventsを使うことにより、コントロール配列を実現します

 

 

 

 

出典元、これ大事!!!

この記事の出典元はかなり古い記事です

 

VisualBasicマガジン2001/01月号 の特集の中の一つ、「クロスプログラミングを自家薬籠中のものに クラスモジュールを使った Excel VBA プログラミング」

Excel VBA の世界では知らない人はいないんじゃないの?ってぐらい有名な大村あつしさん

 

VisualBasicマガジンはもう発行されていません

当時働いていた会社が毎月購入してて、それを読んでおりました

 

この記事を読んだ時「素晴らしい!!!」と思って、記事だけコピーして手元にとっておりました

それから19年もの間紛失することなく大切にとっておりました!!!

 

GitHubに置いてます

全てのソースコードはコチラ ↓

>> GitHubへ飛ぶにはココをクリック

Excelファイルも置いてます

xlsmファイルなので、実行する場合は自己責任でね♪

 

今回のサンプルの種類

  1. 部品の数は固定、ユーザーフォーム使用
  2. 部品の数は固定、ユーザーフォーム未使用
  3. 部品の数は未定(動的に追加する)、ユーザーフォーム使用

今回のコントロール配列のやり方はユーザーフォーム上の部品に対しての方がやり易いです

シート上に貼り付けた部品は色々と面倒です

 

ちなみに今回のサンプルでコントロール配列にしている部品はボタンです

 

1. 部品の数は固定、ユーザーフォーム使用

コントロール配列にしたい部品の数が決まりきっている場合

これが一番分かりやすし、実際このパターンが一番多いでしょう

 

以下にコード示しておきます。

1-1. まずはコントロール配列を実現するクラス

このクラスでコントロール配列を実現します

このクラスをコントロール配列にしたい部品の数だけNewして、そのインスタンスに部品を1個1個登録することでコントロール配列が実現できます

 

     ''**************************************************************
     ''
     '' コントロール配列を実現するクラス(ComandButtonのみ対応)
     ''
     ''   登録したコマンドボタンのイベントハンドラがここにある
     ''
     ''**************************************************************
     Option Explicit

     ''**************************************************************
     '' このクラスのプロパティ
     ''**************************************************************
     ' このクラスに登録したボタン
     Private WithEvents TargetBtn As MSForms.CommandButton
     Attribute TargetBtn.VB_VarHelpID = -1
     ' このクラスに登録したボタンの番号(どのボタンをクリックしたのか知るために必要)
     Private TargetIndex As Integer


     ''**************************************************************
     '' ボタンの登録
     ''**************************************************************
     Public Sub RegistButton(regButton As MSForms.CommandButton, Index As Integer)
          Set TargetBtn = regButton
          TargetIndex = Index
     End Sub


     ''**************************************************************
     '' クリックイベントのハンドラ
     '' 登録したボタン全てがこのイベントハンドラを使う
     ''**************************************************************
     Private Sub TargetBtn_Click()
          ' ラベルにクリックしたバタンのキャプションを表示する
          TargetBtn.Parent.Controls("Label1").Caption = "ボタン=" & TargetBtn.Caption & ":index=" & TargetIndex
     End Sub

 

1-2. コントロール配列にしたい部品が乗ってるユーザーフォーム

ユーザーフォームが開く時のイベントで、3つのコマンドボタンそれぞれをクラスに登録する

登録されたボタンをクリックすると、クラスにあるクリックイベント(TargetBtn_Click())で処理される

 

     Option Explicit

     '' コマンドボタンを登録するクラスの配列
     '' 今回は3個固定
     Private MyBtnArray(1 To 3) As New Class1


     ''**************************************************************
     '' UserFormのInitializeイベント
     ''**************************************************************
     Private Sub UserForm_Initialize()
          Dim i As Integer
          
          ' ボタンの個数分ループして、MyBtnArray配列(クラス)にボタンを登録する
          For i = LBound(MyBtnArray) To UBound(MyBtnArray)
               Call MyBtnArray(i).RegistButton(UserForm1.Controls("CommandButton" & i), i)
          Next i

     End Sub

 

1-3. ユーザーフォーム画面はこれね

コントロール配列に使うユーザーフォーム

 

  • Labelが1個
  • ボタン3個
  • ボタンがコントロール配列になります
  • ボタンをクリックしたら、Labelにクリックしたボタンの番号を表示
  • 各ボタンのクリックイベントではなく、全ボタンがクラスにあるクリックイベントを共通でつかっていることに注意

 

1-4. ユーザーフォームを呼び出すボタンだけを乗せたシート

     Option Explicit

     ''**************************************************************
     '' UserForm1表示ボタン
     ''**************************************************************
     Private Sub CommandButton1_Click()
         UserForm1.Show
     End Sub

 

 

注目ポイント

  • ボタンがコントロール配列になります
  • ボタンをクリックしたら、Labelにクリックしたボタンの番号を表示
  • 各ボタンのクリックイベントではなく、全ボタンがクラスにあるクリックイベントを共通で使っていることに注意

 

2. 部品の数は固定、ユーザーフォーム未使用

ユーザーフォームではなく、シート上に直接部品を貼り付けた場合

このパターンもあると思いますので、そのソースコードを以下に示しておきます

ユーザーフォーム上の部品にできても、シート上の部品だとできないこともありますので、割と面倒です

1-1. まずはコントロール配列を実現するクラス

このクラスでコントロール配列を実現します

このクラスをコントロール配列にしたい部品の数だけNewして、そのインスタンスに部品を1個1個登録することでコントロール配列が実現できます

 

    ''**************************************************************
    ''
    '' コントロール配列を実現するクラス(ComandButtonのみ対応)
    ''
    ''   登録したコマンドボタンのイベントハンドラがここにある
    ''
    ''**************************************************************
    Option Explicit
    
    ''**************************************************************
    '' このクラスのプロパティ
    ''**************************************************************
    ' このクラスに登録したボタン
    Private WithEvents TargetBtn As CommandButton
    Attribute TargetBtn.VB_VarHelpID = -1
    ' このクラスに登録したボタンの番号(どのボタンをクリックしたのか知るために必要)
    Private TargetIndex As Integer
    ' ↓このオブジェクトにはラベルを期待している
    Private objOLE As OLEObject
    
    ''**************************************************************
    '' ボタンの登録
    ''   regButton:コントロール配列対象のボタン
    ''   Index:コントロール配列対象のボタンを識別するための番号
    ''   ole:クリックイベントで使うオブジェクト(今回はラベルを期待している)
    ''**************************************************************
    Public Sub RegistButton(regButton As CommandButton, Index As Integer, ole As OLEObject)
        Set TargetBtn = regButton
        TargetIndex = Index
    ''    Set lblDisp = Label1
        Set objOLE = ole
    End Sub
    
    ''**************************************************************
    '' クリックイベントのハンドラ
    '' 登録したボタン全てがこのイベントハンドラを使う
    ''**************************************************************
    Private Sub TargetBtn_Click()
        ' ラベルにクリックしたバタンのキャプションを表示する
        objOLE.Object.Caption = "ボタン=" & TargetBtn.Caption & ":index=" & TargetIndex
    End Sub

 

2-2. コントロール配列にしたい部品が乗ってるシート

 

    Option Explicit

    '' コマンドボタンを登録するクラスの配列
    '' 今回は3個固定
    Private MyBtnArray(1 To 3) As New Class1
    
    Private hoge As label
    
    Private Sub btnInitialize_Click()
    
        ' ラベルをクリアしておく
        Label1.Caption = ""
    
        ' MyBtnArray配列(クラス)にボタンを登録する
        ' UserFormならforで回してCall MyBtnArray(i).RegistButton(UserForm1.Controls("CommandButton" & i), i)を使えるんだけど、
        ' シート上の場合は無理っぽい
        Call MyBtnArray(1).RegistButton(CommandButton1, 1, OLEObjects("Label1"))
        Call MyBtnArray(2).RegistButton(CommandButton2, 2, OLEObjects("Label1"))
        Call MyBtnArray(3).RegistButton(CommandButton3, 3, OLEObjects("Label1"))
    End Sub

 

2-3. シート画面はこれね

コントロール配列に使うシート

 

注目ポイント

  • Labelが1個
  • 初期化のためだけのボタン1個
  • 最初に初期化ボタンクリックしてね
  • 初期化ボタンのクリックでコントロール配列作ります
  • 実行ボタン3個
  • 実行ボタンがコントロール配列になります
  • 実行ボタンをクリックしたら、Labelにクリックしたボタンの番号を表示
  • 各ボタンのクリックイベントではなく、全ボタンがクラスにあるクリックイベントを共通で使っていることに注意

 

3. 部品の数は未定(動的に追加する)、ユーザーフォーム使用

部品の数が動的に増えたり減ったりする場合もあるかも知れません

そんな時でもコントロール配列はできますっ!!

3-1. まずはコントロール配列を実現するクラス

このクラスでコントロール配列を実現します

このクラスをコントロール配列にしたい部品が動的に増えるたびにNewして、そのインスタンスに動的に増えた部品を登録することでコントロール配列が実現できます

 

''**************************************************************
''
'' コントロール配列を実現するクラス(ComandButtonのみ対応)
''
''   登録したコマンドボタンのイベントハンドラがここにある
''
''**************************************************************
Option Explicit

''**************************************************************
'' このクラスのプロパティ
''**************************************************************
' このクラスに登録したボタン
Private WithEvents TargetBtn As MSForms.CommandButton
Attribute TargetBtn.VB_VarHelpID = -1
' このクラスに登録したボタンの番号(どのボタンをクリックしたのか知るために必要)
Private TargetIndex As Integer


''**************************************************************
'' ボタンの登録
''**************************************************************
Public Sub RegistButton(regButton As MSForms.CommandButton, Index As Integer)
    Set TargetBtn = regButton
    TargetIndex = Index
End Sub

''**************************************************************
'' クリックイベントのハンドラ
'' 登録したボタン全てがこのイベントハンドラを使う
''**************************************************************
Private Sub TargetBtn_Click()
    Call MsgBox("ボタン=" & TargetBtn.Caption & ":index=" & TargetIndex)
End Sub

 

1-2. コントロール配列にしたい部品が乗ってるユーザーフォーム

ユーザーフォームの初期表示は何も無いです

ユーザーフォームをクリックするたびにボタンが動的に追加されて、このクラスがNewされて、動的追加されたボタンがこのクラスに登録されます

 

Option Explicit

'' コマンドボタンを登録するクラスの配列
'' ボタンは動的に追加していく
Dim indexArrayButton As Integer
Dim arrayButton() As Class1


''**************************************************************
'' UserFormのクリックイベント
'' ※クリックするとボタンが1個動的に追加される
''**************************************************************
Private Sub UserForm_Click()
    ' 新たに追加するボタン
    Dim newButton As MSForms.CommandButton
    
    ' ボタン配列(arrayButton)の現在のインデックスを次へ進める
    indexArrayButton = indexArrayButton + 1
    
    '---------------------------------------------------------------
    ' ユーザーフォームにコマンドボタンを追加する
    '---------------------------------------------------------------
    ' まずは追加するボタンを作成
    Set newButton = UserForm1.Controls.Add("Forms.CommandButton.1", , True)
    ' キャプションとかユーザーフォームでの追加位置とか指定
    With newButton
        .Caption = "追加したボタン" & indexArrayButton
        .Top = 10 + (indexArrayButton - 1) * 25
        .Left = 10
        .Height = 20
        .Width = 150
    End With
    
    ' ボタン配列(arrayButton)のサイズを作り直し
    ' ※Preserve指定してるので既存のデータは保持される
    ReDim Preserve arrayButton(1 To indexArrayButton)
    
    ' 配列数が1個増えただけになる
    ' その増えた1個は空っぽなので新たなClass1のインスタンスを入れておく
    Set arrayButton(indexArrayButton) = New Class1
    
    ' その増えた新たなClass1のインスタンスにコントロール配列とするボタンを追加
    Call arrayButton(indexArrayButton).RegistButton(newButton, indexArrayButton)
    
End Sub

 

1-3. ユーザーフォーム画面はこれね

コントロール配列に使うユーザーフォーム

何も無いですが、フォームをクリックしたらボタンが増えます

 

  • 初期表示では何も無し、ユーザーフォームが表示されるだけ
  • ユーザーフォームのどっかをクリックするとボタンが動的に追加される
  • 動的に追加されたボタンがコントロール配列になります
  • ボタンをクリックしたら、ボタンの番号をメッセージボックスで表示
  • 各ボタンのクリックイベントではなく、全ボタンがクラスにあるクリックイベントを共通でつかっていることに注意

 

1-4. ユーザーフォームを呼び出すボタンだけを乗せたシート

Option Explicit

Private Sub CommandButton1_Click()
    UserForm1.Show
End Sub

 

注目ポイント

  • 動的に追加されたボタンがコントロール配列になります
  • ボタンをクリックしたら、Labelにクリックしたボタンの番号を表示
  • 各ボタンのクリックイベントではなく、全ボタンがクラスにあるクリックイベントを共通で使っていることに注意

 

広告




 

さいごに、

VBAは書きやすいし結構便利で強力なので僕は結構好きですね

 

とは言え、言語仕様が古いままで改善されていないので不満は沢山あります
VB(Visual Basic)と同じにしてくれたらいいんですけど、VBとVBAは微妙に違うんですよね

 

その内の一つがコントロール配列です

コントロール配列が使えれば結構ラクにプログラミングできるのに・・・

なんでVBAはでけへんねーーーーーーんっ!!!!!!

 

ってよく思うんですけど、この記事のやり方を使えばできるんですよ!

 

ぜひコントロール配列を使ってみてください!!

 

 


 

プログラミング のレッスンに興味がある方、レッスン内容を聞いてみたい方、なんなりとお問い合わせください。
無料体験レッスンもありますのでお気軽にどうぞ!!!

 

 

次の記事はコチラ
VBAでコントロール配列

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

ABOUTこの記事をかいた人

Windows/Mac/Linuxを使う現役システムエンジニア&プログラマ。オープン系・組み込み系・制御系・Webシステム系と幅広い案件に携わる。C言語やC#やJava等数多くのコンパイラ言語を経験したが、少し飽きてきたので、最近はRubyやPython、WordPressなどのWeb系を修得中。初心者向けのプログラミング教室も運営中。オンライン・対面・出張等でプログラミングをレッスンします。