セル・セル範囲に名前を定義・削除（Name プロパティ・Names コレクション）

2015/07/02 2017/11/01

セル・セル範囲に名前を定義（Name プロパティ）

Range オブジェクトのName プロパティを使用して、セルやセル範囲に、「名前」を定義します。

Name プロパティを使用し「名前」を定義すると、「ブックレベルの名前」として登録されます（エクセルの「名前管理」ダイアログボックスを表示した時、「範囲」の列に「ブック」と表示されます）。

Name プロパティ

【書式】

＜取得＞
result = Object.Name

＜設定＞
Object.Name = string

引数・戻り値

object ・・・対象となる Range オブジェクト
string ・・・セル・セル範囲に定義する「名前」を指定します。（文字列）
result ・・・セル・セル範囲に定義されている「名前」。指定した範囲に「名前」が定義されていない場合はエラーとなります。

セル・セル範囲に名前を定義・削除（Names コレクション）

Names コレクションは、セルやセル範囲に、「名前」を定義したり、削除したりすることができます。

Names コレクションのAdd メソッドを使用して「名前」を定義した場合は、「ブックレベルの名前」または、「ワークシートレベルの名前」のいずれかで登録することができます。（エクセルの「名前管理」ダイアログボックスを表示した時、「ブックレベル」の場合は、「範囲」の列に「ブック」と表示され、「ワークシートレベル」の場合は、「シート名」が表示されます）。

Names コレクション（Add メソッド）

【書式】

Object.Names.Add ( [ Name, RefersTo, Visible, MacroType, ShortcutKey, Category, NameLocal, RefersToLocal, CategoryLocal, RefersToR1C1, RefersToR1C1Local ] )

引数・戻り値

object ・・・対象となる Names オブジェクト
※ ここに、Workbook オブジェクトを指定すると、「ブックレベル」の Names コレクションを、Worksheet オブジェクトを指定すると、「シートレベル」の Names コレクションを、取得・設定することができます。
Name ・・・「名前」として使用する文字列を指定します。（省略可）
RefersTo ・・・「名前」と関連付けるセル範囲を指定します。参照が存在しない場合は、Nothing が返されます。（省略可）
Visible ・・・「名前」を表示する場合は True を指定、非表示にする場合は False を指定します。非表示の場合は、[名前の定義]、[名前の貼り付け]、および [ジャンプ] ダイアログボックスに表示されません。既定値は True です。（省略可）
MacroType ・・・マクロの種類を、次のいずれかの値で指定します。
1・・・ユーザー定義関数 (Function プロシージャ)
2・・・マクロ (Sub プロシージャ)
3・・・なし (定義した名前はユーザー定義関数またはマクロを参照しません)
既定値は 3 です。（省略可）
ShortcutKey ・・・マクロが設定されている場合のショートカットキーを指定します。（省略可）
Category ・・・引数 MacroType が 1 または 2 のときに、「名前」が参照するマクロや関数の分類を指定します。（省略可）
NameLocal ・・・引数 Name が指定されていない場合は、名前として使用する文字列（日本語等）を指定します。名前には、スペースを含めることはできません。（省略可）
RefersToLocal ・・・引数 RefersTo、RefersToR1C1、および RefersToR1C1Local が指定されていない場合は、A1 形式の表記法を使用して、文字列で名前の参照先を指定します。（省略可）
CategoryLocal ・・・引数 Category が指定されていない場合は、ユーザー設定関数の分類を示す文字列を指定します。（省略可）
RefersToR1C1 ・・・引数 RefersTo、RefersToLocal、および RefersToR1C1Local が指定されていない場合は、R1C1 形式の表記法を使用して名前の参照先を指定します。（省略可）
RefersToR1C1Local ・・・引数 RefersTo、RefersToLocal、および RefersToR1C1 が指定されていない場合は、R1C1 形式の表記法を使用して文字列（日本語等）で名前の参照先を指定します。（省略可）
result ・・・作成した「名前」を表す Name オブジェクト

Names コレクション（Item メソッド）

【書式】

Object.Names.Item ( [ Index, IndexLocal, RefersTo ] )

引数・戻り値

object ・・・対象となる Names オブジェクト
Index ・・・対象の定義名またはインデックス番号を指定します。
IndexLocal ・・・定義名の名前を日本語等（ユーザーの言語）で指定します。
RefersTo ・・・「名前」が参照するセル範囲を指定します。
result ・・・コレクションに含まれる Name オブジェクト

３つの引数は、省略可能ですが、必ず 1 つだけ引数を指定するようにしてください。

Names コレクション（プロパティ）

【書式】

Object.Names.Application

Object.Names.Count

Object.Names.Creator

Object.Names.Parent

