VBAで、セル（Range）のオブジェクトが取得できない

質問

セルの情報を取得したいのですが、何故か以下のように書くと上手い具合に取得できません（A、B、Cは数値の変数）。
------
Dim Rng As Range 'セル範囲を格納
Set Rng = Worksheets(1).Range(Cells(A, B), Cells(A, C))
------

以下のようにしてやると、何とか取得できます。
Worksheets(lngASNo + 1).Select
Set Rng = Range(Cells(A, B), Cells(A, C))

どこが悪いのでしょうか？
もし、判る方がいらっしゃいましたら、よろしくお願いいたします。

回答No.1

Wendy02 · ベストアンサー

こんばんは。

これをコンテナといいますが、

>Dim Rng As Range 'セル範囲を格納
>Set Rng = Worksheets(1).Range(Cells(A, B), Cells(A, C))

Worksheets(1)+Rangeはつながっていても、肝心な、Cells は、どこにもつながっていませんので、当然、ActiveSheet を指しています。そうすると、エラーを吐きます。

コンテナスタイルは、

With Worksheets(1)
　Set Rng = .Range(.Cells(A, B), .Cells(A, C))
End With

または、

Set Rng = Worksheets(1).Range(Worksheets(1).Cells(A, B), Worksheets(1).Cells(A, C))

という書き方になります。

>Worksheets(lngASNo + 1).Select
>Set Rng = Range(Cells(A, B), Cells(A, C))

これは、一旦、シートをSelect　しているので、Rangeも Cellsも、一見、親のワークシートと整合性が取れているように見えるので、エラーは吐きません。

ただし、このように、親のワークシートと、Rangeオブジェクトが、直接結ばれていない場合は、必ず、"標準モジュール"に書かないと、シートをSelectしても、 Cells 側の移動が効かないはずです。

'あまり、このようなスタイルはロスが多くて賛成できないマクロの書き方です。
'/標準モジュール/
Sub TestLoop()
Dim lngASNo As Long '本来はInteger
Dim Rng As Range
Const A As Integer = 1
Const B As Integer = 2
Const C As Integer = 5

For lngASNo = 1 To Worksheets.Count - 1
　Worksheets(lngASNo + 1).Select
　Set Rng = Range(Cells(A, B), Cells(A, C))
　MsgBox Rng.Address & vbCrLf & Rng.Parent.Name
Next lngASNo
End Sub

**************************
'上記と内容は違いますが、基本的に、ループでは、Set Rng　で、領域を変数に入れないほうがよいです。
'例：
Sub TestSample()
Dim i As Integer
Const A As Integer = 1
Const B As Integer = 1
Const C As Integer = 5

For i = 1 To Worksheets.Count - 1
　With Worksheets(i + 1)
　　.Range(.Cells(A, B), .Cells(A, C)).FormulaLocal = "=COLUMN()"
　End With
Next i
End Sub
'貼り付ければ分かりますが、Cellsの前に「.（コンマ)」が入っています。

お礼

なるほど、そういう繋がりになっているのですか。なかなかVBAはシートの繋がりとか判りづらくて苦労します。

>Worksheets(lngASNo + 1).Select
>Set Rng = Range(Cells(A, B), Cells(A, C))
これは標準モジュールに書いてたので、エラーが出なかったのですね。なるほど。

詳細な解答いただきまして、有り難うございます。助かりました。