VBAのRangeに違う座標が設定されている理由

Rangeが違う座標を指している？

Rangeオブジェクトにセル範囲を代入したのに、「Rangeオブジェクトがへんな座標になっている。ちゃんと設定しているはずなのに・・・」というようなセル座標がおかしくなっている状況になることがあります。

例えば、B2セルからC5セルまでの8個のセルをRangeオブジェクトの変数に入れているはずなのに、C3からD6になっている、という状況です。実際のExcelではこんな感じです。

これは、一度セル範囲を設定済みのRangeオブジェクトに対して、さらにRangeオブジェクトでセル範囲を重ねて指定するために起きます。

上の例を発生させるだけのコードであればこんな感じになります。

Sub RangeRangeTest1()
    Debug.Print Range("B2:C5").Range("B2:C5").Address(False, False)  '// C3:D6 が出力される
End Sub

Sub RangeRangeTest1()

Debug.Print Range("B2:C5").Range("B2:C5").Address(False, False) '// C3:D6 が出力される

End Sub

コードにはセル範囲として”B2:C5″しか書かれていませんが、Addressプロパティでセル範囲が”C3:D6″で出力されています。

「Range(“B2:C5”).Range(“B2:C5”)」は、左側のRange(“B2:C5”)のセル範囲の左上をA1とみなして、そこを基準として、右側のRange(“B2:C5”)が判定されます。左側のRange(“B2:C5”)のB2セルをA1セルとみなすため、そこから右側のRange(“B2:C5”)は、C3:D6になります。

このように、Range.Range、としてRangeを連続して書いた場合には、左側のRangeオブジェクトのセル範囲の左上を基準にして、右側のRangeオブジェクトで指定したセル範囲が判定されます。

RangeのRangeになる書き方１：Rangeオブジェクト変数を使う

最初の例では単純に「Range(“B2:C5”).Range(“B2:C5”)」と連続して書いていますが、実際のコードでは以下のような書き方もできます。

Rangeオブジェクトの変数を用意しておき、そこにRangeオブジェクトでセル範囲を指定して代入する方法です。このサイトではこの書き方をよくします。

Sub RangeRangeTest()
    Dim r   As Range
    Dim r2  As Range
    
    Set r = Range("B2:C5")
    Set r2 = r.Range("B2:C5")
    
    Debug.Print r2.Address(False, False)  '// C3:D6 が出力される
End Sub

Sub RangeRangeTest()

Dim r As Range

Dim r2 As Range

Set r = Range("B2:C5")

Set r2 = r.Range("B2:C5")

Debug.Print r2.Address(False, False) '// C3:D6 が出力される

End Sub

最初の例と同じ結果になります。Rangeオブジェクトの変数を２つ用意していて、それぞれに”B2:C5″の座標を設定しています。にも関わらず、コードに書いていない”C3:D6″が出力されます。

問題なのは、6行目の「Set r2 = r.Range(“B2:C5”)」の「r.」の部分です。

5行目で変数rにはRange(“B2:C5”)が設定されています。6行目の「r.Range(“B2:C5”)」は書き換えると「Range(“B2:C5”).Range(“B2:C5”)」となります。このような書き方はエラーではなく、コードとしては問題なく動作します。

ただ、Rangeオブジェクト変数を使ってコードを書いている人は、おそらく概念をちゃんと理解していることが多いと思いますので、そもそもRange.Rangeのような罠にハマることは少ないのかもしれません。

RangeのRangeになる書き方２：Withを使う書き方

おそらくRange.Rangeの罠にハマる書き方で多いのはWithを使ったコードを書いている場合が考えられます。

Withを使ったコードはマクロの記録機能を使った場合に見ることがよくあります。

マクロの記録で、セル範囲の黄色背景色＋赤文字＋太字、を設定するとこのようなコードが記録されます。

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("B12:D17").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    Selection.Font.Bold = True
End Sub

Sub Macro1()

' Macro1 Macro

Range("B12:D17").Select

With Selection.Interior

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

.Color = 65535

.TintAndShade = 0

.PatternTintAndShade = 0

End With

With Selection.Font

.Color = -16776961

.TintAndShade = 0

End With

Selection.Font.Bold = True

End Sub

ここで注目すべきなのはWithを使っている２か所です。「With Selection.Interior」と「With Selection.Font」の部分です。WithとEnd Withの間には、Withで指定されたオブジェクトのプロパティが書かれています。このように親オブジェクトを省略して書くことが出来るのですが、理解していないのであれば使わない方がいいです。

このWithですが、Rangeオブジェクトでも使えます。上のコードをWithを使って書くとこんな感じになります。

Sub RangeRangeTest2()
    Dim r   As Range
    Dim r2  As Range
    
    Set r = Range("B2:C5")
    
    With r
        Set r2 = .Range("B2:C5")
    End With
    
    Debug.Print r2.Address(False, False)  '// C3:D6 が出力される
End Sub

Sub RangeRangeTest2()

Dim r As Range

Dim r2 As Range

Set r = Range("B2:C5")

With r

Set r2 = .Range("B2:C5")

End With

Debug.Print r2.Address(False, False) '// C3:D6 が出力される

End Sub

問題なのは「Set r2 = .Range(“B2:C5”)」の部分です。「.Range」がWithに掛かっていることを分かる人は相当慣れている人です。初心者の方はなかなか気づかないでしょう。

Withを使ってこのような書き方をすると、どの部分がRange.Rangeの形になっているのかが分かりにくくなります。

Range.Rangeを書くのではなくOffsetを使いましょう

上で書いたとおり、Range.Rangeの形になるセル範囲の書き方はやめておいた方がいいです。

コードに書いた”B2:C5″などの座標とは全く違う座標が参照されることになり、コードを追いかけないと本当に参照しているセル範囲が分かりません。これはどんなに熟練のプログラマーでも同じです。

Range.Rangeの書き方はVBAのコードとしては正しいですが、Excelの操作を行う上ではまず必要ありません。

座標を変えたい場合は以下のようにRangeオブジェクトのOffsetプロパティを使えば可能です。

Sub RangeRangeTest3()
    Dim r   As Range
    
    Set r = Range("B2:C5")
    Debug.Print r.Address(False, False)     '// B2:C5　が出力される
    
    '// 下に1、右に2移動
    Set r = r.Offset(1, 2)
    Debug.Print r.Address(False, False)     '// D3:E6　が出力される
End Subこ

Sub RangeRangeTest3()

Dim r As Range

Set r = Range("B2:C5")

Debug.Print r.Address(False, False) '// B2:C5　が出力される

'// 下に1、右に2移動

Set r = r.Offset(1, 2)

Debug.Print r.Address(False, False) '// D3:E6　が出力される

End Subこ

このコードは、元々B2:C5のセル範囲を保持していたRangeオブジェクト変数のrに対して、下に1、右に2、の部分にあるセル範囲を代入しなおしています。代入後はD3:E6のセル範囲が変数rに保持されます。

Offsetプロパティについては「VBAで基準セルから相対セルを参照する（Offset）」をご参照ください。

Rangeが違う座標を指している？

RangeのRangeになる書き方１：Rangeオブジェクト変数を使う

RangeのRangeになる書き方２：Withを使う書き方

Range.Rangeを書くのではなくOffsetを使いましょう

関連記事