Excel VBAで定義された範囲の名前をリンク付きで一覧表示するマクロの紹介

このマクロが実現すること
マクロの特徴
マクロのコード
実行後の確認点
使用時の注意点

このマクロが実現すること

Excelのワークシートには、特定のセル範囲に名前を付ける「定義された範囲の名前」という機能があります。この記事で紹介するマクロを使えば、登録されているすべての「定義された範囲の名前」をアクティブセルからハイパーリンク付きで一覧表示することができます。

なお、自動ハイパーリンクが不要であるならば「数式」タブの「定義された名前・数式で使用」から「名前の貼り付け・リスト貼り付け」で事足ります。このマクロは定義された名前のリストをハイパーリンク付きで作成したい場合に導入してください。

マクロの特徴

定義された名前の一覧を取得し、アクティブセルから一覧表示します。
「ブック」や「シート」などの範囲も表示します。
参照範囲はハイパーリンクとして提供され、クリックするだけで該当の範囲にジャンプできます。

マクロのコード

以下のコードをコピーし、お使いのExcel VBAエディタに貼り付けてください。

Sub ListDefinedNames()

    Dim nameObj As Name
    Dim currentRow As Long
    Dim currentCol As Long
    Dim scope As String
    Dim targetSheet As Worksheet
    Dim displayedRef As String
    Dim subAddress As String
    
    ' アクティブシートとアクティブセルの位置を取得
    Set targetSheet = ActiveSheet
    currentRow = ActiveCell.Row
    currentCol = ActiveCell.Column
    
    For Each nameObj In ThisWorkbook.Names
        ' 名前をセット (シート名を取り除く)
        If InStr(nameObj.Name, "!") > 0 Then
            targetSheet.Cells(currentRow, currentCol).Value = Split(nameObj.Name, "!")(1)
        Else
            targetSheet.Cells(currentRow, currentCol).Value = nameObj.Name
        End If
        
        ' スコープ（範囲）をセット
        If nameObj.Parent Is ThisWorkbook Then
            scope = "ブック"
        Else
            scope = nameObj.Parent.Name
        End If
        targetSheet.Cells(currentRow, currentCol + 1).Value = scope
        
        ' 参照範囲をセット
        displayedRef = Mid(nameObj.RefersTo, 2) ' 先頭の"="を除去
        subAddress = Replace(nameObj.RefersTo, "=", "")
        
        targetSheet.Cells(currentRow, currentCol + 2).Value = displayedRef
        targetSheet.Hyperlinks.Add Anchor:=targetSheet.Cells(currentRow, currentCol + 2), Address:="", _
            SubAddress:=subAddress, TextToDisplay:=displayedRef
        
        currentRow = currentRow + 1
    Next nameObj

End Sub