【Excel VBA】ブックの名前一覧の辞書化＋Exists【関数サンプル】

VBA

'-------------------------------
' 関数呼び出し例
'-------------------------------
Private Sub sample()

   Dim nameDic As Object: Set nameDic = CreateObject("Scripting.Dictionary")
    Set nameDic = NameDefinition(ActiveWorkbook)

    '使用例
    Dim ret: ret = IIf(nameDic.Exists("何かの名前"), Range("何かの名前").Value, "")

End Sub

'-------------------------------
' 名前一覧の作成
'-------------------------------
Public Function NameDefinition(wb As Workbook) As Dictionary

    Dim dic As Object: Set dic = CreateObject("Scripting.Dictionary")
    Dim Name: For Each Name In wb.Names
        If dic.Exists(Mid(Name.Name, InStr(Name.Name, "!") + 1)) Then GoTo n
        dic.Add Mid(Name.Name, InStr(Name.Name, "!") + 1), Name
n: Next
    Set NameDefinition = dic
    
End Function