項目の説明

object ・・・対象となる Names オブジェクト
Application ・・・オブジェクトを返すプロパティです。対象となるオブジェクトが指定されない場合は、Excel アプリケーション (Application オブジェクト) を返します。対象となるオブジェクトが指定された場合は、指定されたオブジェクトを作成した Application オブジェクトを返します。値の取得のみ可能です。
Count ・・・コレクションに含まれるオブジェクトの数を表す長整数型（Long）の値を返します。
Creator ・・・指定されたオブジェクトを作成したアプリケーションを、32 ビットの整数値で返します。値の取得のみ可能です。長整数型（Long）の値を使用します。
Parent ・・・指定されたオブジェクトの親オブジェクトを返します。値の取得のみ可能です。

「名前付きセル範囲」使用例

サンプルVBAソース

　
Sub Sample_Name()


    '名前付き範囲の作成

    Sheets("Sheet3").Activate
    
    'セル範囲「B3:H8」の名前を「売上表」（対象は、ブック）
    Application.Names.Add "売上表", Range("B3:H8")
    
    'セル範囲「B3:B8」の名前を「商品ID」（対象は、「Sheet3」）
    Worksheets("Sheet3").Names.Add _
                Name:="商品ID", _
                RefersTo:="=Sheet3!$B$3:$B$8"   '文字列を指定する場合は絶対参照を使用
            
    'セル範囲「J3:K10」の名前を「商品」（対象は、ブック）
    Worksheets(3).Range("J3:K10").Name = "商品一覧表"
    
    'セル範囲「C3:C8」の名前を「商品名」（対象は、ブック）
    Sheets("Sheet3").Range("C3:C8").Name = "商品名"
    
    
    '名前付き範囲の表示
    
    Dim i As Long, str As String, strtmp As String, Chrs As String
    
    With Application
    
        For i = 1 To .Names.Count
            
            '見栄えを整えるため
            If Len(.Names.Item(i).Name) > 4 Then
                Chrs = Chr(9)
            Else
                Chrs = Chr(9) & Chr(9)
            End If
            
            '「範囲の名前」と「アドレス」
            strtmp = .Names.Item(i).Name & Chrs & .Names.Item(i).RefersTo
            
            str = str & strtmp & Chr(13)
        
        Next i
    
    End With
    
        
    '作成した名前付き範囲の数と名前・アドレスを表示
    MsgBox str
        

    '名前付き範囲の変更・削除
    
    '「Sheet3!商品ID」を「Sheet3!合計」に変更
    '（「Sheet3!商品ID」は削除される）
    Names("Sheet3!商品ID").Name = "合計"

    '「商品名と単価」を「商品名」を元に作成
    '「商品名と単価」はそのまま残る
    Range("商品名").Name = "商品名と単価"

    '範囲を変更（文字列を指定　※ 絶対参照を使用する）
    Names("Sheet3!合計").RefersTo = "=Sheet3!$H$3:$H$8"

    '範囲を変更（Range オブジェクトを指定）
    Names("商品名と単価").RefersTo = Range("C3:D8")

    
    '変更・修正を表示
    
    str = "": strtmp = ""
    
    With Application
    
        For i = 1 To .Names.Count
            
            '見栄えを整えるため
            If Len(.Names.Item(i).Name) > 4 Then
                Chrs = Chr(9)
            Else
                Chrs = Chr(9) & Chr(9)
            End If
            
            '「範囲の名前」と「アドレス」
            strtmp = .Names.Item(i).Name & Chrs & .Names.Item(i).RefersTo
            
            str = str & strtmp & Chr(13)
        
        Next i
    
    End With

    MsgBox str
    

    '名前付き範囲を削除
    
    '「Sheet3」の名前付範囲を削除
    If ActiveSheet.Names.Count <> 0 Then

        Do While ActiveSheet.Names.Count > 0

            ActiveSheet.Names(ActiveSheet.Names.Count).Delete

        Loop

    End If
    
    '削除後を表示
    
    str = "": strtmp = ""
    
    With Application

        For i = 1 To .Names.Count
            
            '見栄えを整えるため
            If Len(.Names.Item(i).Name) > 4 Then
                Chrs = Chr(9)
            Else
                Chrs = Chr(9) & Chr(9)
            End If
            
            '「範囲の名前」と「アドレス」
            strtmp = .Names.Item(i).Name & Chrs & .Names.Item(i).RefersTo
            
            str = str & strtmp & Chr(13)
        
        Next i
    
    End With

    MsgBox str
    
    
    'すべての名前付範囲を削除
    If Application.Names.Count <> 0 Then

        Do While Application.Names.Count > 0

            Application.Names(Application.Names.Count).Delete

        Loop

    End If

    '削除後を表示
    
    str = "": strtmp = ""
    
    With Application
    
        For i = 1 To .Names.Count
            
            '見栄えを整えるため
            If Len(.Names.Item(i).Name) > 4 Then
                Chrs = Chr(9)
            Else
                Chrs = Chr(9) & Chr(9)
            End If
            
            '「範囲の名前」と「アドレス」
            strtmp = .Names.Item(i).Name & Chrs & .Names.Item(i).RefersTo
            
            str = str & strtmp & Chr(13)
        
        Next i
    
    End With

    If str = "" Then
        MsgBox "すべて削除されました"
    Else
        MsgBox str
    End If


