Better Approach to Checking For Existence of Worksheet Level Range

[MartinS]

Hi
Odd one. I have a function in a template workbook that checks for the existence of a sheet level range name, and in one instance, it is returning True when the name does not exist in the list of names.

If NamedRangeExistsInWorksheet(shtDA, strTablePrefix & cSfxStartPoint) Then
...

Public Function NamedRangeExistsInWorksheet(sht As Worksheet, strName As String) As Boolean
    If VarType(sht.Evaluate(strName)) <> vbError Then NamedRangeExistsInWorksheet = True
End Function

In this instance, shtDA is a worksheet named "DA-LVDate Basis", and these are all the names that show in the list of workbook names:

The first line in my code example returns True, causing the code inside the If to fail - the first line is returning the value of the RefersToRange, which errors.
Is there any way I can figure out why the error is happening, or a neater approach to the test?
Many thanks
Martin

 

[Micron]

After playing with your code my guess is that you have named range(s) whose scope is not at the sheet level but is at the workbook level. In that case, my testing shows the vartype being returned is 8204 (a range) even if that named range does not "belong" to the sheet I tested. Referencing a sheet level named range that doesn't exist on the sheet raises vartype 10, which is an error. So it seems that if the scope of a named range is at the workbook level, effectively it is going to return True when you test it for any sheet in that workbook.

If you are trying to do something only if the named range scope is to the sheet being passed to the function, then I guess you'd have to test its scope as well. I don't know how to do that but might be able to figure it out if I have time.

 

[MartinS]

After playing with your code my guess is that you have named range(s) whose scope is not at the sheet level but is at the workbook level. In that case, my testing shows the vartype being returned is 8204 (a range) even if that named range does not "belong" to the sheet I tested. Referencing a sheet level named range that doesn't exist on the sheet raises vartype 10, which is an error. So it seems that if the scope of a named range is at the workbook level, effectively it is going to return True when you test it for any sheet in that workbook.

If you are trying to do something only if the named range scope is to the sheet being passed to the function, then I guess you'd have to test its scope as well. I don't know how to do that but might be able to figure it out if I have time.

Thanks - yes, that was the issue - the name existed at the workbook level as well as the sheet level, and the function accepted it as being valid. I was concentrating on the specific worksheet and not the workbook, so didn't spot it!
Be interested to know if it's possible to check the scope properly.
Thanks
Martin

 

[Micron]

Not eloquent I guess, but this seems to work in principle. I'm not a fan of assuming an error is a reliable result (the Set fails if the name is wb level). I also am wondering if it's more efficient to pass the name as a Name and not a string. So this could be edited to simply not test TypeOf since if it Set error is not raised, by default the name is sheet level.

Public Function NamedRangeExistsInWorksheet(sht As Worksheet, strName As String) As Boolean
Dim nme As Name
On Error Resume Next

Set nme = sht.Names(strName)
    If nme Is Nothing Then Exit Function
    If TypeOf nme.Parent Is Worksheet Then NamedRangeExistsInWorksheet = True
Set nme = nothing
End Function

 

[Micron]

Passing a Name object seems more difficult so I scrapped that idea in this condensed version.

Public Function NamedRangeExistsInWorksheet(sht As Worksheet, strName As String) As Boolean
Dim nme As Name
On Error Resume Next

Set nme = sht.Names(strName)
If Not nme Is Nothing Then NamedRangeExistsInWorksheet = True
Set nme = Nothing

End Function