みなさん、こんにちは。
ITソリューション事業部の林です。

本記事では、ExcelVBAのテクニックを紹介・解説いたします。

今回紹介するのは、「複数のブック・シートの操作」です。

【目次】

複数のブック・シートの操作時に気を付けること
操作対象を省略したコードの問題点
親オブジェクトを明示的に記述したソースコード
終わりに

それでは、始めましょう！

複数のブック・シートの操作時に気を付けること

Excelマクロでは、基本機能としてExcelブックやシートを操作できるようになっています。
手作業でExcelを使っている時と同じように、マクロでも複数のブック・シートを展開・操作することが可能です。

可能ではあるのですが、複数のブックやシートを操作するケースでは意識することがあります。
それは、「操作対象を明示的に記述しなければならない」という点です。
その反対に、「操作対象を省略する」とエラーやバグの原因となってしまいます。

意識するべきポイントについて、次のトピックで具体的に解説していきます。

操作対象を省略したコードの問題点

悪い例として、操作対象を明示的に記述しない（記述を省略している）ソースコードを紹介します。
※悪い例です！

Sub ExcelVBA_BadExample()
    '注意！　バグの潜むソースコード！
    Worksheets("Sheet2").Cells(3, 1).Value = Cells(1, 1).Value
End Sub

上記コードでは、アクティブなシートからSheet2シートへセルの値を渡すという単純な処理をしているのですが、実はこのコードの書き方では、意図しない処理結果になる可能性があります。

Worksheets("Sheet2").Cells(3, 1).Value = Cells(1, 1).Value

さて、A1セルからSheet2シートのA3セルへ値の代入を行うコードですが、どこに問題があるでしょうか？

問題点その１　親オブジェクト（ワークシート）指定の省略

1つ目の問題点は、右辺の「Cells(1, 1).Value」です。

Cellsから書き始めるコードはこれまでの記事でも何度か紹介しましたが、この書き方で不具合が起こらないのは、操作対象のシートをあらかじめ選択している場合だけです。
なぜシートを選択しておかねばならないかというと、Cellsの親オブジェクト（ワークシート）の指定が省略されているからです。

Cells（またはRange）から記述した場合、親オブジェクトとなるワークシートが不明なので、ExcelVBAが暗黙的に「ActiveSheet」を親オブジェクトとして解釈します。
ActiveSheetは選択中のワークシートを表しています。

つまり、「Cells(1, 1).Value」は自動的に「ActiveSheet.Cells(1, 1).Value」として処理が実行されますので、選択中のシートのセルが参照されてしまいます。
親オブジェクト指定を省略した場合に、あらかじめシートを選択しておかなければならない理由がこれです。

渡したい値はSheet1シート上にあるのに、もしもSheet3シートを選択していたら、意図しない処理結果に終わってしまいます。

このような理由から、親オブジェクト（ワークシート）を省略したコードには不具合を起こす可能性が潜んでいます。

問題点その②　親オブジェクト（ワークブック）指定の省略

問題点2つ目は、左辺の「Worksheets("Sheet2").Cells(3, 1).Value」です。

一見して問題なさそうですが、ワークブックを2つ以上開いている場合には不具合の原因になりえます。

「Worksheets」から記述する書き方も、親オブジェクトが省略されています。
ワークシートの親オブジェクトはワークブックですので、この場合は暗黙的に「ActiveWorkbook」オブジェクトが使用されます。
ActiveWorkbookは、現在選択されているワークブックを表します。

マクロ実行時に、Sheet2を持たないワークブックが選択されていたら実行時エラーになりますし、
選択中のワークブックがそもそも操作する対象でなかったら、開発時の想定にない挙動を起こします。

問題点①②に対する解決策

さて、ここまで問題点を挙げてきましたが、これらに対する確実な解決策があります。
上記の問題は、いずれも「親オブジェクトを省略した」ために起きています。
記述を省略したせいで不具合になるというなら、その反対のことをすればよいのです。

つまり、「親オブジェクトを省略せずに書く」が解決策となります。

親オブジェクトを明示的に記述したソースコード

先ほど問題のあったコードに対して、親オブジェクトを明示的に記述していきます。

