VBA 別ワークブックの範囲指定

Question

Excel VBA で、別のワークブックの範囲を検索したいのですが
Rangeオブジェクトの初期化がうまくいきません。

具体的には
①    Debug.Print wk2.Sheets(1).Cells(1, 1)
②    Debug.Print wk2.Sheets(1).Cells(5, 1)
③    Debug.Print Range(wk2.Sheets(1).Cells(1, 1), wk2.Sheets(1).Cells(5, 1)).Rows.Count
④    Debug.Print wk2.Sheets(1).Range(Cells(1, 1), Cells(5, 1)).Rows.Count
で、①②は期待どおりに値を取得するのですが、③④は「実行時エラー '1004': アプリケーション定義またはオブジェクト定義のエラーです。」となってしまいます。どうしたら上手くいくでしょうか？

Qchan1962 · Accepted Answer

こんばんは
＞明示して指定しないといけないのかなぁと考えました。
そうですね。ブック、シートを跨ってはいけないと言う事です
明示するべきですね。ただ、実行前にシートをアクティブにしてしまう
などと言う乱暴な方法もあります。

＞Set Rng = wk2.sheets(1).Range(wk2.Sheets(1).Cells(1, 1), wk2.Sheets(1).Cells(5, 1))
ですか？

そうですね。少し難しくなりそうですが、、
Rangeを使う時はシートオブジェクトを明示する（上記の通り）
又は省略した場合はアクティブ シートが省略されていると理解しています

なのでSet Rng = Range(wk2.Sheets(1).Cells(1, 1), wk2.Sheets(1).Cells(5, 1))ではエラーになるはずですね。。
試してみると・・？　私の環境ではエラーにならずに実行されました？
テストコード（wk1にコードが書かれています）2つブックは開いてあります

以下の投稿は　私の環境ではエラーにならずに実行されました　を基に考察しました。

Sub test()
Dim wk2 As Workbook, Rng As Range
Set wk2 = Workbooks("test2.xlsm")
ThisWorkbook.Activate '実行しているのだから要らないが、、
Worksheets(1).Activate '念のため
Set Rng = Range(wk2.Sheets(1).Cells(1, 1), wk2.Sheets(1).Cells(5, 1))
'Set Rng = ActiveSheet.Range(wk2.Sheets(1).Cells(1, 1), wk2.Sheets(1).Cells(5, 1))
Debug.Print Rng(1)
Debug.Print Rng(5)
Debug.Print Rng.Rows.Count
End Sub

なんで？　少し調べてみると

Range オブジェクト
https://docs.microsoft.com/ja-jp/office/vba/api/excel.range(object)

Range.Range プロパティ
https://docs.microsoft.com/ja-jp/office/vba/api/excel.range.range
と
Worksheet.Range プロパティ
https://docs.microsoft.com/ja-jp/office/vba/api/excel.worksheet.range
何かよくわかりませんね

Worksheet.Range プロパティ、Range.Range プロパティ　
両方に下記のような説明があります。
’ーーー抜粋（上記参考サイト）
オブジェクト修飾子を指定せずにこのプロパティを使用すると、
ActiveSheet.Range のショートカットとなります。
つまり、アクティブ シートから範囲を取得します。
アクティブ シートがワークシートでない場合、このプロパティは失敗します。
’ーーー

基本的に私もこのように理解しています。

試しに　
Set Rng = ActiveSheet.Range(wk2.Sheets(1).Cells(1, 1), wk2.Sheets(1).Cells(5, 1))　で実行
当然、1004、、

ではなぜSet Rng = Range(wk2.Sheet　エラーにならない？

どうもRangeプロパティとして扱われているのではなく
Range オブジェクトのようですね。。

何か難しくなってしまいましたが、トーシローの私があれこれ書くより
参考になるかな
https://www.limecode.jp/entry/difference/range-object-property

初心者ですが何か。 · Answer

③ Debug.Print wk2.Range(wk2.Sheets(1).Cells(1, 1), wk2.Sheets(1).Cells(5, 1)).Rows.Count

しか残ってなさそう。

Qchan1962 · Answer

こんにちは
＞どうしたら上手くいくでしょうか？
ブックオブジェクト、シートオブジェクトなど跨った参照は
実行時エラー '1004': アプリケーション定義またはオブジェクト定義のエラーになると思います。
各オブジェクトを明示しましょう。
（SetやWithを使うと書きやすいかも知れません）

wk2が他のブックの場合
①②はエラーなし

③ThisWorkbook.ActiveSheetからオブジェクトを明示して
他のブックを参照した場合同様のエラー
ただ、記載の状態ではエラーにならないと思うのですが
ActiveSheet.Range(wk2.Sheets(1).Cells(1, 1), wk2.Sheets(1).Cells(5, 1)).Rows.Count　ActiveSheetを付け、他のブックを参照すると1004

④wk2.Sheets(1).Range(Cells(1, 1)
他のブックを参照しているので1004
ただ、wk2.Sheets(1)をアクティブにして実行すると5が返る

③で Setを使って考えると
    Set Rng = Range(wk2.Sheets(1).Cells(1, 1), wk2.Sheets(1).Cells(5, 1))
Debug.Print Rng(1)
Debug.Print Rng(5)
Debug.Print Rng.Rows.Count
かな、、

Qchan1962 · Answer

あまり難しく考えると分からなくなってしまいますので　追記します
すでに理解されている通りです

＃２で回答した
各オブジェクトを明示しましょう。
（SetやWithを使うと書きやすいかも知れません）　
で間違いないと思います

おまけ
Dim wk2 As Workbook
Dim sh1 As Object
Set wk2 = Workbooks("XXX.xlsx")
Set sh1 = wk2.Sheets(1)
With sh1
Debug.Print .Range(.Cells(1, 1), .Cells(5, 1))
End With

ちなみに処理の流れで
データ取得　ー＞データ加工　ー＞　データ出力　みたいな流れを基本とした場合、
オブジェクトなどに対してまとめて処理をしますよ、、Withは使い勝手が良いかもです。

例は　Setを使っているのでむしろ分かり難いかも、、、
*　Dim sh1 As Object
　As Worksheetでない理由：　Sheets(1)はworksheetとは限らない為

VBA 別ワークブックの範囲指定

③ Debug.Print wk2.Range(wk2.Sheets(1).Cells(1, 1), wk2.Sheets(1).Cells(5, 1)).Rows.Count

こんにちは

こんばんは

あまり難しく考えると分からなくなってしまいますので 追記します

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

あまり難しく考えると分からなくなってしまいますので　追記します