End Sub

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

Sub Sample_Name()

'名前付き範囲の作成

Sheets("Sheet3").Activate

'セル範囲「B3:H8」の名前を「売上表」（対象は、ブック）

Application.Names.Add "売上表", Range("B3:H8")

'セル範囲「B3:B8」の名前を「商品ID」（対象は、「Sheet3」）

Worksheets("Sheet3").Names.Add _

Name:="商品ID", _

RefersTo:="=Sheet3!$B$3:$B$8" '文字列を指定する場合は絶対参照を使用

'セル範囲「J3:K10」の名前を「商品」（対象は、ブック）

Worksheets(3).Range("J3:K10").Name = "商品一覧表"

'セル範囲「C3:C8」の名前を「商品名」（対象は、ブック）

Sheets("Sheet3").Range("C3:C8").Name = "商品名"

'名前付き範囲の表示

Dim i As Long, str As String, strtmp As String, Chrs As String

With Application

For i = 1 To .Names.Count

'見栄えを整えるため

If Len(.Names.Item(i).Name) > 4 Then

Chrs = Chr(9)

Else

Chrs = Chr(9) & Chr(9)

End If

'「範囲の名前」と「アドレス」

strtmp = .Names.Item(i).Name & Chrs & .Names.Item(i).RefersTo

str = str & strtmp & Chr(13)

Next i

End With

'作成した名前付き範囲の数と名前・アドレスを表示

MsgBox str

'名前付き範囲の変更・削除

'「Sheet3!商品ID」を「Sheet3!合計」に変更

'（「Sheet3!商品ID」は削除される）

Names("Sheet3!商品ID").Name = "合計"

'「商品名と単価」を「商品名」を元に作成

'「商品名と単価」はそのまま残る

Range("商品名").Name = "商品名と単価"

'範囲を変更（文字列を指定　※ 絶対参照を使用する）

Names("Sheet3!合計").RefersTo = "=Sheet3!$H$3:$H$8"

'範囲を変更（Range オブジェクトを指定）

Names("商品名と単価").RefersTo = Range("C3:D8")

'変更・修正を表示

str = "": strtmp = ""

With Application

For i = 1 To .Names.Count

'見栄えを整えるため

If Len(.Names.Item(i).Name) > 4 Then

Chrs = Chr(9)

Else

Chrs = Chr(9) & Chr(9)

End If

'「範囲の名前」と「アドレス」

strtmp = .Names.Item(i).Name & Chrs & .Names.Item(i).RefersTo

str = str & strtmp & Chr(13)

Next i

End With

MsgBox str

'名前付き範囲を削除

'「Sheet3」の名前付範囲を削除

If ActiveSheet.Names.Count <> 0 Then

Do While ActiveSheet.Names.Count > 0

ActiveSheet.Names(ActiveSheet.Names.Count).Delete

Loop

End If

'削除後を表示

str = "": strtmp = ""

With Application

For i = 1 To .Names.Count

'見栄えを整えるため

If Len(.Names.Item(i).Name) > 4 Then

Chrs = Chr(9)

Else

Chrs = Chr(9) & Chr(9)

End If

'「範囲の名前」と「アドレス」

strtmp = .Names.Item(i).Name & Chrs & .Names.Item(i).RefersTo

str = str & strtmp & Chr(13)

Next i

End With

MsgBox str

'すべての名前付範囲を削除

If Application.Names.Count <> 0 Then

Do While Application.Names.Count > 0

Application.Names(Application.Names.Count).Delete

Loop

End If

'削除後を表示

str = "": strtmp = ""

With Application

For i = 1 To .Names.Count

'見栄えを整えるため

If Len(.Names.Item(i).Name) > 4 Then

Chrs = Chr(9)

Else

Chrs = Chr(9) & Chr(9)

End If

'「範囲の名前」と「アドレス」

strtmp = .Names.Item(i).Name & Chrs & .Names.Item(i).RefersTo

str = str & strtmp & Chr(13)

Next i

End With

If str = "" Then

MsgBox "すべて削除されました"

