1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
| Function NameManagerList() As Variant
' Philippe Tulliez (https://magicoffice.be)
Dim n As Name, r As Long, Pos As Byte
Dim tbl()
Dim rng As Range
ReDim tbl(1 To ActiveWorkbook.Names.Count + 1, 1 To 5)
For Each n In ActiveWorkbook.Names
r = r + 1: Pos = InStr(n.Name, "!")
On Error Resume Next
Set rng = n.RefersToRange
On Error GoTo 0
tbl(r, 1) = Mid(n.Name, Pos + 1)
tbl(r, 2) = n.Value
tbl(r, 3) = IIf(rng Is Nothing, "'" & n.RefersToLocal, n.RefersToLocal)
tbl(r, 5) = n.Comment
If Pos = 0 Then
tbl(r, 4) = "Classeur"
Else
tbl(r, 4) = Replace(Left(n.Name, Pos - 1), "'", "")
End If
Next
NameManagerList = tbl
End Function |
Partager