Programmatically determine if a named range is scoped to a workbook

I didn't manage to make the WorkbookParameter work but I found a workaround:

For i = 1 To ThisWorkbook.Names.Count
    If UBound(Split(ThisWorkbook.Names(i).Name, "!")) > 0 Then Debug.Print ThisWorkbook.Names(i).Name
Next i

The name of the local Named Range (i.e. scope to a sheet only) is formatted this way: Sheet1!NamedRange whereas the name of the global Named Range is formatted: NamedRange.

Thus, you can split on the ! and check the length of the array.

You can use the Parent property:

Sub Global_Local_names()
    Dim oNm As Name
    For Each oNm In Names
        If TypeOf oNm.Parent Is Worksheet Then
            Debug.Print oNm.Name & " is local to " & oNm.Parent.Name
        Else
            Debug.Print oNm.Name & " is global to " & oNm.Parent.Name
        End If
    Next
End Sub