こんにちは。ナガオカ（@boot_kt）です。
VBAでコントロール配列です
VBやC#だとコントロール配列が使えるんですけど、VBAは無いんですよね
が、しかしっ！！！
クラスを使えば実現できるんすよ～！！！
サンプルをアップしますので、ぜひ参考にしてください！
この記事のザックリした内容
◆対象読者
- VBAでコントロール配列を実現したい人
◆解決できるかも知れないお悩み
- シート上に貼り付けた部品でも実現可能だけど、結構面倒なので要注意！！
- 基本的にはユーザーフォームを使用する方がいいでしょう
◆どうやって解決するか
- 標準機能としてはVBAにコントロール配列はありません
- しかし、クラスとWithEventsを使うことにより、コントロール配列を実現します
Contents
合わせて読みたい
出典元、これ大事！！！
この記事の出典元はかなり古い記事です
VisualBasicマガジン2001/01月号 の特集の中の一つ、「クロスプログラミングを自家薬籠中のものに クラスモジュールを使った Excel VBA プログラミング」
Excel VBA の世界では知らない人はいないんじゃないの？ってぐらい有名な大村あつしさん
VisualBasicマガジンはもう発行されていません
当時働いていた会社が毎月購入してて、それを読んでおりました
この記事を読んだ時「素晴らしい！！！」と思って、記事だけコピーして手元にとっておりました
それから19年もの間紛失することなく大切にとっておりました！！！
GitHubに置いてます
全てのソースコードはコチラ ↓
Excelファイルも置いてます
xlsmファイルなので、実行する場合は自己責任でね♪
今回のサンプルの種類
- 部品の数は固定、ユーザーフォーム使用
- 部品の数は固定、ユーザーフォーム未使用
- 部品の数は未定（動的に追加する）、ユーザーフォーム使用
今回のコントロール配列のやり方はユーザーフォーム上の部品に対しての方がやり易いです
シート上に貼り付けた部品は色々と面倒です
ちなみに今回のサンプルでコントロール配列にしている部品はボタンです
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
'' コマンドボタンを登録するクラスの配列
'' 今回は３個固定
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
'' コマンドボタンを登録するクラスの配列
'' 今回は３個固定
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はでけへんねーーーーーーんっ！！！！！！
ってよく思うんですけど、この記事のやり方を使えばできるんですよ！
ぜひコントロール配列を使ってみてください！！
プログラミング のレッスンに興味がある方、レッスン内容を聞いてみたい方、なんなりとお問い合わせください。
無料体験レッスンもありますのでお気軽にどうぞ！！！
次の記事はコチラ
