VBAで複数ファイル、複数シートに対して集計業務をするには、WorkBookオブジェクト、WorkSheetオプジエクト、Rangeオブジェクトの理解が必須です。
Excelの集計業務は、通常1つのワークシートで完結するよりも、複数のワークシートにまたがって集計作業をすることが多いですよね。さらに言えば、1つのExcelファイルで完結せず、複数のExcelファイルにまたがって集計作業をすることも少なからずあります。
VBAによる集計作業では、もちろん上記のような複数のワークシートやブックを扱う集計作業に対しても対応できる仕組みを持っており、その仕組みをきちんと理解することは、VBAで集計業務をする上でとても大切になります。
今回の記事では、その大切な仕組みを理解するために、VBA集計業務に使う3つのオブジェクトについてのお話しをしたいと思います。
この3つのオブジェクトとは、「WorkBook」、「WorkSheet」、そして「Range」オブジェクトです。
オブジェクト型を含む他のデータ型については、過去の記事で簡単に説明しましたが、ここでは改めてオブジェクト型について詳しくお話ししたいと思います。
では、さっそく順番にお話ししていきますね。
目次
WorkBookオブジェクト
WorkBookオブジェクトは、Excelファイルのことを指します。VBA集計業務では、データ元になる複数のExcelファイルから、集計先の取りまとめ用Excelファイルへデータを転記することがあります。
集計中は、取りまとめ用Excelファイルは開いたままです。理由は、VBAコードが書かれているファイルであることと、開いたデータ元ファイルから転送されるデータが都度張り付けられるからです。
その取りまとめ用Excelファイルが開かれている状態で、VBAでデータ元のExcelファイルを１個ずつ開いてから、中のデータを取り出して取りまとめ用Excelファイルへ転記します。
その転記が済んだデータ元ファイルを閉じてから、また次のデータ元のExcelファイルを処理します。
このように、2つ（時には3つ以上）のExcelファイルを開いて処理をする時、VBAコードの中では、それぞれのExcelファイルを固有のWorkbookオブジェクト変数へ代入して管理します。
例えばデータ転記先の取りまとめ用Excelファイルを変数「wbkSelf」、データ元ファイルの変数名を「wbkData」として、VBAコード上で区別します。
このようにすることで、VBAコード上でワークシートを指定したいときに、「どっちのワークブックのワークシートなんだ？」っていうことが無くなるんですね。
WorkBookオブジェクト変数の設定例
今回の3つのオブジェクト変数の設定方法は、文字列型や長整数型の変数設定の方法とは異なります。
そこで、オブジェクト変数の設定方法について、前出の図の例を使って詳しくお話しします。図の例では、「元データ用」と「取りまとめ用」の2つのオブジェクトに対して、それぞれオブジェクト変数を設定しています。
そして、それぞれのオブジェクト変数をVBAコード上で設定する方法は、以下のとおり「Set」をつけて定義します。
- Set wbkSelf = Workbooks("取りまとめ用.xlsm")・・・取りまとめ用Excelファイル名「取りまとめ用.xlsm」を、オブジェクト変数「wbkSelf」に設定
- Set wbkData=Workbooks("Data1.xlsx")・・・元データExcelファイル名「Data※.xlsx」を、オブジェクト変数「wbkData」に設定
（※は、元データファイルにつけた追番（今回の例では1～3）が入る）
ここで、上記のようなファイル名「取りまとめ用.xlsm」や「Data1.xlsx」が入る箇所は、フルパスで指定しなければなりませんので注意してくださいね。
まとめると、Workbookオブジェクトの変数設定方法は、以下のとおりになります。
Set (変数名) = Wokbooks("Excelファイル名")
VBAコード上で、変数名の前に「Set」をつけ、Workbooks()のカッコ内にExcelファイル名（フルパス指定）を書きます。
WorkSheetオブジェクト
このオブジェクトは、Excelの「ワークシート」のことです。
VBAでこのワークシートを処理する場合には、処理したいワークシートをWorkSheetオブジェクトに代入して管理します。
例えば、VBAで下記5枚のシートを一度に取り扱うコードを書いたとします。この場合、コードが実行されるあいだは、各シートが区別されていなくてはいけません。
仮に5つのシートの区別ができないと、同じセル番地は実行中5個存在することになり、どのワークシートのセルであるか分からなくなってしまいます。
図の例は、4枚は元データがあるワークシートです。1枚は、それら元データの転記先になる取りまとめシートです。
WorkSheetオブジェクト変数の設定例
図の例を使って、VBAコード上でWorkSheetオブジェクト変数を設定する例を示します。
- Set wstData※ = Worksheets("元データ※")・・・「元データ1～4」までのワークシートをオブジェクト変数に設定する例です。
- Set wstSelf = Worksheets("取りまとめシート")・・・「取りまとめシート」をオブジェクト変数に設定する例です。
まとめると以下のようになります。
Set (変数名) = Worksheets("ワークシート名")
VBAコードで変数名の前に「Set」をつけ、Worksheets()のカッコ内にワークシート名を書く
Tips：複数ワークシートを扱うVBAサンプルコード集
ワークシート名を使う複数ワークシートの管理法サンプルコード
集計対象データがあるシートとは別シートに表を作るサンプルコード
別シートにある帳票形式の表にデータを転記するサンプルコード
複数シートのデータを1つの集約シートへ集約するサンプルコード
取り扱い注意のActiveWorkbookやActiveSheet
VBAを使い始めの方や、普段VBAを使い慣れてない方のVBAコードの中に、ActiveWorkbookやActiveSheetが多用されているのを見ることがあります。
恐らく今回ご紹介したWorkbookオブジェクトやWorkSheetオブジェクトを変数として取り扱わずに、処理対象のファイルやワークシートをその都度Activateにした後に処理しているからだと思います。
確かに目的の処理はされているようなのですが、やはり可読性やメンテナンス性が良くありません。そのため、VBAツールにとって日常茶飯事な機能追加のためのコード変更に大変な労力を割くことになり得ます。
例えば、Activeにしたシートの処理途中に、他ブックを開く処理を機能追加させた場合、そのブックを一度開くとそれまでActiveだった処理対象のシートが、他ブックを開くことでActiveでなくなってしまいます。
一方で今度は開かれたブックがActiveになりますので、その後の処理コードの対象シートがまったく意図したものと違うものになってしまい、当然集計結果も変わってしまいます。
上記の例は、言葉で書くと間違いのからくりが良くわかるのですが、コード上で見つけようとすると結構な時間を取られてしまいます。
以上の不都合があるためか、筆者の場合、ActiveWorkbookやActiveSheetを使った記憶がありません。その代わり、VBAコード上で取り扱うWorkbookやWorksheetを必ずオブジェクト変数へセットして管理しています。
Rangeオブジェクト
まずは、下図を見てください。
Rangeオブジェクトは、セル単体もしくは複数のセル範囲をまとめたものをいいます。図の例でお話しします。まず4つの太枠で囲まれた範囲がありますよね。
「B2」は単体セル、「C4:D5」、「B8:D10」、「F2:F6」はセル範囲ですが、これらをまとめて「Rangeオブジェクト」として定義し、VBAコードで取り扱います。
VBAコードの書き方は、「B2」（単体セル）の場合「Range("B2")」、「C4:D5」、「B8:D10」、「F2:F6」の場合は、それぞれRange("C4:D5")、Range("B8:D10")、Range("F2:F6")というようにします。
Rangeオブジェクト変数の設定例
セル範囲をVBAコードで扱う場合、Rangeオブジェクト変数として定義します。ここでは、図の3つのセル範囲について、VBAコード上でRangeオブジェクト変数を設定する方法をお話しします。
- Set rngData1 = Range("C4:D5")・・・ セル範囲「C4:D5」をRangeオブジェクト（変数名「rngData1」）に設定
- Set rngData2 = Range("B8:D10")・・・セル範囲「B8:D10」をRangeオブジェクト（変数名「rngData2」）に設定
- Set rngData3 = Range("F2:F6")・・・セル範囲「F2:F6」をRangeオブジェクト（変数名「rngData3」）に設定
じつは、このRangeオブジェクトの設定方法ですが、Cellsを使っても定義できます。同じように下記にその例を示しますね。
- Set rngData1 = Range(Cells(4,3), Cells(5,4))・・・ セル範囲「C4:D5」をRangeオブジェクト（変数名「rngData1」）に設定
- Set rngData2 = Range(Cells(8,2), Cells(10,4))・・・セル範囲「B8:D10」をRangeオブジェクト（変数名「rngData2」）に設定
- Set rngData3 = Range(Cells(2,6), Cells(6,6))・・・セル範囲「F2:F6」をRangeオブジェクト（変数名「rngData3」）に設定
さらにRangeオブジェクトの設定方法には、いろいろとバリエーションがあるのですが、VBA集計業務ではここで紹介する2つの方法をまずは覚えてください。
別のバリエーションについては、この先コードの紹介をする中で、必要に応じて詳しく紹介しようと思います。
まとめ
今回は、VBA集計業務で複数のExcelファイル、および複数のワークシートを扱う場合に必要な「Workbook」と「Worksheet」、それぞれのオブジェクトに使う変数の設定方法についてお話ししました。
また、Excelシート上の単体セルやセル範囲のVBAコードの表記法と、セル範囲をRangeオブジェクトとして扱う場合の設定方法もお話ししました。
これらは、VBAコード内できちんと目的のExcelファイルやワークシート、セル範囲を管理することで、間違いなく目的のセルへ値を集計するために必須の知識になります。