セルの範囲の指定方法の注意点
創成図を描画するために2次元配列を使用していると、VBAの言語使用による厄介な点と数多く出会います。
2次元配列を高速でセルに書き込むためには、"Range(Cells(※, ※), Cells(※, ※))" でセルの範囲を指定して、そこに配列全体を書き込むと良いことを前回の記事で説明しました。
"Range(Cells(※, ※), Cells(※, ※))" はVBAの言語仕様を理解していないと嵌ってしまう点があるので、今回はその嵌ってしまう点と対処法を説明しようと思います。
Range(Cells(※, ※), Cells(※, ※))の注意点
VBAは省略した記載方法を受け付けたり、要請する縛りが緩く様々な記載方法があったりします。これによって、初心者がサンプルプログラムを見て言語を学習し始める時には非常にとっつきやすいですが、いざ自分でコードをアレンジして書いていこうとした時に嵌ってしまう原因となります。
"Range(Cells(※, ※), Cells(※, ※))" はその典型的な例であり、これを使用してエラーとなる現象を下の確認用のプログラムで見てみましょう。
確認用プログラム
下のように2行3列の2次元配列を準備して、指定したセルの範囲に書き込んでみます。
Option Explicit
Sub test_input()
Dim tmpArray(1, 2) As Variant
tmpArray(0, 0) = 1
tmpArray(0, 1) = 2
tmpArray(0, 2) = 3
tmpArray(1, 0) = 4
tmpArray(1, 1) = 5
tmpArray(1, 2) = 6
Sheet5.Range(Cells(3, 4), Cells(4, 6)) = tmpArray
End Sub
エラーの現象の確認
上のプログラムを実行して、エラーになる現象を下の動画にしました。
動画の前半は上手くプログラムが実行される場合を確認し、後半でエラーとなる状況を確認してください。
書き込むシートを開いてプログラムを実行した時はOKで、エラーとなっているのは別のシートを開いている状態でプログラムを実行した時のようです。
Sheet5.Range～ とシートを指定しているにも関わらずエラーとなるのは何故なのかが問題です。
（今回、"Sheet5."とシートのオブジェクト名で指定していますが、様々なシートの指定方法がVBAにはあり、今回の記事の最後に【補足】として説明しています。）
セルへの書き込み方法は様々な方法があり、それぞれどのような結果になるか下で検証してみました。
様々なセルへの書き込み方法での検証
以下のコードのように9種類のセルへの書き込み方法により、どのような結果になるのか検証してみました。
Sub test_input1()
Dim tmpArray(1, 2) As Variant
tmpArray(0, 0) = 1
tmpArray(0, 1) = 2
tmpArray(0, 2) = 3
tmpArray(1, 0) = 4
tmpArray(1, 1) = 5
tmpArray(1, 2) = 6
'9つのセルへの書き込み方法
Sheet5.Range(Cells(4, 4), Cells(5, 6)) = tmpArray '-----------------①
Range("D7") = tmpArray(0, 0) '--------------------------------------②
Sheet5.Range("D8") = tmpArray(0, 0) '-------------------------------③
Cells(9, 4) = tmpArray(0, 0) '--------------------------------------④
Sheet5.Cells(10, 4) = tmpArray(0, 0) '------------------------------⑤
Range(Cells(12, 4), Cells(13, 6)) = tmpArray '----------------------⑥
Range("D14:F15") = tmpArray '---------------------------------------⑦
Sheet5.Range("D16:F17") = tmpArray '--------------------------------⑧
Sheet5.Range(Sheet5.Cells(18, 4), Sheet5.Cells(19, 6)) = tmpArray '-⑨
End Sub
①から⑨までのセルへの書き込み書式を検証した結果が下の図になります。
エラーとなるのは①のみで、”Range"と"cells"の両方を使用して別シートからの実行でも正しく動作するのは⑨となります。
ここまで見れば、原因はお分かりになると思います。
”Range"と"cells"の前にはどのシートを対象とするのか指定することができ、指定がない場合は現在開いているシートが対象となります。
何もシートを指定していない⑥の場合は、下の赤字で記載しているコードが省略されています。
ActiveSheet.Range(ActiveSheet.Cells(18, 4), ActiveSheet.Cells(19, 6)).value = tmpArray '-⑨
現在開いているシートは "ActiveSheet" であり、値を代入するという意味の".value" も省略されています。
何が省略されているかが理解できれば、対応方法は予想が付きやすくなるでしょう。
お勧めの対応方法
"Range(Cells(※, ※), Cells(※, ※))" を正しく使うには、同じシートの指定を3度もしなければならず、面倒ですし可読性も悪くなります。
よって、"Withステートメント" を使用して、いつも以下のように書くようにしておくのが、良いでしょう。
With シートを指定する名称 .Range(.Cells(12, 4), .Cells(13, 6)) = tmpArray End With
【補足】シートの指定方法
シートを指定する方法には主に以下の3つがあります。
- オブジェクト名で指定する方法
- シート名で指定する方法
- アイテム番号で指定する方法
シート名で指定する方法が、エクセルの画面で常にシート名が表示されているため、最初のうちは一番馴染みやすいかもしれません。しかし、シート名はエクセル画面で簡単に変更することが出来るため、変更するとシート名で指定されたプログラムはエラーになってしまう欠点があります。
オブジェクト名で指定する方法は、下図のように、Visual Basic Editor（VBE）からしかシートのオブジェクト名を確認出来ず、最初は分かりづらいかもしれませんが、プロパティ ウィンドウでシート名も自由に変更することができ、シート名の変更にも影響されないため、一番確実な指定方法と言えるでしょう。
アイテム番号はエクセル画面のシートタブの左から順に、１，２，３，・・・と連番となり、シートの増減や入れ替えが発生するような特殊な状況なら、有効な指定方法になりそうです。
その他に、省略可能ですが現在開いているシートを指定する”ActiveSheet"で指定する方法や、シート（ワークシートオブジェクト）に変数名をSetして指定する方法もあり、以下に様々なシートの指定方法を列挙しています。
Sheet5.Range("A1") = 1 'シートのオブジェクト名で指定
Worksheets("書き込み").Range("A2") = 2 'シート名で指定①
Sheets("書き込み").Range("A3") = 3 'シート名で指定②
Worksheets.Item(5).Range("A4") = 4 'シートのアイテム番号で指定①
Sheets.Item(5).Range("A5") = 5 'シートのアイテム番号で指定②
Worksheets(5).Range("A6") = 6 'シートのアイテム番号で指定③（Itemを省略可）
Sheets(5).Range("A7") = 7 'シートのアイテム番号で指定④（Itemを省略可）
ActiveSheet.Range("A8") = 8 '現在開いているシートを指定（省略可）
Dim ws As Worksheet
Set ws = Worksheets("書き込み")
ws.Range("A9") = 9 'ワークシートオブジェクトの変数名をSetして指定
最後のワークシートオブジェクトの変数名をSetして指定する方法では
Dim ws As Worksheet
上の記述では、wsをワークシートオブジェクトとして宣言しているので、"WorkSheet"は単数形になります。
Set ws = Worksheets("書き込み")
上の"Worksheets("書き込み")" は、ワークシート コレクションの中の"書き込み" という名のオブジェクトを示しているので、"WorkSheets"は複数形になります。
また、"Worksheets("書き込み")" は、オブジェクト名やアイテム番号を用いても構いません。
まとめ
今回の記事でお話ししたように、"セルへの値の書き込み方法" と "シートの指定方法" には様々な記述方法と省略することが出来る記述が存在します。
きちんと理解していないと、何度も同じところでプログラムがエラーとなり、対応するのに手間取ることが多くなります。
筆者もそうでした（です？）。
本格的にVBAに取り組むのなら、一度、きちんと勉強して理解することをお勧めいたします。
⇒次回記事、配列をセルに書き込む時、途中に空白行が欲しい場合へ