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

2023.08.25

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

実行後の確認点

マクロを実行すると、以下の情報がアクティブセルから順に表示されます：

  1. 定義された範囲名
  2. その範囲名が属している範囲（ブックまたはシート名）
  3. 定義された名前の参照範囲（この部分はハイパーリンクとして提供されます）

使用時の注意点

  • このマクロはアクティブなセルを起点に情報を表示しますので、情報を表示したい位置を事前に選択してください。
  • 定義された名前が非常に多い場合、一覧が長くなる可能性がありますので、十分な空きスペースを確保してからマクロを実行してください。
  • ハイパーリンクは、シート内の参照範囲へのジャンプをサポートしています。外部のファイルやウェブサイトへのリンクはサポートしていません。

コメント