List Names in Workbook

The following procedure will print all the named ranges in the active workbook in the Debug Window. It will skip the two hidden ranges FilterDatabase and Print_Area.

Sub ListNames()

Dim wkbk As Excel.Workbook
Dim nm As Name
Dim countOfNames As Long
Dim i As Long
Dim currentName As String

  Set wkbk = ActiveWorkbook

  countOfNames = wkbk.Names.Count

  For i = 1 To countOfNames

    currentName = wkbk.Names(i).Name

    If InStr(currentName, "FilterDatabase") = 0 And _
       InStr(currentName, "Print_Area") = 0 Then
      Debug.Print currentName & " - " & wkbk.Names(i).RefersTo
    End If
  Next i

End Sub

To call this procedure, just call ListNames. To print the names in every open workbook, here is a slightly modified version of the above procedure.

Function ListNames(wb As Excel.Workbook)

Dim wkbk As Excel.Workbook
Dim nm As Name
Dim countOfNames As Long
Dim i As Long
Dim currentName As String

  Set wkbk = wb

  countOfNames = wkbk.Names.Count

  For i = 1 To countOfNames

    currentName = wkbk.Names(i).Name

    If InStr(currentName, "FilterDatabase") = 0 And _
       InStr(currentName, "Print_Area") = 0 Then
      Debug.Print currentName & " - " & wkbk.Names(i).RefersTo
    End If
  Next i

End Function

You would call the above function as follows:

Sub WorkbookLoop()

Dim wb As Excel.Workbook

For Each wb In Excel.Workbooks
  Call ListNames(wb)
Next wb

End Sub

Taking advantage of these hidden names we can do a few semi-useful things:

Check if a workbook ever had a filter applied to it

If a workbook was ever filtered using Data » Filter » Autofilter, the hidden range name FilterDatabase will exist.

Function wasEverFiltered() As Boolean

Dim wkbk As Excel.Workbook
Dim nm As Name
Dim countOfNames As Long
Dim i As Long

  Set wkbk = ActiveWorkbook

  countOfNames = wkbk.Names.Count

  For i = 1 To countOfNames
    If InStr(wkbk.Names(i).Name, "FilterDatabase") > 0 Then
      wasEverFiltered = True
      Exit Function
    End If
  Next i

End Function

And of course we can functionize this to check every open workbook. The reasons for doing so are left as an exercise for you.

Function wasEverFiltered(wb As Excel.Workbook) As Boolean

Dim wkbk As Excel.Workbook
Dim nm As Name
Dim countOfNames As Long
Dim i As Long

  Set wkbk = wb

  countOfNames = wkbk.Names.Count

  For i = 1 To countOfNames
    If InStr(wkbk.Names(i).Name, "FilterDatabase") > 0 Then
      wasEverFiltered = True
      Exit Function
    End If
  Next i

End Function

Check print area

If you want to check if a workbook has its print area set, use the following code:

Function IsPrintAreaSet() As Boolean

Dim wkbk As Excel.Workbook
Dim nm As Name
Dim countOfNames As Long
Dim i As Long

  Set wkbk = ActiveWorkbook

  countOfNames = wkbk.Names.Count

  For i = 1 To countOfNames
    If InStr(wkbk.Names(i).Name, "Print_Area") > 0 Then
      IsPrintAreaSet = True
      Exit Function
    End If
  Next i

End Function

To loop through the open workbooks and check for print area, use the function below. The WorkbookLoop procedure above can be modified to call IsPrintAreaSet instead of ListNames.

Function IsPrintAreaSet(wb As Excel.Workbook) As Boolean

Dim wkbk As Excel.Workbook
Dim nm As Name
Dim countOfNames As Long
Dim i As Long

  Set wkbk = wb

  countOfNames = wkbk.Names.Count

  For i = 1 To countOfNames
    If InStr(wkbk.Names(i).Name, "Print_Area") > 0 Then
      IsPrintAreaSet = True
      Exit Function
    End If
  Next i

End Function

Note that you can also use Worksheet.PageSetup.PrintArea to check if the Print Area is set for a particular sheet. It returns an empty string (Len = 0) if not set.

Get Sheet Name from Range Reference

The sheet name may be extracted from the RefersTo property using the following procedure.

Function ExtractSheetName(RefersToReference As String) As String
' extracts sheet name from a range name reference
Dim exclamationPointPosition As Long
Dim equalsSignPosition As Long

  exclamationPointPosition = InStr(RefersToReference, "!")
  equalsSignPosition = InStr(RefersToReference, "=")

  ExtractSheetName = Replace(Mid$(RefersToReference, equalsSignPosition + 1, exclamationPointPosition - 2), "'", "")

End Function

To print the sheet names, loop through the Names collection and pass the RefersTo property to the ExtractSheetName function as follows:

Sub PrintSheetNamesAndRanges()
' can be used with:
' RefersTo
' RefersToLocal
' RefersToR1C1
' RefersToR1C1Local

Dim wkbk As Excel.Workbook
Dim countOfNames As Long
Dim i As Long
Dim sheetName As String
Dim refersToName As String

  Set wkbk = ActiveWorkbook

  countOfNames = wkbk.Names.Count

  For i = 1 To countOfNames
    refersToName = wkbk.Names(i).RefersTo
    sheetName = ExtractSheetName(refersToName)
    Debug.Print sheetName & " - " & refersToName
  Next i

End Sub

The above procedure will only loop through the active workbook. You can adapt this to print sheet names and ranges from all open workbooks by looping through the Workbooks collection and calling a slightly altered version of the above procedure.

Sub PrintSheetNamesAndRanges(wb As Excel.Workbook)
' can be used with:
' RefersTo
' RefersToLocal
' RefersToR1C1
' RefersToR1C1Local

Dim wkbk As Excel.Workbook
Dim countOfNames As Long
Dim i As Long
Dim sheetName As String
Dim refersToName As String

  Set wkbk = wb

  countOfNames = wkbk.Names.Count

  For i = 1 To countOfNames
    refersToName = wkbk.Names(i).RefersTo
    sheetName = ExtractSheetName(refersToName)
    Debug.Print sheetName & " - " & refersToName
  Next i

End Sub

Call the above function as follows:

Dim wb As Excel.Workbook

For Each wb In Excel.Workbooks
    PrintSheetNamesAndRanges(wb)
Next wb