#Range型について
この記事ではEXCEL VBAにおいて頻繁に使用するRange型の扱いについて記述する。
#Range型の宣言
Range型を宣言します。
※Range型の変数を宣言せず、Sheet1.Cells(1,1).??と記入するとメンバー候補は表示されません。
Sheet1.Cells(1,1)をRange変数に代入してから使用するとメンバーの候補が表示されるようになります。
Dim a As Range
Set a = Sheet1.Cells(1,1)
#様々なRange型の取得方法
##引数としてRange型を取得
計算式に記述され、対象セル範囲を引数として受け取る。
例：
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を取得します。
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
結果:
#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