VBAクイズ問1～問5の解説 Excel VBAのRange操作の注意事項

今回は先日投稿したクイズの問1～問5のRangeに関する問題について解説していきたいと思います。

前の記事
解答
解説について
基礎知識
解説
まとめ
続編

f:id:Kotori-ChunChun:20190616230553p:plain

出題ページは下記になります。

まだ挑戦してない方は、本記事を読む前にやってみてくださいねー。

www.excel-chunchun.com

解答

まずは全体の正解を発表しておきます。

下記の表を文字列に設定したエクセルに貼って、適当な列だけ初期設定を標準にして、回答と下記の正解をイコールで結んで貰えば採点は容易にできると思います。

設問	正解
---------- 問1 ----------
1.	4
2.	3
---------- 問2 ----------
1.	$A$2:$B$3
---------- 問3 ----------
1.	$A$2
2.	$B$3
3.	$C$6
---------- 問4 ----------
1.	2
---------- 問5 ----------
1.	2
---------- 問6 ----------
1.
---------- 問7 ----------
1.	1 2 3
---------- 問8 ----------
1.	-999-666
2.	333
---------- 問9 ----------
1.	10
---------- 問10 ----------
1.	時 : 03
2.	分 : 01
3.	秒 : 05
---------- 問11 ----------
1.	2
2.	3
---------- 問12 ----------
1.	Then
---------- 問13 ----------
1.	Else
---------- 問14 ----------
1.	Else
---------- 問15 ----------
1.
---------- 問16 ----------
1.	Error
2.	Else
3.	存在する
---------- 問17 ----------
1.
2.	ブック２
---------- 問18 ----------
1.	Sheet1
---------- 問19 ----------
1.	tmp
---------- 問20 ----------
1.

解説について

まず初めに申し上げて起きますが、ここで解説する内容は実際に実行した結果から中の動きを推測して説明しているものです。

MSの記事等により明確な根拠があるわけではないため、正確性に欠けるかもしれない点にご注意ください。

また間違いや補足、不明な点がありましたら、遠慮なく教えてください。

一度に20問全てを説明すると長くなりそうなので、今回はRangeオブジェクトに関する問1～問5について説明していきます。

基礎知識

まずは本記事で使用しているプロパティのリンク集を貼っておきます。

解説

問１ Offsetに関する問題

Sub q_01()
    'A2:B3のセルを結合
    Range("A2").Resize(2, 2).Merge
    '1. A2の一つ下のセルの行番号を出力
    Debug.Print "" & Range("A2").Offset(1).Row
    '2. A1の一つ下のセルの行番号を出力
    Debug.Print "" & Range("A1").Offset(2).Row
End Sub

設問	正解
1.	4
2.	3

この問題の焦点は 結合セルをオフセットするとどのような動きをするのか です。

初っ端からマイナーな問題で申し訳ございません。

まず1行目ではResizeプロパティを使って、A2を縦2、横2の大きさに拡張した上で結合しています。

よくVBAの序盤の解説でRange(Range("A2"), Range("B3"))みたいなのを見ますが、これを正しく書くとActiveSheet.Range(ActiveSheet.Range("A2"), ActiveSheet.Range("B3"))になってしまうため、非常にわかりにくいと思うのですが私だけでしょうか。

おまけにWorkBooks("hoge.xlsx").WorkSheets(1).Range(Range("A2"), Range("B3"))を実行しようとしてエラーが出る等のバグの温厚となってますし。

Resizeならシートは最初の1回の指定で済みますし、領域の大きさを一目で理解できるのでとてもオススメです。

またResizeにはこれから説明するOffsetのような変な癖も無いので、安心して使うことが出来ます。

(1) Range("A2").Offset(1).Row = 4

Range("A2").Offset(1)は A2を選択してから、十字キーで一つ下のセルに移動したところのセルを指定 したという感じです。

A2を選択すると結合されているので実際にはA2:B3が目標となり、そこから下に動くとA4が目標となるわけですね。

(2) Range("A1").Offset(2).Row = 3

(1)の考え方で行くなら「4」になりそうなものですが、実際には「3」となり (1)の考え方が破綻 しました。

ちなみにRange("A1").Offset(1).Offset(1).Rowとすると(1)と同じ結果となります。

ここから分かったこと Offsetプロパティを呼び出したセルが結合セルの場合のみ挙動が変わる ということです。

そんなわけで慣れれば便利なOffsetですが、必要無い限りは使わないほうが良い です。

セルを一行下にシフトするならRange("A2").Cells(2,1).Rowとすれば再現できますから必要ありません。

