PR
古庄 潤(ふるしょう じゅん)

本業はエンジニア。ICに様々な機械をつなぎ,電流やら電圧を測定する。もちろん,これらの測定器もVBAでコントロールし,取り込んだデータもマクロで処理する。人呼んで,マクロの鬼軍曹!

診断(9)
親オブジェクトを省略できない罠

「先生,暑くて死にそうです」
「ゲール君,夏は暑いもんじゃ」
「それは,そうですけど…」
「心頭滅却すれば火もまた涼しじゃ」
「そんな仙人みたいなことを言わないで,エアコンを買いましょうよ」
「エアコンは,ヒートアイランド現象,地球温暖化の元凶じゃ」
「あれ? 机の下のたらいは何ですか?」
「ん! いや…次の方ど~ぞ~」

今月の相談
「各店舗の調査票(野菜四種の一週間の売り上げ)から,野菜ごとのデータを集めて集計するマクロを作りました。CopyメソッドとPasteメソッドの組み合わせは,時間がかかるので,代入式にしましたが,エラーになって実行できません(リスト1図1)。なぜでしょうか?」


Sub Syuukei()
  Dim myFILE_NAME As Variant
  Dim i As Integer
  Dim j As Integer
  Dim BOOK_A As Workbook
  Dim BOOK_B As Workbook
  Dim B_BOOK_NAME As String

  myFILE_NAME = Application.GetOpenFilename( _
    "Excel ファイル (*.xls)," & " *.xls,全てのファイル,*.*", _
    MultiSelect:=True)

  If IsArray(myFILE_NAME) = False Then
    Exit Sub
  End If

  Set BOOK_A = _
    Workbooks.Open(ThisWorkbook.Path & "\" & "集計表.xls")

  For i = LBound(myFILE_NAME) To UBound(myFILE_NAME)
    Set BOOK_B = Workbooks.Open(myFILE_NAME(i))
    B_BOOK_NAME = BOOK_B.Name

    For j = 1 To 4
      BOOK_A.Worksheets(j).Range(Cells(i, 3), Cells(i, 9)) = _
        BOOK_B.Worksheets(1).Range(Cells(j+2, 3), Cells(j+2, 9))
    Next j

    BOOK_B.Close
  Next i

  BOOK_A.Close

  Set BOOK_A = Nothing
  Set BOOK_B = Nothing
End Sub
リスト1●複数の店舗ファイルから,売り上げデータ取り出して集計するマクロ。しかし,実行するとエラーになる

図1●表示されるエラー・メッセージ
図1●表示されるエラー・メッセージ

「どれどれ。なるほど,この方は“省略の罠”にはまっておるな」
「先生,省略の罠って,どういうことですか?」
「うむ,VBAは,様々な場面でコードの記述を省略できるようになっておる。例えば,セルRangeオブジェクトのValueプロパティは,以下のように省略できる。
Range("B3") = 100
Cells(3, 2) = 200

こんなコードでも,ちゃんと機能するんじゃ」
「あぁ,そうでした。親オブジェクトも省略できるんでしたね」
「そう,ゲール君,今日は冴えておるぞ」
「え? 何のことですか?」
「まさに,その親オブジェクトの省略が今回のエラーの原因なんじゃ」
「やっぱり!って偶然ですけど」
「なんじゃ,あてずっぽうか。では,解説しよう。任意のオブジェクトの親オブジェクトを省略すると,アクティブなオブジェクトが親オブジェクトになる」
「呪文ですか?」
「情けない,わしの元で修行をして,何を学んできたのじゃ」
「冗談ですよ。Rangeオブジェクトの親オブジェクトを省略すると,その時アクティブなワークシートが親オブジェクトになるんですよね?」
「その通り!」
「同様に,Worksheetオブジェクトの親オブジェクトを省略すると,その時アクティブなブックが親オブジェクトになります」
「ご名答! マクロを記述したブックだけで処理が完結する場合,そのマクロは操作の対象となるブックを意識する必要はない。つまり,ThisWorkbook.Worksheets(1)と記述しなくても,Worksheets(1)だけで済む。また,アクティブなシートだけを操作の対象とする場合は,Worksheetオブジェクトも省略できる」

「でも,リスト1のコードは,Rangeオブジェクトの親オブジェクトがちゃんと記述されていますよ」
「そうなんじゃ,そこで安心してしまうから落とし穴にはまってしまうんじゃ」
「ということは,Rangeプロパティの問題ではない?」
「その通り。では,どこに問題があるのか?」
「ん~,残っているのはCellsプロパティだけです。あ,そうか,Cellsプロパティの親オブジェクトが記述されていません」
「正解! Rangeプロパティの引数にCellsプロパティを使う場合,Rangeオブジェクトの親オブジェクトがCellsオブジェクトの親オブジェクトになるように思えるが,個別に記述しなければならないんじゃ」
「なんだか不自然です。Rangeプロパティの引数に,他のワークシートのCellsオブジェクトを使うわけないじゃありませんか」
「そうじゃな,ゲール君の言うことも最もじゃ。しかし,それがVBAの仕様だからしょうがない」
「そうなんですかぁ。では,そろそろ処置済みのコードを」


Sub Syuukei()
  Dim myFILE_NAME As Variant
  Dim i As Integer
  Dim j As Integer
  Dim BOOK_A As Workbook
  Dim BOOK_B As Workbook
  Dim B_BOOK_NAME As String
  Dim myROW_No As Long
  Dim SHEET_A As Worksheet
  Dim SHEET_B As Worksheet

  myFILE_NAME = Application.GetOpenFilename( _
    "Excel ファイル (*.xls)," & " *.xls,全てのファイル,*.*", _
    MultiSelect:=True)    '--------(1)

  If IsArray(myFILE_NAME) = False Then
    Exit Sub              '--------(2)
  End If

  Set BOOK_A = _
    Workbooks.Open(ThisWorkbook.Path & "\" & "集計表.xls")
                          '↑------(3)

  For i = LBound(myFILE_NAME) To UBound(myFILE_NAME)
                          '↑------(4)
    Set BOOK_B = Workbooks.Open(myFILE_NAME(i))
                   '↑-------------(5)

    B_BOOK_NAME = BOOK_B.Name '----(6)
    Select Case B_BOOK_NAME   '----(7)
      Case "スーパー激安.xls"
        myROW_No = 3
      Case "安井商店.xls"
        myROW_No = 4
      Case "八百屋金兵衛.xls"
        myROW_No = 5
    End Select

    For j = 1 To 4    '------------(8)
      Set SHEET_A = BOOK_A.Worksheets(j) '--(9)
      Set SHEET_B = BOOK_B.Worksheets(1) '--(10)
      
      SHEET_A.Range(SHEET_A.Cells(myROW_No, 3), _
        SHEET_A.Cells(myROW_No, 9)).Value = _
        SHEET_B.Range(SHEET_B.Cells(j + 2, 3), _
        SHEET_B.Cells(j + 2, 9)).Value   '--(11)
    Next j
    BOOK_B.Close    '--------------(12)
  Next i

  BOOK_A.Close      '--------------(13)

  '↓----------(14)
  Set SHEET_A = Nothing
  Set SHEET_B = Nothing
  Set BOOK_A = Nothing
  Set BOOK_B = Nothing
End Sub
リスト2●リスト1のエラー個所を修正した集計マクロ

 まず,GetOpenFilenameメソッドで,集計の対象となる店舗ごとのファイルの名前(フルパス+ファイル名)を取得して,変数に代入します(1)。

 (2)で,変数myFILE_NAMEをIsArray関数で調べ,配列でない場合は,直ちにプロシジャを抜けます。変数myFILE_NAMEが配列でないということは,(1)で表示された「ファイルを開く」ダイアログボックスで,ユーザーが[キャンセル]ボタンをクリックしたか,ファイルを選択しないで[開く]ボタンをクリックしたことを意味します。どちらのケースもユーザーはマクロ実行の意思がないと判断して,直ちにマクロの実行を中断します

 ファイルが選択されていたら,(3)で集計したデータを保存するブックを開き,オブジェクト型変数に代入します。

 (4)は,配列変数myFILE_NAMEに格納したファイル名の数だけ繰り返すループです。LBound関数で配列の最小の添え字を,UBound関数で配列の最大の添え字を取得し,それぞれループのStartとEndに使用します。

 (5)で,集計の対象となるブックを開き,オブジェクト型変数BOOK_Bに代入します。開くファイルの名前は,配列変数myFILE_NAMEの引数を,ループのカウンタ変数iにしていることで,順番に取り出されます。

 続いて,BOOK_Bのファイル名を取り出し,変数に代入します(6)。ここで取り出したファイル名で,データを代入するセルの行番号を決定します(7)。

 (8)は,調査した商品の数だけ繰り返すループです。その中では最初に,集計したデータを保存するワークシートと,調査したデータが保存されているワークシートをそれぞれオブジェクト型変数に代入します(9)(10)。そして,調査したデータを集計シートに代入します(11)。これを,四回(野菜四種ぶん)繰り返します。

 (8)のループを抜けたら,調査したデータが保存されているブックを閉じます(12)。さらに,店舗ファイルごとのループ(4)を抜けたら,集計したデータを保存したブックを閉じます(13)。

 最後に,オブジェクト型変数を空にして,メモリーを解放します(14)。