[VBA] 配列の要素を一括で検証する方法

質問

こちらの識者の方々にはいつもお世話になっています。
VBAの質問です。

環境は下記になります。
OS=windowsXP SP3
Office=Excel2003(11.8347.8403) SP3

会社などで一般的にVBAを使用してデータの処理をする場合、自動で吐き出されるcsvファイルを読み込んで、そのデータを加工し、使いたいデータに成型する。というケースが往々にしてあると思うのですが、吐き出されるcsvファイルのタイトル行が今までのものと同一でない（フィールドが知らないうちに増えたり減ったりしている）場合を想定し、csvファイルを取り込んだ時点でタイトル行の検査をしたいのですが、csvファイルのタイトル行を一旦配列に格納し、あらかじめ用意しておいたタイトル行のデータと比較する場合、配列内の一要素ずつ検査するしかないのでしょうか？
例としてはタイトル行が
"品名", "4月", "5月", "6月", "7月", "8月", "9月"
と仮定し、

Sub test()

　Dim EndClm As Long
　Dim TitleA As Variant
　Dim TitleB As Variant
　Dim i As Long

　EndClm = Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Column
　TitleA = Workbooks("aa.csv").Sheets(1).Range(Sheets(1).Cells(1, 1),Sheets(1).Cells(1, EndClm)) 'csvファイルのタイトル行
　TitleB = ThisWorkbook.Sheets(1).Range(Sheets(1).Cells(1, 1), Sheets(1).Cells(1, EndClm)) '検証用 "品名", "4月", "5月", "6月", "7月", "8月", "9月"

　For i = 1 To UBound(TitleA, 2)
　　If TitleA(1, i) <> TitleB(1, i) Then MsgBox "項目が変更されています"
　Next i

End Sub

のようなコードで1つずつ検証できますが、配列内の要素を一気に検証する方法はありますか？
当然通りませんが
If TitleA <> TitleB Then MsgBox "項目が変更されています"
のような感じです。
短いコードで確実にタイトル行の検査が行われれば、上記の配列に取り込んで要素を検証することに特に頓着はしていません。

今このテストコードを自宅のWindows7,Excel2010で書いていて思ったのですが、TitleA、TitleB共に配列に取り込む際、ブックをアクティブにしないとエラーが起きてしまいます。
もしよろしければこの原因も併せて教えていただいてもよろしいでしょうか。

質問に不備不足等ございましたらご指摘ください。
ご面倒お掛けしますがよろしくお願いします。

回答No.2

cj_mover · ベストアンサー

こんにちは。

まず、修正を加えるなら、こんな感じ。

' ' ///
Sub testA()

　　Dim EndClm As Long
　　Dim TitleA As Variant
　　Dim TitleB As Variant
　　Dim i As Long

　　With ThisWorkbook.Sheets(1)
　　　　EndClm = .Cells(1, Columns.Count).End(xlToLeft).Column
　　　　TitleB = .Cells(1).Resize(, EndClm) '検証用 "品名", "4月", "5月", "6月", "7月", "8月", "9月"
　　End With
　　TitleA = Workbooks("aa.csv").Sheets(1).Cells(1).Resize(, EndClm) 'csvファイルのタイトル行, "7月", "8月", "9月"

　　For i = 1 To EndClm
　　　　If TitleA(1, i) <> TitleB(1, i) Then
　　　　　　MsgBox "項目が変更されています"
　　　　　　Exit For
　　　　End If
　　Next i

End Sub
' ' ///

"短いコード"とは言い難いですが、ループしない方法もある、ということで以下。

' ' ///
Sub testJ()

　　Dim EndClm As Long
　　Dim TitleA As Variant
　　Dim TitleB As Variant

　　With ThisWorkbook.Sheets(1)
　　　　EndClm = .Cells(1, Columns.Count).End(xlToLeft).Column
　　　　TitleB = .Cells(1).Resize(, EndClm) '検証用 "品名", "4月", "5月", "6月", "7月", "8月", "9月"
　　End With
　　TitleA = Workbooks("aa.csv").Sheets(1).Cells(1).Resize(, EndClm) 'csvファイルのタイトル行, "7月", "8月", "9月"

　　If Join(Application.Transpose(Application.Transpose(TitleA)), vbCr) _
　　　　<> Join(Application.Transpose(Application.Transpose(TitleB)), vbCr) _
　　Then MsgBox "項目が変更されています"

End Sub
' ' ///

