Excel
VBA
ExcelVBA
VBAマクロ

EXCEL VBA -Range型について

Range型について

この記事ではEXCEL VBAにおいて頻繁に使用するRange型の扱いについて記述する。

Range型の宣言

Range型を宣言します。
※Range型の変数を宣言せず、Sheet1.Cells(1,1).??と記入するとメンバー候補は表示されません。
 Sheet1.Cells(1,1)をRange変数に代入してから使用するとメンバーの候補が表示されるようになります。

range型宣言
Dim a As Range

Set a = Sheet1.Cells(1,1)

様々なRange型の取得方法

引数としてRange型を取得

計算式に記述され、対象セル範囲を引数として受け取る。

例:
image.png

Function Test(a As Range)
    Test = a.Cells(1, 1)
End Function

選択されているRangeを取得

セルが選択されている範囲をRangeとして取得します。
Selectionのタイプ名がRangeか判定して処理を行ってください。

Sub Macro1()
    Dim a As Range

    If TypeName(Selection) <> "Range" Then
        Exit Sub
    End If

    Set a = Selection
End Sub

名前からRangeを取得

セルに付けられた名前を元にRangeを取得します。
image.png

Sub Macro1()
    Dim a As Range
    Dim shtAct As Worksheet

    Set shtAct = ActiveSheet

    Set a = shtAct.Range("テスト")

End Sub

Rangeの参照範囲の確認

Range.Addressに参照している範囲が格納されています。

Sub Macro1()
    Dim a As Range
    Dim shtAct As Worksheet

    Set shtAct = ActiveSheet

    Set a = shtAct.Range("テスト")

    Debug.Print a.Address
End Sub

結果:
image.png

WorksheetのUsedRangeの扱いについて

UsedRangeは保存済みの使用範囲を取得するのに便利でよく使いますが、気をつけないといけないこととがあります。
使用範囲がCell(1,1)から始まっていない場合があり、そうした時、UsedRange(1,1)の位置はWorksheet.Cells(1,1)と同じ位置ではなくなってしまいます。
そのため、基点を基準として考えていたコードにバグが発生する要員となります。

この問題を解決するにはいくつかの方法があります。
・EXCELシートの使用範囲開始位置が必ずWorksheet.Cells(1,1)となるよう修正し、その前提で処理を行う
・UsedRangeを取得する際に下記のコードで必ず記入するようにし、Worksheet.Cells(1,1)が基点となるようにする

Sub Macro1()
    Dim a As Range
    Dim shtAct As Worksheet

    Set shtAct = ActiveSheet

    Set a = shtAct.Range(shtAct.Cells(1, 1), shtAct.UsedRange(shtAct.UsedRange.Rows.Count, shtAct.UsedRange.Columns.Count))

End Sub

Rangeへの高速取得、代入

Rangeへの値の取得、代入は配列を使用したほうが早い。
データ件数が少ない場合は考慮しなくてもよいが、高速化したい場合は覚えておくと便利です。

RangeオブジェクトをVariant型のオブジェクトに代入すると多次元配列としてコピーされます。
Variantオブジェクトの値を編集し、再度、Rangeオブジェクトに格納すると値のみ更新されます。
※オブジェクトをセットするわけではないので、Set は不要

Sub Macro1()
    Dim a As Range
    Dim shtAct As Worksheet
    Dim vals As Variant

    Set shtAct = ActiveSheet

    Set a = shtAct.Range(shtAct.Cells(1, 1), shtAct.Cells(10, 10))

    vals = a

    vals(2, 2) = "10"

    a = vals
End Sub