RangeとCellの仕組みと注意点, オブジェクトの宣言【VBA】

RangeとCellsは想像以上に奥が深い

普段、Excelを使うときに範囲選択って頻繁に使用しますよね。まあ、1セルでも選択しなけれな何もできないしね。
そんな範囲選択をVBAでできるのが、RangeとCellsというプロパティです。この二つ、基本的にできることは同じなはずなのに、何かと厄介なのです。ということで、それぞれについて説明していきます。

Range

Rangeは基本的に：

• 固定位置のセルを選択するとき
• 複数のセル範囲を指定するとき

に使用します。

使い方としては

'固定位置セルの指定
Range("A4")          'A4セルの範囲
Range(Cells(4, 1))   'A4セルの範囲
'複数セルの指定
Range("A1:C3")       'A1からC3セルの範囲
Range("A1", "C3")    'A1からC3セルの範囲
Range("A1", Cells(3, 2))    'A1からC3セルの範囲
Range(Cells(1, 1),Cells(3, 2)) 'A1からC3セルの範囲

と多種多様な書き方ができます。もうこの時点で初心者的には嫌だよね。ほんと。
また。行全体の選択や列全体の選択も行えます。

Range(Row(1), Row(5))  '1から5行全体の範囲選択
Range("1:5")  '1から5行全体の範囲選択
Range(Columns(1), Columns(5)) '1から5列全体の範囲選択
Range("A:C")   '1から5列全体の範囲選択
Range(Row(1), Columns(1))  '全セル

ここまで出てきたRow, Columnsは夫々、"行"と"列"を表します。

Cells

Cellsプロパティは、上位のオブジェクト式を省略した際は、アクティブなワークシート上のセルを表すRangeオブジェクトを返します。
つまり、Sheet1がアクティブな時に

Dim A as Long
A = 8
Cells(8, 3)
Cells(A, 3)
Cells(8, "C")

と書けば、どれもC8セルを指定したことになります。

他のシートのセルを選択するとき

例えば、Sheet1がアクティブな状態でSheet3のC8セルを指定したい場合、上記のように書いてしまうと上位のオブジェクト式が省略されているため、正しく実行されません。
正しくは

Dim A as Long
A = 8
Sheets("Sheet3").Cells(8, 3)
Sheets("Sheet3").Cells(A, 3)
Sheets("Sheet3").Cells(8, "C")
Sheets("Sheet3").Range("C8")
Sheets("Sheet3").Range("C" & A)

と書きます。

以下に他のシートからコピーするときの例を示します。

Dim A as Long
A = 55
With Sheets("Sheet3")
　　.Range(.Cells(8, 3), .Cells(9, A)).Copy _
    Destination:=Sheets("Sheet1").Range("C5")   'パラメータのコピー
End With

Dim A as Long
A = 55
 Sheets("Sheet3").Range(Sheets("Sheet3").Cells(8, 3), Sheets("Sheet3").Cells(9, A)).Copy _
    Destination:=Sheets("Sheet1").Range("C5")   'パラメータのコピー
End With

そもそも、オブジェクトの指定について

上記では、異なるシート間でコピーを行うときにオブジェクトを指定しなければならないことを説明しました。
これはExcel VBA が命令をオブジェクトの階層を上から順にたどって取得している為です。
Excelのオブジェクトは
Applicationオブジェクト
⇒Workbookオブジェクト (ブック(Excelファイル)のオブジェクト)
⇒⇒Worksheetオブジェクト(シートのオブジェクト)
⇒⇒⇒Rangeオブジェクト(セルのオブジェクト)
というような階層構造になています。
このため、使用するオブジェクトは階層の最上位から最下位まで順に指定して命令するのが由緒正しいやり方なのですが、よく使われるオブジェクトは基本的に省略可能になっています。
そして省略された場合はアクティブなブック、アクティブシートを取得するようになっています。
なのでアクティブなブックやアクティブなシート以外のオブジェクトに命令を行いたい場合は個別に指定する必要があるのです。

「テスト」という名前のExcelファイル(Workbook)の「名簿」シート(Worksheet)のセルに書かれた名前を取得する場合は

Workbook("テスト").Worksheets("名簿").Range(Cells(1, 1), cells(10, 2))

が一番確実な書き方でしょう。この場合はA1セルからB10セルまで範囲選択します。
また、シートの指定はWorksheetではなくWorkSheetsとすることに注意しなければなりません。
また、プログラムの中で毎回「Worksheets("シート名")」と記述するのは面倒であり、さらには指定するシートを変数化したい場合がありますよね。
その時は次のようにSetステートメントを用います。

変数の定義でSetを使用するとき

普段、変数を設定する時に

Dim test as String
test = "お腹がへった"
MsgBox test

と書くことで正しく変数が格納されていることが分かります。
ところろがどっこい、Worksheetを変数で設定したい場合は、普通とは少し違います。

Dim test as Worksheet
Set test =Sheets("Sheet1")
MsgBox test.Name

まあこんな感じで、変数の設定にはSetを使わないといけないようです。

この違いはデータ型の違いに因るものです。
Setを使わ無ければならないのはObject型の変数、つまりWorkbook, Worksheet, Rangenなどです。
これ等のオブジェクト変数に値を格納する場合は、Setステートメントを用いなければならないようです。
特に、範囲選択を行う場合のRangeで範囲を変数として設定したい場合は要注意ですね。