要するにタイトルすべてを連結した文字列を比較する、という方法です。
単行複列の二次元配列を、ワークシート関数のTRANSPOSEに２回掛けると一次元配列が返ります。
一次元配列をJoin関数で連結した文字列を比較します。
（vbCrを区切り文字に指定するのは、セル内で使わることがない文字だからです。）
実践的には普通にループする方法の方が却って無駄が無いように思いますが、
ループしないことを好む向きもありましょうから、紹介まで。

csvファイルについては、単にテキストファイルとしての扱いが可能ですから、
　　Open "aa.csv" For Input As #1
　　　　Line Input #1, TitleA
　　Close #1
とかで、そのままタイトル行だけcsvテキストを取得しちゃう方が
部分的には簡単で処理も速く、正攻法な気がします。
（タイトル行に引用符を使うようなcsvだとシートとの比較が難しいですけれど）
比較するのが、どちらもcsvという場合なら、かなりの説得力を持って奨められるところですが、
片方がExcelブックだとちょっとアンバランスな漢字がするかも、です。

' ' ///
Sub testC()
'
　　Dim EndClm As Long
　　Dim TitleA As Variant
　　Dim TitleB As Variant
　　Dim nFree As Integer

　　With ThisWorkbook.Sheets(1)
　　　　EndClm = .Cells(1, Columns.Count).End(xlToLeft).Column
　　　　TitleB = .Cells(1).Resize(, EndClm) '検証用 "品名", "4月", "5月", "6月", "7月", "8月", "9月"
　　End With

　　nFree = FreeFile
　　Open "aa.csv" For Input As #nFree
　　　　Line Input #nFree, TitleA
　　Close #nFree

　　If TitleA <> Join(Application.Transpose(Application.Transpose(TitleB)), ",") _
　　Then MsgBox "項目が変更されています"

End Sub
' ' ///

さて、本題の「列内の要素を一気に検証する方法はありますか？」
というご質問について。
単行または単列の二次元配列または一次元配列を
丸ごと比較する機能、についてですが、
（＃最近思いだしたのですが）ユーザー設定のリストを使う方法が簡単といえば簡単です。
但し、Excel2007以降の場合は、Excel97-2003互換ブックからの実行に限定されます。
これは裏技ですから、堂々と人に奨めるようなものではないです。

' ' ///
Sub testAJ()

　　Dim EndClm As Long
　　Dim TitleA As Variant
　　Dim TitleB As Variant
　　Dim i As Long

　　With ThisWorkbook.Sheets(1)
　　　　EndClm = .Cells(1, .Columns.Count).End(xlToLeft).Column
　　　　TitleB = .Cells(1).Resize(, EndClm) '検証用 "品名", "4月", "5月", "6月", "7月", "8月", "9月"
　　End With
　　TitleA = Workbooks("aa.csv").Sheets(1).Cells(1).Resize(, EndClm) 'csvファイルのタイトル行, "7月", "8月", "9月"

　　With Application
　　　　.AddCustomList TitleB　　'　配列を引数にユーザー設定のリストを追加
' ' 引数にした配列がユーザー設定のリストの何番目にあるかを取得。無ければ、０。
　　　　If .GetCustomListNum(TitleA) = 0 Then MsgBox "項目が変更されています"
　　　　.DeleteCustomList .CustomListCount　　'　追加したユーザー設定のリストを削除して元に戻す。
　　End With

End Sub
' ' ///

結論っぽいことを書くなら、お奨めするなら最初に挙げたtestAです。
個人的には、どれでも許せる記述ですけれど、人によって意見は分かれそうです。
そういう意味では、testAのように万人が理解できるものが一番安心して使える、と思います。

お礼

:testA
修正ありがとうございます。
たしかにRangeとCellsを組み合わせるよりかはResizeプロパティで書いたほうがすっきりしますね。

:testJ
transpose関数自体を知りませんでした。
Redimでは配列の次元数自体を変更できないようなのであきらめていたんですが、こういう技もあるんですね。

:testC
ブックを開かずデータを読み込む方法があるんですね。
たしかにこれだと、開く前に検査だけできるので使いやすいです。

:testAJ
配列の要素を一気に検証する方法も教えていただきありがとうございます。
やはり一般的ではないんですね。

おすすめ通りtestAか、両方csvの場合はtestCでやりたいと思います。
transpose関数を使った配列の次元数変更はとても参考になりました。
ありがとうございました。

回答No.1

over_the_galaxy

後半のエラーは、range(cells, cells)の形で複数のブックを扱う場合、cellsがどのブックか指定する必要があります。つまり
Workbooks.sheets.range(Workbooks,sheets,cells, Workbooks.sheets,cells)

もちろん冗長になるのでwithを使うとスッキリします。

お礼

ご回答ありがとうございます。
仰る通りでした。
bookを指定するのを忘れていました。