以下の記事では、静的配列を使ってデータに必要な列が重複なく存在することを確認するマクロを作成しました。
auroralights.jp
ただし、上記の記事のマクロではチェックする『必要な列』をVBAのコードに直接記載しているため、チェックする列名が変わったり、チェックする列数が増えたりする都度コードを変更する必要がありました。
今回は動的配列を使って『必要な列』を別シートから取得するようにして、コードを変更せずにチェックする列名・列数の変更に対応するマクロを作成します。
【目次】
目的
シートに必要な列が重複せずに存在するかどうかを確認できるマクロを作成することが目的です。
以下のように「data」シートと「check」シートがあり、「data」シートにはチェックするデータがあります。
「check」シートのA列には「data」シートに存在していることを確認する必須の列名を入力します。
マクロを実行すると「check」シートのA列の列名が「data」シートにあることを確認します。
<データの定義>
- 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
以下のようなメッセージを表示します。
あとがき
今回は動的配列の練習もかねて、あえて配列を使ってVBAを書きました。
よくよく考えると、わざわざ配列に調べる必須列を格納しなくても、checkシートのA列の値とdataシートの列名を照らし合わせた方がシンプルで可読性もよくなると思いました。
以上、お読みいただきありがとうございました。