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.
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.
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:
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.
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.
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:
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.
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.
' 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:
' 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.
' 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:
For Each wb In Excel.Workbooks
PrintSheetNamesAndRanges(wb)
Next wb
