オーロラさんの勉強帳

IT企業勤務。データベース、Excel、Excel VBA、ネットワーク、LinuxなどIT関連のことを主に書いていきます。少しでもお役に立てたら幸いです。

【Excel VBA 練習】必要な列が重複なく存在することを確認するマクロ ~動的配列の練習~

以下の記事では、静的配列を使ってデータに必要な列が重複なく存在することを確認するマクロを作成しました。
auroralights.jp
ただし、上記の記事のマクロではチェックする『必要な列』をVBAのコードに直接記載しているため、チェックする列名が変わったり、チェックする列数が増えたりする都度コードを変更する必要がありました。
今回は動的配列を使って『必要な列』を別シートから取得するようにして、コードを変更せずにチェックする列名・列数の変更に対応するマクロを作成します。



【目次】

目的

シートに必要な列が重複せずに存在するかどうかを確認できるマクロを作成することが目的です。
以下のように「data」シートと「check」シートがあり、「data」シートにはチェックするデータがあります。
「check」シートのA列には「data」シートに存在していることを確認する必須の列名を入力します。
マクロを実行すると「check」シートのA列の列名が「data」シートにあることを確認します。
f:id:auroralights:20211014002025p:plain


<データの定義>

  • dataシートには、チェックするデータが入力されている
  • checkシートのA列2行目以降にdataシートに存在するかどうかを確認する必須の列名が入力されている
  • checkシートのA列の必須列名の数は可変である

必要な列が重複せずに存在するかどうかを確認するVBAのコード

以下のようなコードを書きました。

Sub checkCol2()

    'dataシート、checkシートをオブジェクト変数に格納
    Dim wsData As Worksheet
    Dim wsCheck As Worksheet
    Set wsData = ThisWorkbook.Worksheets("data")
    Set wsCheck = ThisWorkbook.Worksheets("check")

    '配列に格納する必須列名の要素番号を取得する
    'checkシートの見出し行を除く、配列は0から始まるため、A列の最終行-2で求める
    Dim checkCol As Long
    checkCol = wsCheck.Cells(Rows.Count, 1).End(xlUp).Row - 2
    

    '動的配列の宣言と必須列名の格納
    Dim arrCheckCol() As String
    ReDim arrCheckCol(checkCol)
    
    'For文用の変数宣言
    Dim i As Long
    Dim j As Long: j = 0
    
    '配列に必須列名を格納する
    For i = 2 To wsCheck.Cells(Rows.Count, 1).End(xlUp).Row
        arrCheckCol(j) = wsCheck.Cells(i, 1)
        j = j + 1
    Next i

    'dataシートの最終列の取得
    Dim maxCol As Long: maxCol = wsData.Cells(1, Columns.Count).End(xlToLeft).Column

    'dataシートに必須列名が重複なく存在するかどうかチェック
    Dim x As Long
    Dim y As Long
    Dim z As Long
    Dim msg As String
    
    '配列の要素数だけループ
    For x = LBound(arrCheckCol) To UBound(arrCheckCol)
        'A列から最終列までループ
        For y = 1 To maxCol
            If wsData.Cells(1, y) = arrCheckCol(x) Then
                z = z + 1
            End If
        Next y
        
       '重複・存在結果を格納
        If z < 1 Then
            msg = msg & arrCheckCol(x) & "列がありません" & vbCr
        ElseIf z > 1 Then
            msg = msg & arrCheckCol(x) & "列が" & z & "列存在。重複しています" & vbCr
        Else
            msg = msg & arrCheckCol(x) & "列:OK" & vbCr
        End If
        z = 0
    Next x

    MsgBox msg
    
End Sub

考え方

■オブジェクト変数wsData、wsCheckの宣言
「wsData」、「wsCheck」にマクロを記述しているブックの「data」シート、「check」シートを格納します。

    Dim wsData As Worksheet
    Dim wsCheck As Worksheet
    Set wsData = ThisWorkbook.Worksheets("data")
    Set wsCheck = ThisWorkbook.Worksheets("check")


■配列に格納する必須列名の要素数の取得、動的配列の宣言
「check」シートのA列に必須列名が記載されています。
最初の1行は列見出しのため不要。さらに配列は0から始まるので、「check」シートの最終行数-2で配列に指定する最大要素数を取得しました。

arrCheckCol()という動的配列を宣言し、ReDimでarrCheckColの要素数を指定します。
要素数には、checkシートの最終行数-2の値を格納している変数checkColを使います。

    '配列に格納する必須列名の要素番号を取得する
    'checkシートの見出し行を除く、配列は0から始まるため、A列の最終行-2で求める
    Dim checkCol As Long
    checkCol = wsCheck.Cells(Rows.Count, 1).End(xlUp).Row - 2
    

    '動的配列の宣言と必須列名の格納
    Dim arrCheckCol() As String
    ReDim arrCheckCol(checkCol)


■配列に必須列名を格納する
配列「arrCheckCol」に「check」シートのA列2行目以降の必須列名を格納します。

    'For文用の変数宣言
    Dim i As Long
    Dim j As Long: j = 0
    
    '配列に必須列名を格納する
    For i = 2 To wsCheck.Cells(Rows.Count, 1).End(xlUp).Row
        arrCheckCol(j) = wsCheck.Cells(i, 1)
        j = j + 1
    Next i


■dataシートの最終列の取得
変数maxColに「data」シートの最終列数を格納します。

    'dataシートの最終列の取得
    Dim maxCol As Long: maxCol = wsData.Cells(1, Columns.Count).End(xlToLeft).Column


■dataシートの最終列の取得
配列に格納した必須列名の数だけ、dataシートの1行目のA列~最終列までに必須列名が重複なく存在するかどうかをチェックします。
チェックした結果を変数msgに格納します。

    'dataシートに必須列名が重複なく存在するかどうかチェック
    Dim x As Long
    Dim y As Long
    Dim z As Long
    Dim msg As String
    
    '配列の要素数だけループ
    For x = LBound(arrCheckCol) To UBound(arrCheckCol)
        'A列から最終列までループ
        For y = 1 To maxCol
            If wsData.Cells(1, y) = arrCheckCol(x) Then
                z = z + 1
            End If
        Next y
        
       '重複・存在結果を格納
        If z < 1 Then
            msg = msg & arrCheckCol(x) & "列がありません" & vbCr
        ElseIf z > 1 Then
            msg = msg & arrCheckCol(x) & "列が" & z & "列存在。重複しています" & vbCr
        Else
            msg = msg & arrCheckCol(x) & "列:OK" & vbCr
        End If
        z = 0
    Next x


■Msgboxで結果を出力
メッセージボックスで必須列名が重複なく存在するかどうかを表示します。

    MsgBox msg


以下のようなメッセージを表示します。
f:id:auroralights:20211014192355p:plain

あとがき

今回は動的配列の練習もかねて、あえて配列を使ってVBAを書きました。
よくよく考えると、わざわざ配列に調べる必須列を格納しなくても、checkシートのA列の値とdataシートの列名を照らし合わせた方がシンプルで可読性もよくなると思いました。


以上、お読みいただきありがとうございました。