投稿者: tanakadaigoro

埋め込みコントロールはワークシートにコントロールを作成したものです
このコントロールはワークシートの広さから、往々にしてコントロールの数が多くなります

しかも、ほとんど同じ動きをさせることも多い場合があります

そんな状態で、1つ1つのコントロールにイベント処理を何個も作成していくのは、大変だし、メンテナンス性も劣化します

クラスモジュールを作成する

クラスモジュールが作成出来たら、そのモジュール内にコードを作成します

画像の様なコードを作成しました
プロジェクトウィンドウの青くなっている「Class1」モジュール内です

Dim WithEvents 作成 As MSForms.OptionButton

Sub 紐付け(作成コントロール As MSForms.OptionButton)
    Set 作成 = 作成コントロール
End Sub

Private Sub 作成_Change()
    If 作成.Value = True Then
        Selection.Interior.Color = 作成.BackColor
    Else: End If
End Sub

こちらは、クラスモジュール内に作成するコードなので、OptionButtonの取得であればコピペしてそのまま使用できます

Dim WithEvents 作成 As MSForms.OptionButton

この1行が集約するコントロールを代入させる変数の宣言です
WithEventsを使用することにより、イベントの作成が可能になります

MSForms.OptionButtonはオプションボタンのコントロールです
変数の型指定です、Object型は指定できません

Sub 紐付け(作成コントロール As MSForms.OptionButton)
    Set 作成 = 作成コントロール
End Sub

ここで紐付け処理を作成しています

後述で出てくる紐付け作業の時に、実行されるプロシージャです
中身は引数のコントロールを上記の変数に代入させているだけです
ただ、これだけで以降は後述のイベント処理が実行されます

Private Sub 作成_Change()
    If 作成.Value = True Then
        Selection.Interior.Color = 作成.BackColor
    Else: End If
End Sub

イベント処理です
内容は、OptionButtonがON（True）ならコントロールの背景色を選択範囲に適用するだけです
ここに作成したイベントが紐付けされたコントロール全てで実行されます

もし、イベント処理に変更があった場合はここの処理を変更すればいいだけです
コントロールの数が10個あろうが100個あろうが、ここで全てが集約されています

考えるだけで便利な話です

ここまでで、クラスモジュールの作成は完了です

シートモジュールの処理作成

クラスが作成出来たら、それを使用してシートモジュールに処理を作成します

画像の様なコードを作成しています
モジュールは青色になっている、「Sheet1」モジュールになります

Dim 動的作成() As New Class1

Private Sub Worksheet_Activate()
    Dim 取得用 As Shape, インデックス As Long
    For Each 取得用 In ActiveSheet.Shapes
        If 取得用.Name Like "*OptionButton*" Then
            ReDim Preserve 動的作成(インデックス)
            動的作成(インデックス).紐付け OLEObjects(取得用.Name).Object
            インデックス = インデックス + 1
        Else: End If
    Next 取得用
End Sub

Private Sub Worksheet_Deactivate()
    Erase 動的作成
End Sub

Private Sub CommandButton1_Click()
    Selection.Interior.ColorIndex = 0
    Erase 動的作成
End Sub

こちらは、コントロールの名前が違っていると使用できませんのでコピペする場合は修正をしてください

Dim 動的作成() As New Class1

クラスを使用する場合には、クラスの作成が必要になります
この1行が、クラスの作成とその変数の宣言になります

コントロールの個数分この変数が必要になるため、変数は動的配列で作成します
またNewキーワードによりクラスがここで作成されています

Private Sub Worksheet_Activate()
    Dim 取得用 As Shape, インデックス As Long

シートのActivateイベントを使用します、シートがアクティブ状態になったときに発生するイベントです

まずは使用する変数の宣言です
「取得用」はシートに配置されたコントロールをループ処理で代入させるための変数です。配置されたコントロールは全てShapesオブジェクトとなるため、型指定はShape型を使用します。ここには図形なんかも入ってきます

「インデックス」は動的配列のインデックス番号用の数値で使用します。整数なのでLong型を使用します

    For Each 取得用 In ActiveSheet.Shapes

