VBA Rangeでセル範囲に付けた名前を参照

Excelでは、セルに任意の名前を付けることができる。
任意のセルを選択した状態で、数式バーの左にある名前ボックスに名前を入れてEnterで確定させるだけだ。
f:id:t-hom:20160707232924p:plain

セル範囲に対しても同様の操作ができる。
f:id:t-hom:20160707233152p:plain

付けた名前は数式で参照させることができる。
f:id:t-hom:20160707233244p:plain

行や列を挿入すると、名前が指すセル範囲も自動的に移動してくれる。
f:id:t-hom:20160707233358p:plain

一度つけた名前を消したり範囲を変更するには数式タブから名前の管理を開く。
f:id:t-hom:20160707233607p:plain

すると次のようなダイアログで編集や削除ができる。
f:id:t-hom:20160707233710p:plain

さて、前置きが長くなったが、実はこの「名前」はVBAからシートのRangeプロパティでも参照できる。

標準モジュールに次のコードを入力して実行すると、

Sub hoge()
    Dim r As Range
    For Each r In Range("データ範囲")
        Debug.Print r.Value
    Next
End Sub

このとおり。
f:id:t-hom:20160707234103p:plain

さて、データの個数が固定されている場合はこれで十分だが、可変の場合はいちいち名前を変更するのはわずらわしい。
そんな時はテーブル機能を利用する。

ホームタブからテーブルとして書式設定を選択し、
f:id:t-hom:20160707235110p:plain

データ範囲を選択してOKをクリックする。
f:id:t-hom:20160707235234p:plain

するとテーブルが作成される。
テーブル内のセルを選択すると、デザインタブが表示されるので、そこでデザインを変更したりテーブルの名前を変えたりできる。
f:id:t-hom:20160707235549p:plain

デザインタブのプロパティグループにテーブル名があるので、ここを編集することでテーブル名を変更できる。
f:id:t-hom:20160707235706p:plain

そしてこのテーブル名も先ほどの「名前」と同じようにRangeプロパティで参照できるのだ。

Sub hoge()
    Dim r As Range
    For Each r In Range("テーブル1")
        Debug.Print r.Value
    Next
End Sub

しかもデータを追記すれば自動的に拡張される。

テーブルに対してVBAでもう少し高度な操作をしようと思ったらListObjectを使用することになるが、Rangeなら気軽に使えて良い。

ただし、「名前」や「テーブル名」はユーザーが変更可能な領域なので、どんなブックにでも使えるテクニックというわけではない。
利用シチュエーションによって最適なセルの指定方法は違うので、色々な方法を知って使いこなせるようになりたい。

t-hom’s diary