'Range'メソッドは失敗しました

質問

ExcelのVBAの質問になりますが、教えてください。
下記を動かすと最後の行で「'Range'メソッドは失敗しました: '_Worksheet' オブジェクト」と出ます
どうしても最後の行をセレクトしたいのですが、どうしたらよいでしょうか。

Option Explicit
Public WB1 As Workbook
Public WB1SH1 As Worksheet

Public CSVWB1 As Workbook
Public CSVWB1SH1 As Worksheet

Dim MaxRow As Integer

Private Sub CommandButton1_Click()

Set WB1 = ActiveWorkbook
Set WB1SH1 = WB1.Worksheets(1)
Dim a As String

a = WB1SH1.Range("a1)

Workbooks.Open "C:\Users\User\Desktop\" & a & ".CSV"

Set CSVWB1 = ActiveWorkbook
Set CSVWB1SH1 = CSVWB1.Worksheets(1)

MaxRow = CSVWB1SH1.Cells(Rows.Count, 1).End(xlUp).Row

WB1SH1.Activate
WB1SH1.Range(Cells(1, 1), Cells(MaxRow, 3)).Select
CSVWB1SH1.Activate
CSVWB1SH1.Range(Cells(1, 1), Cells(MaxRow, 3)).Select　　'←ここでエラーがでる

End Sub

回答No.1

keithin · ベストアンサー

初心者さんが必ず一度は失敗します。

＞Private Sub CommandButton1_Click()

のようにシートオブジェクトに記載したマクロでは、シートを修飾しないセル（レンジ）オブジェクトは全て「マクロを記載したシート」に紐付けられています。

CSVWB1SH1.Range(CSVWB1SH1.Cells(1, 1), CSVWB1SH1.Cells(MaxRow, 3)).Select
のようにしなければいけません。

お礼

keithin様

早々のご回答ありがとうございます。
回答をメールで確認できたおかげで午前中に出先で処理ができました。
見事動きました！！

自己流でコードを組んでいるためとても読みづらかったと思われるのに
的確なご指摘で驚きました。

自分では全く何がダメなのかわからず困り果てての質問だったため感動
しました。

本当にありがとうございました。

回答No.2

WindFaller

こんにちは。

私のように細かなことを言う人はいないかと思いますが、少し我慢してください。

Private Sub CommandButton1_Click()
ということは、シートモジュール（オブジェクトモジュール）ですね。
シートモジュール上で、Public ステートメントが使えません。

シートモジュールに、もし入れるなら、

Dim Wb1 As Workbook
Dim Wb1Sh1 As Worksheet
Dim CSVwb1 As Workbook
Dim CSVwb1Sh1 As Worksheet
Dim MaxRow As Integer

のようにしますが、ブックが閉じるまで、変数を抱えることになりますから、必要以上には、モジュールスコープ（=そのモジュールだけの範囲）でも、用いません。

Public ステートメントを使うなら、標準モジュールを使います。しかし、Public ステートメントは、慎重に使わないといけない書き方です。テキストには「グローバル変数(Public変数）はできる限り使わない」ということが書かれています。それは、プロジェクト全体に関わってきますので、通常、使っても、モジュール内だけにします。ちなみに、なぜ「グローバル」と呼ぶのか、少し疑問がつくのは、厳密には、複数のプロジェクト間には使えないからです。

それと、変数は、大文字・小文字を含めていれるというのが、標準的な記法です。そうすることによって、入力ミスを減らすわけです。大文字だけで書くのは、定数に限ります。

例：
'標準モジュール
Public Wb1 As Workbook
Public Wb1Sh1 As Worksheet

Public CSVwb1 As Workbook
Public CSVwb1Sh1 As Worksheet

また、VB6系では、Sub プロシージャ名の直下から、Dim の変数定義の宣言をまとめるというのが通例になっていますから、

Private Sub CommandButton1_Click()
Dim a As String　'←ここに置く
Set Wb1 = ActiveWorkbook
Set Wb1Sh1 = WB1.Worksheets(1)

---------------------
Set Wb1 = ActiveWorkbook
Set Wb1Sh1 = Wb1.Worksheets(1)
実際は、このようには書きません。Worksheet("*****") を変数に入れた時に、すでに親オブジェクトの、Workbookも含まれていますから、２つにする必要はありません。

Set Wb1Sh1 = ActiveWorkbook.Worksheets(1)　'←出来る限り、パラメータは、シート名にしてください。

だけで良いです。
----------------------

このコードは少し問題がありますから、正確には書けませんが、Rangeのパラメータで、Cells を２ついれることは、ふつうはしません。いずれにしても、なるべく、Withステートメントを使うようにしましょう。（オブジェクトの前に、ピリオド[.]が入っていますから、注意してください）

もちろん、前に変数を使って、コンテナ型（親オブジェクトから全部入れる）という方法も悪くはありませんが、VBAの書き方の基本は、文字数を少なくして、なおかつ可読性を悪くしないということです。

'//

With Wb1Sh1
　.Activate　
　.Range(.Cells(1, 1), .Cells(MaxRow, 3)).Select
　　'前々行のActivate を含めて、実務上は、Activate やSelect はあまり使いません。これらのメソッドはユーザーに見せるために使うものです。
End With
With CSVwb1Sh1
　.Activate
　.Range(.Cells(1, 1), .Cells(MaxRow, 3)).Select　'こちらも同様です。
End With

'//

記法の参考資料です。今さらという人もいるかもしれませんが、私でも参考にしています。

コーディング技法(VS.Net 2003)
http://msdn.microsoft.com/ja-jp/library/aa291593(v=vs.71).aspx

Microsoft Office 2000/Visual Basic プログラマーズ ガイド
http://msdn.microsoft.com/ja-jp/library/cc375992.aspx
私は、この書籍版を手に入れています。

面倒な話を長く書きましたが、少しずつ習得してみてください。信じないかもしれませんが、これを覚えればエラーが減り、自然と上達していきます。なお、インデント（桁下げ）は、mougで知られた渡辺さんという方の、VBePlus というツールを使っています。これも、エラーを発見するためです。

お礼

WindFaller様

ご指導、ありがとうござます。
初心者で独学の私にはとても勉強になりました。

＞シートモジュール上で、Public ステートメントが使えません
＞変数は、大文字・小文字を含めていれるというのが、標準的な記法です
＞大文字だけで書くのは、定数に限ります
＞Subプロシージャ名の直下から、Dim の変数定義の宣言をまとめるというのが通例

これらはどれも知りませんでした。。。

＞親オブジェクトの、Workbookも含まれていますから、２つにする必要はありません。
２個も書く必要はないのですね！！少しコンパクトになります！

＞なるべく、Withステートメントを使うようにしましょう
Withはとても苦手で避けているところが自分にあります。
もっと勉強して活用します。

記法の参考資料、Ｗeb版も参考になりますが、書籍版ももっと興味があります。
Ｗeb版を活用してみて自分にあっていそうだったら入手を考えてみます。

ご指導、ありがとうございました。感謝いたします。