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

お知らせ

>> プログラミングレッスンやってます！　無料体験レッスンの詳細はこちら

こんにちは。ナガオカ（@boot_kt）です。

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

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

が、しかしっ！！！

クラスを使えば実現できるんすよ～！！！

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

この記事のザックリした内容

◆対象読者

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

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

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

◆どうやって解決するか

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

Contents

出典元、これ大事！！！
GitHubに置いてます
今回のサンプルの種類
1. 部品の数は固定、ユーザーフォーム使用
2. 部品の数は固定、ユーザーフォーム未使用
3. 部品の数は未定（動的に追加する）、ユーザーフォーム使用
さいごに、
- こちらの記事もあなたにオススメです！

初心者向けのプログラミングレッスンをしております。プログラミングを始めてみたい方、まずはお気軽に無料体験をお試しください！

合わせて読みたい

>> ExcelからSQLiteを使う方法

>> [Excel/VBA]ファイル/フォルダ一覧を再帰的に取得してxlsをxlsxに変換する！

>> [Excel/VBA]ファイルが開いたことを検出する方法

出典元、これ大事！！！

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

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

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

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

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

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

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

GitHubに置いてます

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

>> 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はでけへんねーーーーーーんっ！！！！！！

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

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

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

プログラミングレッスンを無料体験してみたい方は、
このボタンをクリック！

次の記事はコチラ

メニュー

最新の記事

出典元、これ大事！！！

GitHubに置いてます

今回のサンプルの種類

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

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

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

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

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

注目ポイント

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

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

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

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

注目ポイント

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

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

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

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

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

注目ポイント

さいごに、

こちらの記事もあなたにオススメです！

コメントを残す コメントをキャンセル

RECOMMENDこちらの記事も人気です。

Excelなのに超簡単に２枚の画像を合成できるってマジすか！？

[Excel]あなたにも作れる！ ワークシート関数を自作する！！

Excel方眼紙公開討論会 presented by Forguncyで…

Excelは画像編集ソフトとしてもそこそこ優秀なのだっ！！

[Excel/VBA]プログラミングなんて全く分からない人でも、なんとか…

教えてほしい。Excel方眼紙って何がそんなに悪いの？ ホントのちゃんと…

[Excel/VBA]ファイル/フォルダ一覧を再帰的に取得してxlsをx…

ExcelからSQLiteを使う方法

ABOUTこの記事をかいた人

NEW POSTこのライターの最新記事

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

C#でプラグイン機能を実装しよう！

[mac][Python]Pygameをインストールする

【合格への道】Python 3 エンジニア認定基礎試験

プログラミングレッスンの生徒様の声

広告

プログラミングレッスン

サイト内検索

Feedly

このブログを書いている人

Twitter

Facebookページ

直近24時間でよく読まれている記事

アーカイブ

カテゴリー

コメントを残すコメントをキャンセル

[Excel]あなたにも作れる！　ワークシート関数を自作する！！

教えてほしい。Excel方眼紙って何がそんなに悪いの？　ホントのちゃんと…