～～　中略　～～

    Next 取得用

次にシートに配置されたコントロールの取得処理に入ります
ここではシート上のShapesオブジェクトをForEachループで1つずつ検証しながら取得を行っていきます

変数の取得用に各オブジェクトが代入されます

        If 取得用.Name Like "*OptionButton*" Then

～～　中略　～～

        Else: End If

オブジェクトの取得ができたら、そのオブジェクト名で取得可否の条件分岐を行います

If分岐で文字列比較を行います
今回はオプションボタンを取得するので、名前に「OptionButton」が含まれていたら処理を中略の処理が実行されます

含まれていなければ何も行いません

            ReDim Preserve 動的作成(インデックス)

取得可能なコントロールが見つかったら、まずは動的配列を追加します
もちろんそれまでに取得したコントロールを削除されていは困りますのでPreserveキーワードを使用してください

変数インデックスはあとで加算しています

            動的作成(インデックス).紐付け OLEObjects(取得用.Name).Object
            インデックス = インデックス + 1

ついにクラス定義の出番です
ここまで長かったです、書いてる自分もお尻が痛いです

まず、クラス宣言した変数名ではクラスモジュールで作成したメソッドやプロパティが使用できます

今回は、紐付けという処理を作成しました
それはメソッドという形でここで使用することが出来ます
変数に対するメソッドなので、「.」を付けることでインテリセンス入力できます

これはCallによる処理の呼び出しでは無いので、引数に「()」は必要ありません
半角スペースを入れてから、引数を指定します

今回は「OLEObjects(取得用.Name).Object」となっています
まずもって、そのままでは取得できません
変数「取得用」がShape型だからです

なので、コントロールとしてのオブジェクトで指定する必要があります
OLEObjectsはシートにあるコントロールのコレクションです
その中から取得用.Nameで指定することで取得しているコントロールを指定することができます
指定したコントロールのObjectプロパティを指定することで最終的にコントロールが指定される状態になります

この時点でクラスの紐付けプロシージャが実行されて、クラスで宣言した変数「作成」に代入されます

ここ以降にクラスで作成したイベントがこのコントロールで実行されるようになります

紐付けが出来たら、変数「インデックス」に1を加算します

ここまでで、処理の作成が完了です、おっつかれした～

処理の動きと紐付けの解除

まずは、シートのアクティブイベントを実行する必要があります

Sheet2からSheet1への切り替えを行っています
この時点でOptionButtonのクラスの紐付けが完了しています

実際にOptionButtonを切り替えてみると、選択範囲の背景色がOptionButtonのONのものと同じに切り替わっています

OptionButtonが増えたとしても、問題は無いしコントロールの背景色を変更すれば変更後の色指定も合わせて行えます

Private Sub Worksheet_Deactivate()
    Erase 動的作成
End Sub

また、このDeactivateイベントにより動的配列変数を解放しています
このイベントはシートがアクティブ状態でなくなったときに発生するイベントなので、別のシートに移動したら紐付けを解除する動きになります

状況によっては必要ないかもしれません

Private Sub CommandButton1_Click()
    Selection.Interior.ColorIndex = 0
    Erase 動的作成
End Sub

解放自体をユーザーにゆだねることも出来ます
その為にコマンドボタンを1つ作成しています

コマンドボタンをクリックすると、背景色を塗りつぶし無しに設定します
その後、動的配列変数を解放しています

画像の動きを確認してください

ボタンをクリックすることで、背景色が無くなり、Redのオプションボタンに切り替えても背景色は塗られることはありません
イベントが発生していないことが確認できます

動的配列でメモリ上に乗っかったままなので、基本的には必要ない時は解放してあげるほうが良いです

複数のコントロール

単一のコントロールだけであれば、全く問題にならないので普通にシートモジュールに作成すればいいです

この記事でもありますが、複数のコントロールになるとこのクラスを使用する方法しかありません

クラス自体は、使用頻度が非常に低く無くても処理作成を行えることが多いため
初心者では踏み込みづらい内容かもしれません

