カテゴリー: ExcelVBA

まず、この画像のような表があるとします
毎日なんらかの確認作業を行い、確認完了後にチェックを付ける表です

単純に○を入力するだけのなんてことない表ですが、これが項目数が大量にあった場合は結構大変になります
画像は結果表示なので一気に入力されてますが、実際は1つずつ入力する必要があります

そこで、セルをダブルクリックするだけでチェックを入力して、間違った時のことも考えて、ダブルクリックで○と空白を切り替えるようにします
こうすると、結構入力が楽になります

まずは、処理上選択されているセル範囲のみで実行する必要があります
見出しや日付のセルに○が入力されると困ります

名前定義セルの作成

処理したいセル範囲を選択して、数式タブにある名前の管理ボタンからセル範囲に名前定義を作成します

名前の管理画面から新規作成ボタンを選択します

表示された新しい名前画面から、新規作成内容を設定します
名前はなんでも構いませんが、範囲はシート範囲にするようにしてください

シートを複製しても処理が問題なく動作できるように、名前定義の範囲を限定しておきます

シートイベントの作成

シートのイベントプロシージャで処理を作成していきます

今回の処理では、ユーザーの意思による操作で誤操作の可能性の低いダブルクリックを選択します
ダブルクリックは、ワークシート上では編集モードに入る操作なので無意識にしてしまうことは少ないのでこういった処理に向いています

ダブルクリックをしたときに発生するイベントが「BeforeDoubleClick」イベントになりますのでシートモジュールでプロシージャボックスから選択して作成してください

今回作成したコードがこのような形になっています

コード解説

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Range("チェック範囲"), Target) Is Nothing Then
        Cancel = True
        If Target = "○" Then
            Target.ClearContents
        Else
            Target = "○"
        End If
    Else: End If
End Sub

このコードでは、ダブルクリックされたセルが指定範囲であれば入力処理を実行します
入力処理では、空白セルには○を入力し、○が入力されていれば消去する処理です

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

シートイベントです。
シートのセルをダブルクリックした後に発生するイベントで、引数Targetにダブルクリックしたセルが代入されます

Targetにはセル範囲が代入されますので、単一セルとは限りません
ですが、ダブルクリックはExcelの仕様上必ず1セルに限定されます
この仕様からもダブルクリックイベントは使いやすいです

    If Not Intersect(Range("チェック範囲"), Target) Is Nothing Then

～～　中略　～～

    Else: End If

        Cancel = True

このコードを実行すると、既定の動作をキャンセルします
イベントによって規定の動作は変わります

ここではセルの編集モードに入るのをキャンセルします
最後に解説しますが、入力モードに入られると非常にわずらわしいです

        If Target = "○" Then
            Target.ClearContents
        Else
            Target = "○"
        End If

最後に入力処理になります

If分岐でセルに○が入力されているかで分岐します
○が入力されていれば、ClearContentsメソッドにより入力内容を消去します
○以外であれば、○を入力します

○以外の別の文字列でも○を入力します
これはCOUNTIF関数を使用したことがある人は分かるかもしれませんが、○をカウントするのに別の文字を入力されていてうまく個数が取得できないことがあります
そういったことを回避する意味でも指定文字以外なら指定文字にしてしまいます

動きの解説

まずは空白セルに○を入力する動きです
セル範囲が処理実行可能範囲なので、処理が実行されます
またCancelにTrueを代入することで、ダブルクリックしたセルが編集モードに入りません

空白セルで○以外セルなので、○が入力されてい処理が完了です

今度は○が入力されているセルをダブルクリックしたときの動きです

今回は○が入力されているため、そのセルは空白になります

最後に処理が許可されていない範囲をダブルクリックしたときの動きです

日付セルは名前定義したセルとは重複していないため、最初のIf分岐で何も処理を実行しない形になっています

このとき、Cancelも変更されていないため、通常の操作通りセルの編集モードに入っていることが確認できます

この処理は、入力処理ですが非常に応用範囲が広い内容です

例えばセルへのデータ入力ではなく書式設定の変更を行えばダブルクリックでセルの背景色を切り替えたりできます
ユーザーフォームの表示処理を行うことも可能です、この場合表示ボタンが必要なくなります

まずは入力処理から行って、動きが確認できたら色々な設定変更を行ってみてください

'アクティブセルがB列か判定
If Not Intersect(Range("B:B"), ActiveCell) Is Nothing Then
Debug.Print "範囲内"
Else: End If

任意のセル範囲が指定セル範囲に含まれているかを調べるにはIntersectメソッドを使用します

コード解説

任意のセル範囲とは、ユーザーが選択しているセル範囲であったりする
動的に変化する指定セル範囲のことになります
このコードでは、ActiveCellの部分になります

指定セル範囲とは、処理の実行を前提とした固定の指定セル範囲のことになります
ActiveCellがこのセル範囲内にあれば処理を実行するような判定の親要素のセル範囲になります
コードでは、Range(“B:B”)になります

このコードを実行した時点のアクティブセルの位置によって処理が分岐します
引数のセル範囲は順番は関係ありませんので、入れ替えても使用可能です

If Not Intersect(Range("B:B"), ActiveCell) Is Nothing Then

ここで条件分岐を行っています

Intersectメソッドは重複する範囲が存在しない場合は「Nothing」を返します
Nothingは文字列ではないので、比較には「Is」演算子を使用します

これで、重複する範囲が無い時はTrueとなります
ただそれでは処理上少しわかりにくいので、逆説を取って判定します

その為に「Not」演算子を合わせて使用します
これで、範囲内に存在する場合にTrueになります

主な使用場面

ユーザーにセル範囲を指定させて処理を実行するような場合には、必ず使用します

データを入力したり、背景色を変更したりするような処理であった場合に、見出しのセル範囲であったり、関数の入力されたセルを指定されては困ります
そういった場合に、処理の実行前にこのコードで実行可否を判定します

また、Range(“B:B”)の部分を名前定義セルにすれば複雑なセル範囲も簡単に指定できますのでおすすめです

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

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

そんな状態で、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バイト以上の文字があった場合は別の対応が必要になります
ただ、この対応はかなり稀でありどこまでのバイトを対応するかも難点です