記述例①　Excelマクロ有効ブックを親オブジェクトに指定する

Sub ExcelVBA_015_Example1()
    '記述例①　Excelマクロ有効ブックを親オブジェクトに指定する場合
    ThisWorkbook.Worksheets("Sheet2").Cells(3, 1).Value = _
                    ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Value
End Sub

「ThisWorkbook」は、マクロの保存されているワークブックのことを指します。
これを親オブジェクトに指定すると、そのブック内のSheet1,Sheet2シートは子オブジェクトとして扱われます。
ThisWorkbookを何度も書くと冗長な見た目のコードになるので、Withステートメントでブロック化するのもアリです。

Sub ExcelVBA_015_Example2()
    '記述例①をWithステートメントでグループ化
    With ThisWorkbook
        .Worksheets("Sheet2").Cells(3, 1).Value = .Worksheets("Sheet1").Cells(1, 1).Value
    End With
End Sub

上記のマクロを実行すると、マクロ有効ブックのSheet1からSheet2へ値が渡ります。

記述例②　2つのワークブックをそれぞれ親オブジェクトに指定する

Sub ExcelVBA_015_Example3()
    '記述例②　2つのワークブックをそれぞれ親オブジェクトに指定する場合
    Workbooks("Sample.xlsx").Worksheets("Sheet1").Cells(3, 1).Value = _
                        ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Value
End Sub

Excelマクロ有効ブックと別ワークブック（Sample.xlsx）間での値のやりとりができます。
「Sample.xlsx」はあらかじめ開いている状態で動かすマクロですので、気を付けてください。

記述例③　ワークブックとシートをオブジェクト変数にする

Sub ExcelVBA_015_Example4()
    '記述例③　ワークブックとシートを変数にセットして親オブジェクトに指定する
    Dim SampleBK As Workbook, SampleSh As Worksheet
    Dim ThisBk As Workbook, ThisSh As Worksheet
    Set SampleBK = Workbooks("Sample.xlsx") 'Sample.xlsxをオブジェクト変数にする
    Set SampleSh = SampleBK.Worksheets("Sheet1") 'Sample.xlsxのSheet1をオブジェクト変数にする
    Set ThisBk = ThisWorkbook 'マクロ有効ワークブックをオブジェクト変数にする
    Set ThisSh = ThisBk.Worksheets("Sheet1") 'マクロ有効ワークブックのSheet1をオブジェクト変数にする
    SampleSh.Cells(3, 1).Value = ThisSh.Cells(1, 1).Value
End Sub

こちらは記述例②を実用的なコードに書き換えたものです。処理結果は全く同じです。
マクロ開発では、親オブジェクトを変数として扱えるようにして、コードの冗長化を避けて可読性を上げる手法が一般的です。
オブジェクトとして扱えるようになった変数は「オブジェクト変数」と言います。
Setはオブジェクト変数にオブジェクトを格納する時には必ず付けるようにしましょう。

補足ですが、上記のコードでは解説のためにWorkbookのオブジェクト変数をセットしてから、WorkSheetのオブジェクト変数へセットしています。
より短くするなら、以下のようにSetステートメントを1行に集約してWorkSheetのオブジェクト変数へセットする方が、コードをより短くできます。

Sub ExcelVBA_015_Example5()
    '記述例③をさらにコンパクト化
    Dim SampleSh As Worksheet
    Dim ThisSh As Worksheet
    Set SampleSh = Workbooks("Sample.xlsx").Worksheets("Sheet1") 'Sample.xlsxのSheet1をオブジェクト変数にする
    Set ThisSh = ThisWorkbook.Worksheets("Sheet1") 'マクロ有効ワークブックのSheet1をオブジェクト変数にする
    SampleSh.Cells(3, 1).Value = ThisSh.Cells(1, 1).Value
End Sub

終わりに

以上、複数のブック・シートを操作するコードの解説になります。
親オブジェクトを確実に指定しておくと、処理対象オブジェクトが固定化されるので、選択中のブックやシートのことを意識せずにマクロを実行することができます。
大量のワークブックを処理するマクロでは必須になるテクニックですので、親オブジェクトは明示的に指定することをおすすめします。

それでは、また次回お会いしましょう！