Else

MsgBox str

End If

End Sub

実行結果

作成した「名前付き範囲」の名前・アドレスを表示

修正した「名前付き範囲」の名前・アドレスを表示

シートレベルの「名前付き範囲」を削除したあとの「名前付き範囲」を表示

すべての「名前付き範囲」を削除

まとめ

「名前」の作成

　
Sub Make_Name()

    'Names.Add メソッド「ブックレベル」
    ThisWorkbook.Names.Add Name:="テスト１", RefersTo:=Range("B4:B8")
    
    'Names.Add メソッド「シートレベル」
    ActiveSheet.Names.Add "テスト２", Range("C4:C8")
    
    'Name プロパティ「ブックレベル」
    Range("D4:D8").Name = "テスト３"
    
    'Names.Add メソッド「ブックレベル」文字列で指定
    Names.Add "テスト４", "=Sheet3!$E$4:$E$8"
    
    'Names.Add メソッド「シートレベル」
    Worksheets("Sheet3").Names.Add "テスト5", Range("F4:F8")
　
End Sub

Sub Make_Name()

'Names.Add メソッド「ブックレベル」

ThisWorkbook.Names.Add Name:="テスト１", RefersTo:=Range("B4:B8")

'Names.Add メソッド「シートレベル」

ActiveSheet.Names.Add "テスト２", Range("C4:C8")

'Name プロパティ「ブックレベル」

Range("D4:D8").Name = "テスト３"

'Names.Add メソッド「ブックレベル」文字列で指定

Names.Add "テスト４", "=Sheet3!$E$4:$E$8"

'Names.Add メソッド「シートレベル」

Worksheets("Sheet3").Names.Add "テスト5", Range("F4:F8")

End Sub

「名前」の情報取得

　
Sub Info_Name()
  
    '定義されている「名前」の数（すべて）
    MsgBox Names.Count
    
    '定義されている「名前」の数（「シートレベル」のみ）
    MsgBox ActiveSheet.Names.Count
    
    '「名前」のインデックス番号を取得
    MsgBox Names.Item("テスト４").Index
    
    '「名前」に関連付けられた範囲のアドレスを取得（A1形式）
    MsgBox Application.Names(1).RefersTo
    
    '「名前」に関連付けられた範囲のアドレスを取得（R1C1形式）
    MsgBox Worksheets("Sheet3").Names.Item(1).RefersToR1C1
　
End Sub

Sub Info_Name()

'定義されている「名前」の数（すべて）

MsgBox Names.Count

'定義されている「名前」の数（「シートレベル」のみ）

MsgBox ActiveSheet.Names.Count

'「名前」のインデックス番号を取得

MsgBox Names.Item("テスト４").Index

'「名前」に関連付けられた範囲のアドレスを取得（A1形式）

MsgBox Application.Names(1).RefersTo

'「名前」に関連付けられた範囲のアドレスを取得（R1C1形式）

MsgBox Worksheets("Sheet3").Names.Item(1).RefersToR1C1

End Sub

「名前」のセル範囲取得・セル範囲の変更

　
Sub Range_Name()
  
    '「名前」のセル範囲を選択（Range プロパティ）
    Range("テスト5").Select
    
    '「名前」のセル範囲を選択（RefersToRange プロパティ）
    Names("テスト5").RefersToRange.Select
    
    '「名前」のセル範囲を変更
    Names("テスト5").RefersTo = Range("H2:J7")
    
    '「名前」のセル範囲を変更（文字列で指定）
    Names("テスト5").RefersTo = "=Sheet1!$H$2:$J$7"
　
End Sub

Sub Range_Name()

'「名前」のセル範囲を選択（Range プロパティ）

Range("テスト5").Select

'「名前」のセル範囲を選択（RefersToRange プロパティ）

Names("テスト5").RefersToRange.Select

'「名前」のセル範囲を変更

Names("テスト5").RefersTo = Range("H2:J7")

'「名前」のセル範囲を変更（文字列で指定）

Names("テスト5").RefersTo = "=Sheet1!$H$2:$J$7"

End Sub

「名前」の削除

　
Sub Delet_Name()
  
    '「ブックレベル」の「名前」を削除
    Application.Names.Item("テスト１").Delete
    
    '「シートレベル」の「名前」を削除
    ActiveSheet.Names.Item("テスト２").Delete
    
    
    Dim v
    '「シートレベル」の「名前」をまとめて削除
    For Each v In Sheets("Sheet3").Names
    
        MsgBox v.Name
        v.Delete
    
    Next v
    
    'すべての「名前」をまとめて削除
    For Each v In Application.Names
        
        MsgBox v.Name
        v.Delete
    
    Next v
　
End Sub