Excel VBA 構文エラーについて教えてください。

エクセルＶＢＡ初心者です。
下記の２つの構文があったとして、同じ動きをするわけですが、"Sheet1" となっているところを "Sheet3" に書き換えた時に、下の構文は動くのに、上の構文はエラー(実行時エラー '1004') となるのはなぜでしょうか？　"Sheet1" であれば、どちらの構文も問題なく動きます。.Cells としてもダメでした。Sheet名を変えただけで、なぜ上の構文だけ動かなくなるのか原因がわからずすっきりしないので、どなたか教えていただけると助かります。

Sub SetColumns()
Worksheets("Sheet1").Activate
Worksheets("Sheet1").Range(Cells(2,2),Cells(5, 5)) _
.EntireColumn.Value="XXX"
End Sub

Sub SetColumns()
Worksheets("Sheet1").Activate
Worksheets("Sheet1").Range("B2:E5") _
.EntireColumn.Value="XXX"
End Sub

No.1 ベストアンサー 回答者： nag0720 回答日時： 2015/09/11 17:58

上のコードは、どこのコードウィンドウに書いたかによってエラーになる場合とならない場合があります。

Sheet1のコードウィンドウに書いた場合は、Cells(2,2)、Cells(5,5)はSheet1のセルを指します。
Worksheets("Sheet1").Rangeの"Sheet1"を"Sheet3"に書き換えてもCellsが指すセルはSheet1のままなので、
Worksheets("Sheet3").Range(Worksheets("Sheet1").Cells(2,2),Worksheets("Sheet1").Cells(5, 5)).EntireColumn.Value="XXX"
と同じ意味になり、Sheet3の中にSheet1のセルはありえないのでエラーになります。
エラーにさせないためには、
Worksheets("Sheet3").Range(Worksheets("Sheet3").Cells(2,2),Worksheets("Sheet3").Cells(5, 5)).EntireColumn.Value="XXX"
と、Cellsの前にWorksheets("Sheet3").を付加する必要があります。

ThisWorkbookのコードウィンドウに書いた場合は、Cellsが指すセルはActiveSheetになるのでエラーにはなりません。
（ただし、Worksheets("Sheet3").Activateがなければエラーになります）



Range("B2:E5")とした場合は、Worksheets("xxxxxx")で指定したシートのセルを指すのでシート名を変えてもエラーにはなりません。

この回答へのお礼

Sheet1のコードウィンドウに書いていました。
とてもわかりやすい説明でよく理解できました。
どうもありがとうございました！

お礼日時：2015/09/14 09:43

No.3 回答者： x-1919 回答日時： 2015/09/12 16:43

Worksheets("Sheet1").Range(Cells(2,2),Cells(5, 5))

2つの Cells がどこブックのどのシートのセルを指し示そうとしているのかが明確じゃない。

「Worksheets("Sheet1") 」 は絶対的に "Sheet1" というワークシートを指し示している。
「Worksheets("Sheet1").Range」 は 「Sheet1 の中の Range」 と明確である。
Cells も同様に明確に場所を示してあげる必要がある。

No.2 回答者： WindFaller 回答日時： 2015/09/12 16:03

こんにちは。

#1さんがご説明になっておられますが、少し違う言い方をさせていただきます。

理屈は後で追いかけてくるものです。
基本的なことですが、MS-Office の中で、ほぼ、Excelだけが、[標準モジュール]を使うことが多いのです。
オブジェクトモジュール(Sheetモジュール,ThisWorkbookモジュール)の使い方は違います。だから、入門レベルでは、深く考えずに、[標準モジュール]を利用したほうがよいです。

オブジェクトモジュールは、Sheet1 なら、Sheet1 のオブジェクトの中での使い方になります。
オブジェクトモジュールでは、主にイベントと言って、Wokrsheet_Change など、特殊なマクロを置くことになります。

Worksheets("Sheet1").Range(Cells(2,2),Cells(5, 5))
としても、Sheet1以外のオジェクト・モジュールは、Rangeオブジェクトは、Sheet1 の親とつながっていますが、Cellsの親オブジェクトは、Sheet2とか、Sheet3 とか別のモジュールに書くと、別の親を呼んでいますから、エラーが発生します。

もし正しく書くなら、このようになります。(標準モジュールでも可)
Sub SetColumns()
With Worksheets("Sheet1")
　.Activate
　.Range(.Cells(2,2),.Cells(5, 5)) _
.EntireColumn.Value="XXX"
End With
End Sub

ただ、なぜ、こんな問題があるかというと、たぶん、VBA/VBの設計者と実際に作った人とが仲が悪かったとか、意思疎通ができていなくて、このようなことが発生したのだと思っています。

この回答へのお礼

#1さんの説明で理解できたのですが、さらに詳しく教えていただいてありがとうございます。ためになりました。

お礼日時：2015/09/14 09:46