ExcelのVBAにて欠かせない、RangeとCellsを使ったセルの参照方法をご説明します。
VBAでのセルの参照方法
例えばこのような表で、セル「B3」を参照したい場合ですが、方法は2通りあります。
方法1：Rangeを使用する場合
記述方法
Range("セルのアドレス")記述例
Range("B3")解説
Rangeの場合は、「列の名前（A、B、C、・・・）」+「行番号」 を指定します。
ワークシート関数で使うような、見慣れた形式になっているかと思います。
方法2：Cellsを使用する場合
記述方法
Cells(行番号, 列番号)記述例
Cells(3, 2)解説
Cellsの場合は、「行番号」，「列番号」を指定します。
Rangeと違って、行番号を先に記述することと、列の名前を数字で表すことに注意です。
※列の名前を「A、B、C、・・・」といったアルファベットで書くことも出来ますが、それなら「Cells」を使わずに、素直に「Range」を使ったほうが良いかと思います。
補足説明
上記の例のように、セルの位置を固定で指定する場合は、どちらでも構いません。好みの問題かと思います。
では、どう使い分ければ良いかというと、以下のような場合に違いが出ます。
- セルの範囲・複数セルを指定する場合
➡Range一択です。Cellsでも出来ますが、複雑になります。 - 変数を使って行番号や列番号を変えたい場合
➡列番号を変えたい場合はCells一択です。
セルの範囲の指定方法
Rangeを使用する場合
セル範囲の指定（例：セルA1～C3を指定）
Range("A1:C3")セルを「:」（コロン）でつなげると、連続するセル範囲を指定できます。
複数のセルを指定（例：セルA1とC3の2つのセルを指定）
Range("A1,C3")「:」（コロン）ではなく「,」（カンマ）でつなげると、離れたセルを同時に指定できます。
複数のセル範囲を指定（例：セルA1～A3と、C1～C3の2つのセル範囲を指定）
Range("A1:A3,C1:C3")上記「:」（コロン）と「,」（カンマ）の合わせ技です。
行全体を指定（例：1行目の全体を指定）
Range("1:1") または
Rows("1:1") または
Rows(1)行番号を「:」（コロン）でつなげると、行全体を指定できます。（単一行の場合は同じ行番号をコロンの前後に指定）
また、行全体を指定する場合は、「Range」の代わりに「Rows」も使用できます。
※後ろに「s」が付かない「Row」というのもありますが、「Row」は行番号を取得するプロパティですので、ご注意ください。
行全体を指定（例：1～3行目の全体を指定）
Range("1:3") または
Rows("1:3")行番号の始点と終点を「:」（コロン）でつなげると、連続する行全体を指定できます。
列全体を指定（例：A列の全体を指定）
Range("A:A") または
Columns("A:A") または
Columns("A")列の名前を「:」（コロン）でつなげると、列全体を指定できます。（単一行の場合は同じ列の名前をコロンの前後に指定）
また、列全体を指定する場合は、「Range」の代わりに「Columns」も使用できます。
※後ろに「s」が付かない「Column」というのもありますが、「Column」は列の番号を取得するプロパティですので、ご注意ください。
列全体を指定（例：A～C列の全体を指定）
Range("A:C") または
Columns("A:C")列の名前の始点と終点を「:」（コロン）でつなげると、連続する列全体を指定できます。
Cellsを使用する場合
複雑になるので推奨しませんが、参考までに記載しておきます。
セル範囲を指定（例：セルA1～C3を指定）
Range(Cells(1, 1), Cells(3, 3))「Cells」単独では実現できず、「Range」と組み合わせて始点・終点を指定する必要があります。
複数のセルを指定（例：セルA1とC3の2つのセルを指定）
Union(Cells(1, 1), Cells(3, 3))この場合は、「Union」で対象セルを指定します。
複数のセル範囲を指定（例：セルA1～A3と、C1～C3の2つのセル範囲を指定）
Union(Range(Cells(1, 1), Cells(3, 1)), Range(Cells(1, 3), Cells(3, 3)))上記「Union」と「Range」の合わせ技です。
行全体を指定（例：1行目の全体を指定）
Cells(1, 1).EntireRow末尾に「.EntireRow」を付けると、行全体を指定できます。
行全体を指定（例：1～3行目の全体を指定）
Range(Cells(1, 1), Cells(3, 1)).EntireRow上記「Range」と「.EntireRow」の合わせ技です。
列全体を指定（例：A列の全体を指定）
Cells(1, 1).EntireColumn末尾に「.EntireColumn」を付けると、列全体を指定できます。
列全体を指定（例：A～C列の全体を指定）
Range(Cells(1, 1), Cells(1, 3)).EntireColumn上記「Range」と「.EntireColumn」の合わせ技です。
変数の使用について
変数の使用方法の概要は、以下の通りです。
- 行に対して変数（Idx）を使う場合
Cells(Idx, 1) または Range(“A” & Idx) - 列に対して変数（Idx）を使う場合
Cells(1, Idx)
Rangeの場合は、列の名前を”A”のように文字で表すため、変数で列を変化させることが出来ません。
Rangeについては、私の主観も交えた補足がございますが、長くなるので、当記事の末尾に記載しました。
変数を使う例：行に対して変数を使う場合
簡単なサンプルとして、先ほどの表で、「平成生まれ（生年月日＞1989/1/7）の場合、D列に”平成生まれ”と表示する」場合、こんな感じになります。
（これぐらいなら関数で出来ますが、説明用の簡単なサンプルということで・・・）
Cellsを使用する場合
Sub test()
Dim Idx1 As Integer
For Idx1 = 2 To 5
If Sheets("Sheet1").Cells(Idx1, 3) > #1/7/1989# Then
Sheets("Sheet1").Cells(Idx1, 4) = "平成生まれ"
End If
Next
End Sub補足をします。
- 行番号をループカウンターIdx1として、For文でループさせています。
- 「C列」は「左から3番目の列」ということで、Cells(Idx1, 3)
「D列」は「左から4番目の列」ということで、Cells(Idx1, 4)
となります。 - 日付を扱いたい場合、#1/7/1989# のように、##で囲んで下さい。
なお、VBAのエディタ上で #1989/1/7# のように入力すると、自動的に #1/7/1989# のように変換されます。 - セルを扱う場合は、上記のSheets(“Sheet1”)のように、シート名を必ず指定するクセをつけて下さい。
複数のシートを扱うことになった場合に、正しく動かなくなります。
Rangeを使用する場合
Sub test2()
Dim Idx1 As Integer
For Idx1 = 2 To 5
If Sheets("Sheet1").Range("C" & Idx1) > #1/7/1989# Then
Sheets("Sheet1").Range("D" & Idx1) = "平成生まれ"
End If
Next
End Sub今度はRangeを使用した例です。
違いは、Range(“C” & Idx1)の部分ですね。
セル「C2」～「C5」をループさせるという意味で、列の名前 ”C” をダブルクォーテーション「”」で括って、行番号Idx1を「&」で繋げればOKです。
”C”をダブルクォーテーション「”」で括らないと、「C」が変数として扱われてしまうので、気を付けてください。
実行結果
無事、D列に”平成生まれ”が表示されました。
変数を使う例：列に対して変数を使う場合
簡単なサンプルとして、「1行目の入力値を順に表示させる」という場合、こんな感じになります。
（またしても全く実用的でないプログラムですが、説明用の簡単なサンプルということで・・・）
なお、先ほど述べた通り、列に対して変数を使いたい場合は、Rangeは使わずにCells一択です。
Cellsを使用する場合
Sub test3()
Dim Idx1 As Integer
For Idx1 = 1 To 3
MsgBox Sheets("Sheet1").Cells(1, Idx1)
Next
End Sub今度は、行番号が「1」で固定で、列番号をループカウンター「Idx1」として、ループさせます。
実行結果
[OK]を押すと、1行目の値が順番にメッセージボックスに表示されます。
Rangeについての補足
行に対して変数を使う場合
この記事を書くにあたり色々と調べたところ、上記のように「Range(“A” & Idx)」といった書き方は好ましくないという情報が、複数ありました。
理由としては、以下の通りです。
・Cellsを使えば行・列ともに変数を使えるので、あえてRangeを使う必然性が無い。
・強引な記述方法に思える
・性能がやや劣る
しかし、あくまで個人的な主観ですが、私としてはどちらでも良いと思っています。
Rangeのほうが、関数で使うような見慣れた形式に思えますし、性能をシビアに求められるような場合は、セル範囲をまとめて配列に格納して、配列を操作するという、有識者には割と知られたテクニックがあります。
※以下の記事をご参照ください。
列に対して変数を使う場合
Rangeの場合でも、文字コードを使ったり、列の名前を格納した配列を別に用意したりすれば、出来ないことはありません。
しかし、手間がかかるうえに、コードが読みづらくなるため、そこまでする必然的は無いと思います。
コメント