なので、たくさんのコントロールのイベント処理を一括して管理するにはクラス使わなあかん、とだけ認識しておいてください

作成して、紐付けする

画像の様なコードを作成しています

Dim WithEvents 動的作成 As MSForms.TextBox

最初にWitheventsを使用した変数宣言があります
これに代入するテキストボックスに処理を作成できるようになります

Private Sub 動的作成_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
動的作成.BackColor = RGB(0, 200, 0)
End Sub

ここで、動的に作成するコントロールのイベント内容を作成しています
内容としては、マウスカーソルがコントロール上で移動すると背景色を緑色に変更するだけの処理です

Private Sub CommandButton1_Click()
Set 動的作成 = Me.Controls.Add("Forms.TextBox.1", "")
End Sub

ここで、コマンドボタンのクリックによりテキストボックスを作成しています
プロパティ設定は何も行っていないので、作成しているだけです

それと同時に、動的作成に代入を行っています
この代入により、Witheventsで作成した変数と紐付けされるので、以降で作成したイベントが発生するようになります

画像を確認してください、コマンドボタンをクリックするとテキストボックスが作成されます

そのテキストボックスにマウスを移動させると、背景色が緑色に変化します
イベントが実行されていることがわかります

動的に作成したコントロールには、こうして処理を作成しておき
実際に作成された後に紐付け処理を行うことで、使用することができるようになります

紐付けの解除

Set 動的作成 = Nothing

紐付けには変数に代入することで行いました

なので、紐付けの解除は変数を解放してあげればいいだけです
このコードを解除したいタイミングで実行すれば可能です

赤枠内に解除用コードを挿入しました
上記のコードに、紐付け直後に解除する動きになっています

画像のように、テキストボックス作成後にマウスをコントロール上に移動させても背景色が変化しません
イベントが発生していないことがわかります

この仕様をうまく使えば、ユーザーフォームでは使用できないApplication.EnableEventsプロパティの代用ができます

単体でのみ有効な処理

この処理は変数が1つしかありませんので、単体のコントロールでのみ有効な処理です

変数を増やせばいいだけと考えてしまうかもしれませんが、その分イベント処理の作成も同時に増加していきます
こうなると、もはやなんのための処理か分からなくなります

イベント処理は1つで集約して、代入する変数を増やすには、クラスが必要になります
クラスを使用することで、例でいう「動的作成」という変数を配列で指定することが出来るようになります

Excelのイベントであったり、作成するコントロールが単体であればクラスまで使わなくてもこの記事の内容で対応可能です
可読性もこちらのほうが良いので、場面で使い分けるようにすればいいです

’半角の存在確認
Dim 対象文字列 As String
対象文字列 = "ナンバー32"

If LenB(対象文字列) > LenB(StrConv(対象文字列, vbFromUnicode)) Then
Debug.Print "含まれています"
Else
Debug.Print "含まれていません"
End If

コード解説

Dim 対象文字列 As String
対象文字列 = "ナンバー32"

ここでは、引数の文字列が後で複数回でてくるので変数に代入しています

If LenB(対象文字列) > LenB(StrConv(対象文字列, vbFromUnicode)) Then

ここで判定処理を行っています、If分岐を使用しています

LenB(対象文字列)

左辺では、LenB関数によりバイト数を取得しています
VBAでは文字は2バイトを返します

LenB(StrConv(対象文字列, vbFromUnicode))

右辺では、文字形式を変換して半角を1バイトに表現してバイト数を取得しています

この2つの比較により、LenB関数単体より少なければ含まれていることが分かります

If LenB(対象文字列) > LenB(StrConv(対象文字列, vbFromUnicode)) Then
Debug.Print "含まれています"
Else
Debug.Print "含まれていません"
End If

→→→　含まれています

例にある「ナンバー32」という文字列に対して、コードを実行すると「含まれています」が返されます
「32」が半角のため、バイト数が一致しないからです

これは1バイトと2バイト文字という表現に対して有効な処理です
3バイト以上の文字があった場合は別の対応が必要になります
ただ、この対応はかなり稀でありどこまでのバイトを対応するかも難点です