複数セルをシフトするRange("A1:B2").Offset(1,1)みたいなのだと代替式を作るのは難しくなりますが、領域を指定する段階でCellsとResizeでシフトさせたほうが良いと思います。

逆にOffsetを使うのは セルの結合を考慮してシフトしたい時 くらいだと思います。

問２ Selectに関する問題

Sub q_02()
    'A2:B3のセルを結合
    Range("A2").Resize(2, 2).Merge
    'A2を選択
    Range("A2").Select
    '選択中のセルのアドレスを出力
    Debug.Print Selection.Address
End Sub

設問	正解
1.	$A$2:$B$3

この問題の焦点は SelectしたセルとSelectionは常に同一か？ ですね。

Selectionを使ったことがある人なら、言わずとも知れたサービス問題です。

Range("A2")ではA2しか選択していないように見えますが、指定されたセルが結合セルに含まれている場合は結合セル全体が選択状態になるため、SelectionはA2:B3になります。

ちなみにRange("A3").Select、Range("B2").Select、Range("B3").Selectとした場合も同様です。

（完全にイコールと見て良いか確認出来ていませんが）まず .MergeAreaプロパティ`を指定したのと同じ 結果になると考えて良いと思います。

もし安全な書き方にしたいなら、次のようにSelectionを使わない書き方を考えてください。

Sub q_02_better()
    With Range("A2").Resize(2, 2)
        .Merge
        .Select
        Debug.Print .Address
    End With
End Sub

問３ Cellsに関する問題

Sub q_03()
    Debug.Print Range("A1:C5").Cells(4).Address
    Debug.Print Range("C3:E5").Cells(0).Address
    Debug.Print Range("C3:C5").Cells(4).Address
End Sub

設問	正解
1.	$A$2
2.	$B$3
3.	$C$6

これはもう悪魔のような問題です。

回答を収集していないので想像ですが、 正答率0% ではないかと思います。

もし正解した人はなんで知ってるのか教えて欲しいくらいです。

(1) Range("A1:C5").Cells(4).Address = A2

１つ目のこれなら知ってる人はいるかと思います。

Microsoftの解説を翻訳すると、Cellsプロパティは本来Rangeオブジェクトを返すのですが、Rangeオブジェクトの既定のプロパティがItemプロパティであるために、Cellsの直後にはItemプロパティのパラメータである行と列が指定できます。

そしてRange.Itemプロパティの解説にはexpression.Item(RowIndex, ColumnIndex)と書いてあるのですが、よく読むとRowIndexの解説にはさり気なくRange.Item(2)は左上端のセルの右側のセルを返します。などと書かれており、さらにはRowIndex 必須、ColumnIndex 省略可能とも書いてありますので、第一パラメータしか書かない方法 が正式に認められているようです。

さて、覚えても仕方がない意味不明な文章が続きましたが、要するに Cellsに第一パラメータしか指定していない場合 は、こう動きますよってことです。

＼	A	B	C
1	Cells(1) = A1	Cells(2) = B1	Cells(3) = C1
2	Cells(4) = A2	Cells(5) = B2	Cells(6) = C2
3	Cells(7) = A3	Cells(8) = B3	Cells(9) = C3
4	Cells(10) = A4	Cells(11) = B4	Cells(12) = C4
5	Cells(13) = A5	Cells(14) = B5	Cells(15) = C5

範囲の先頭が1で列→行の順でカウントアップされている形ですね。

で、頭の良い人は’はっと’したかもしれませんが、実はこれ、セル範囲をfor eachで回したときと同一の順番になります。

Sub q_03_1_foreach()
    Dim rng As Range
    For Each rng In Range("A1:C5")
        Debug.Print rng.Address
    Next
End Sub

だからこんな風な使い方ができます。

Sub q_03_1_forCount()
    Dim i As Long
    With Range("A1:C5")
        For i = 1 To .Count
            Debug.Print .Cells(i).Address
        Next
    End With
End Sub

たとえばセル範囲を 一段階のループで巡回したいけどカウンターの数値も欲しい場合 に使えると思います。滅多に無いけど。

大抵の人は Cellsで第二パラメータを省略すると痛い目見るゾ☆ とだけ覚えといてくださいませ。

(2) Range("C3:E5").Cells(0).Address = B3

そして本当に意味不明な問題がこちら。

こんな書き方ぜってーしねーよ！ という声はさておき、先のように表に起こすとこうなりました。

＼	A	B	C	D	E
3	？	Cells(0) = B3	Cells(1) = C3	Cells(2) = D3	Cells(3) = E3
4	？	？	Cells(4) = C4	Cells(5) = D4	Cells(6) = E4
5	？	？	Cells(7) = C5	Cells(8) = D5	Cells(9) = E5

解説？無理っす。謎すぎ意味わからん。

で、終わらせたら叱られそうなので、もうひといき頑張りましょう。

しかしC3:E5では検証するスペースが足りないので、D4:E5の縦横2x2に変更しまして次のようなコードを実行して、0以下のマイナス方向についても調べてみます。

Sub q_03_2_hukayomi()
    On Error Resume Next
    Dim i As Long
    For i = 7 To -10 Step -1
        Debug.Print "Cells(" & i & ") = " & Range("D4:E5").Cells(i).Address(False, False)
    Next
    On Error GoTo 0
End Sub

そして出力結果を表に起こすとこうなりました。

＼	A	B	C	D	E
1	？	？	Cells(-6) = C1	Cells(-5) = D1	？
2	？	？	Cells(-4) = C2	Cells(-3) = D2	？
3	？	？	Cells(-2) = C3	Cells(-1) = D3	？
4	？	？	Cells(0) = C4	Cells(1) = D4	Cells(2) = E4
5	？	？	？	Cells(3) = D5	Cells(4) = E5
6	？	？	？	Cells(5) = D6	Cells(6) = E6
6	？	？	？	Cells(7) = D7	Cells(8) = E7

なるほど、そういうことか！！！

マイナス方向は領域の開始位置を末尾として、列幅を保ったまま上方向へ。

プラス方向は開始位置を起点として、列幅を一定に保ったまま下方向へ。

という法則のようです。

解説は以上。だって需要ないもの。

(3) Range("C3:C5").Cells(4).Address = C6

まあ(2)の説明で大体分かりますよね。

元の範囲がC列しか無いので、1列の幅でC3から下方向に4番目の位置なのでC6になります。

一応表も貼っておきます。

＼	A	B	C
1	？	？	Cells(-1) = C1
2	？	？	Cells(0) = C2
3	？	？	Cells(1) = C3
4	？	？	Cells(2) = C4
5	？	？	Cells(3) = C5
6	？	？	Cells(4) = C6
7	？	？	Cells(5) = C7

問４ Range型変数に関する問題1

Sub q_04()
    Dim rng As Range
    'rngにセルA1の位置を示す参照を格納
    Set rng = Range("A1")
    '1行目に新規セルを挿入
    Rows(1).Insert xlDown
    'rngの行数を出力
    Debug.Print "" & rng.Row
End Sub

設問	正解
1.	2

この問題は Rangeを変数に格納した状態で行の挿入などを行うと動くよー って話です。

オブジェクト型変数が覚えているのは インスタンスの位置を示すメモリアドレス だけなので、エクセルで行の挿入が行われるとRange.Itemから見ればアドレスが動くわけですねー。

だから挿入前のRange("A1")と、挿入後のRange("A1")は完全に別のインスタンスです。

表にすると

＼	A	B	C
1	rng	？	？	←この行の前にNEW挿入
2	？	？	？

が

＼	A	B	C
1	NEW	NEW	NEW	←挿入された行
2	rng

となるわけです。

問５ Range型変数に関する問題2

Sub q_05()
    Dim rng As Range
    'rngにセルH1の位置を示す参照を格納
    Set rng = Range("H1")
    'セルH1を切り取って、H2に貼り付け
    rng.Cut Range("H2")
    'rngの行数を出力
    Debug.Print "" & rng.Row
End Sub

設問	正解
1.	2

これも問4と考え方は一緒

「切り取り」コマンドは 指定したインスタンスの位置（Range.Itemのアドレス）を移動する コマンドです。

で、「貼り付け」先のインスタンスはどうなったかと言うと 消され ます。

事前に変数にRange("H2")を格納しておいた場合、Nothingになっているはずです。

もちろん「削除」コマンドなんかも同様の現象が起こります。

まとめ

今回はRangeオブジェクトに関する問題でしたがいかがでしたか？

Rangeと言えば、VBAにとっては切っても切れない関係の基本中の基本なわけですが、知れば知るほど謎は深まるばかり。

私も記事を書きながら、なんつー難しいオブジェクトだと改めて実感しました。

そんなところもかわいいExcel VBAちゃんですが、末永く付き合っていただければと思います。

以上

続編

問6～問8の解説 VBAの配列処理の注意事項

www.excel-chunchun.com

何か御座いましたらコメント欄、またはTwitterからどうぞ♪

それではまた来週♪　ちゅんちゅん(・8・)

えくせるちゅんちゅん

ことりがエクセルをちゅんちゅんするブログ