以下の記事では、静的配列を使ってデータに必要な列が重複なく存在することを確認するマクロを作成しました。
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シートの列名を照らし合わせた方がシンプルで可読性もよくなると思いました。
以上、お読みいただきありがとうございました。
