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

【目次】

※2021/10/12：列の重複の観点が漏れていたのでコードを修正しました。

以下の記事では動的配列を使って、必要な列が存在するかどうか確認するマクロを紹介しています。
auroralights.jp

目的

以下の画像のようなデータの1行目に必要な列が存在するかどうか確認することが目的です。

応用として、データ集計などのVBAツールを作成する際に、元データに必要な列があるかどうかをチェックするための機能に使えたら良いなと思っています。

＜データの定義＞

• データはVBAを記述するブックと同じブックのSheet1に存在します。
• 1行目がデータの列名になります。
• データはA1が開始点となります。
• 今回は「受注番号」「商品コード」「受注日」「納入日」「見積番号」「担当者」の6つの列が存在することを確認します。

必要な列があるかどうか確認するVBAのコード

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

Option Explicit

Sub checkCol()

    '配列に必要な列名を格納
    Dim arrCol(5) As String
    arrCol(0) = "受注番号"
    arrCol(1) = "商品コード"
    arrCol(2) = "受注日"
    arrCol(3) = "納入日"
    arrCol(4) = "見積番号"
    arrCol(5) = "担当者"
        
    '最終列の取得
    Dim maxCol As Long: maxCol = Cells(1, Columns.Count).End(xlToLeft).Column
    
    'for文用カウント変数i、j、列の存在・重複確認用の変数k、メッセージ用の変数msgの宣言
    Dim i As Long, j As Long, k As Long
    Dim msg As String
    
    '配列の要素数だけ、A列～最終列のチェックを繰り返す
    For i = LBound(arrCol) To UBound(arrCol)
        For j = 1 To maxCol
            If Cells(1, j) = arrCol(i) Then
                k = k + 1
            End If
        Next j
        
        If k = 1 Then
            msg = msg & arrCol(i) & ":OK" & vbCr
        ElseIf k < 1 Then
            msg = msg & arrCol(i) & "が存在しません" & vbCr
        Else
            msg = msg & arrCol(i) & "が" & k & "列存在。重複しています。" & vbCr
        End If
        k = 0
    Next i
    
    '結果の表示
    MsgBox msg
    
End Sub

考え方

■配列への必要な列名の格納
必要な6つの列名(「受注番号」「商品コード」「受注日」「納入日」「見積番号」「担当者」)を「arrCol」という配列に格納します。

■最終列数の取得
データの最終列(右端の列数)を「maxCol」という変数に格納します。

■for文用カウント変数i、j、列の存在・重複確認用の変数k、メッセージ用の変数msgの宣言
for文用の変数i、j、列の存在・重複確認用の変数k、メッセージ用の変数msgを宣言します。

■for文で配列の要素数分、A列～最終列のチェックを繰り返す
配列arrCol(0)に格納されている列名「受注番号」がSheet1の1行目のA列～最終列までに存在する数だけ変数「k」に1をプラスします。
(存在しなければ1はプラスしません)

kが1ならOK、kが0ならarrCol(0)の列名が存在しない、それ以外ならarrCol(0)の列名が重複しているとメッセージ用の変数msgに格納し、kに0を再設定します。

同じようにarrCol(1)~arrCol(5)に格納されている列名が、1行目のA列～最終列までに存在するかどうかをチェックして、変数msgに結果を継ぎ足します。

■結果の表示
配列arrColに格納したすべての列名が1つずつ存在する場合は、以下のようなメッセージが表示されます。

配列arrColに格納した列名で、重複する列名や存在しない列名がある場合は、以下のようなメッセージでどの列が重複、存在していないかわかるようにしました。

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