【VBA】名前定義をVBAで使用する方法

はじめに
Code
ブックとシート
1. ブックレベル
2. シートレベル
名前定義をVBAで定義
削除
SQLやADOとの併用
まとめ
蛇足

はじめに

ExcelVBAでは、タブ[数式]→[定義された名前]→[名前の定義]で定義された保存した名前定義を使用することができます。

よくシートでは使用する人が多いかもしれませんが、実はVBAでもその定義を使用することができます。今回はその使用方法、コードの使い方を紹介していきます。

Code

※名前管理の使用方法については既に知っている前提でお話します。

① 一番シンプルな書き方
※名前定義にて【住所一覧】という範囲を登録しているとする。

Sub Sample1()

    Dim rng As Range
    Set rng = Range("住所一覧")
    
    MsgBox rng.Count

End Sub

ポイントは

Range("名前") で直接取得できる
ブックレベルの名前ならどのシートからでもOK

これで変数”rng”には住所一覧という定義された範囲が代入されます。

ブックとシート

どこからでも呼び出せるブックレベルで保存されているものとシートレベルで保存されているものの２つが存在します。

ブックレベル

どのシートからでも呼べる

Range("売上範囲")

シートレベル

Sheet1にだけ定義された名前の場合

Worksheets("Sheet1").Range("売上範囲")

または

Range("Sheet1!売上範囲")

名前定義をVBAで定義

Sub AddName()

    ThisWorkbook.Names.Add Name:="売上範囲", RefersTo:="=Sheet1!$A$1:$A$10"

End Sub

シートレベルで作る場合

Sub AddName()
　　Worksheets("Sheet1").Names.Add ame:="売上範囲", RefersTo:="=Sheet1!$A$1:$A$10"
End Sub

名前定義する範囲が可変の時はこのコードを使うと良いかもしれません。
しかし個人的にはあまり使用することはオススメしません、なぜなら単純に可読性やメンテが面倒になるからです笑

削除

Sub DeleteName()
　　ThisWorkbook.Names("売上範囲").Delete
End Sub

SQLやADOとの併用

Sub Sample2()

    Dim rs As Object
    
    Set rs = GetRecordset() ' SQL取得処理
    
    With ThisWorkbook
        .Names("出力開始セル").RefersToRange.ClearContents
        .Names("出力開始セル").RefersToRange.CopyFromRecordset rs
    End With

End Sub

1.SQLでデータ取得
2.出力開始位置を名前で取得
3.一旦クリア
4.Recordsetを一気に貼り付け

まとめ

ポイントは：

ブックレベル／シートレベルを理解する
RefersToRangeの挙動を知る
ActiveWorkbook依存を避ける

このあたりを押さえれば、実務で困ることはほぼなくなります。

しかし、シートごとに名前定義を保存していてもシートをコピーすることで同じ名前の名前定義が出現するため、あまり多用することはおすすめしません。

例
【Sheet1　名前定義：AAA】 コピー
        ↓
【Sheet1(2)　名前定義：AAA】

このように同じ名前の名前定義が出来てしまうため、保守性があまり良くないという欠点がありませす。
そのため作成者でないものが引継いでプログラムを修正する時に親切でない設計になってしまうため多用することはオススメしません。もっと別なアプローチが必要だと私は感じました。

蛇足

万が一に名前定義がない状態でプログラムが走ってしまったときのセーフティを紹介します。

Sub NameSafe()

    Dim nm As Name
    
    On Error Resume Next
    Set nm = ThisWorkbook.Names("出力開始セル")
    On Error GoTo 0
    
    If nm Is Nothing Then
        MsgBox "名前定義が見つかりません。" & vbCrLf & _
               "処理を終了します。", _
               vbExclamation, "定義エラー"
        Exit Sub
    End If

    ' ▼ ここから本処理
    nm.RefersToRange.ClearContents
    nm.RefersToRange.CopyFromRecordset GetRecordset()

End Sub