Understanding Code - Check If Named Range Exists

[Simple_One]

Hi,

I don't really understand how the code below (which I found on the net) works and I was hoping someone could help me out with some questions:

[vba]Sub CheckRanges()
Dim vNames As Variant, v As Variant

vNames = Array("ABC", "DEF", "GHI", "JKL", "MNO")
For Each v In vNames
Debug.Print v, RangeExists(CStr(v))
Next
End Sub

Function RangeExists(s As String) As Boolean
On Error GoTo Nope
RangeExists = Range(s).count > 0
Nope:
End Function[/vba]
I understand that the sub is setup to pass an array of strings to the function RangeExists and the results are printed to the immediate window. What I don't get is quite how it all works, so my questions:

1. Why do you need to use CStr when passing the variable 'v' to the RangeExists function? (given that each array item in 'v' is a string already)

2. How does this line in particular work:
RangeExists = Range(s).count > 0
In what order does that execute?
I thought if you used an assignment (variable = something) you couldn't use a comparison operator with out it being inside an if statement; so in pseudo code it would like this:
RangeExists = IF(Range(s).count > 0, True, False)

3. Is it poor practice not to explicitly code RangeExists = False at the start of the function?

4. Does anyone know of a better way to efficently check whether an explicitly defined named range exists?
That is to say, I want to test whether:
'NamedRange' (passed as a string), exist within 'Worksheet Object' (passed as worksheet object) within the named 'Workbook Object' (passed as a workbook object)...

So again in dodgy pseudo code -

Does:
WorkbookObject.WorksheetObject.Range(NamedRange)
exist within:
WorkbookObject.WorksheetObject


EDIT:
Also one further question, am I being redundant in how I address that range?
For instance is:
WorksheetObject.Range(NamedRange)
Exactly the same as:
WorkbookObject.WorksheetObject.Range(NamedRange)
for some reason? (say because the worksheet object already holds information about which workbook it is in?)

Cheers

[Aflatoon]

1. v is declared as Variant (as it has to be to use it to iterate through an array) but the function takes a String passed ByRef so it will not accept a variant. Hence the need for CStr.

2. Range(s).Count is evaluated first, the result is tested to see if it's greater than 0 then that (Boolean) result is returned as the function result. The default False is returned in case of error.

3. That's a matter of opinion. (in my opinion, yes it's lazy )

4. If you pass a Worksheet object there is strictly no need to pass a Workbook. The Worksheet is already specific. However, unless the name is defined as local to the sheet what you actually want to test is whether any of the workbook names refer to ranges on the worksheet (at least that is my interpretation of your question).

[SamT]

1.) A Range Name is is held in a workbooks' and a worksheets' collection Names. Included in those collections are Sheet names, chart names, Print Area names, custom names, range names and Defined Names. Defined Names can refer to Ranges, Formulas, Constants, and other Names.

Since there is no way to know if v is in fact the Name of a Range, RangeExists checks to see if it is a Collection. Another method would be to check if it Is Nothing. All such checks use the Error return method, hence the On Error Resume Next.

You can index a Collection by its position in the collection, (a Long,) or in some collections, by the name, (a String.) therefore Range(s) is referencing by name.

2.) When a Boolean is Declared, (RangeExists() As Boolean) its initial state is False. Explicityl initializing it to False is redundant, but if it helps you remember, then it's good practice. For a beginner. I still prefer to explicitly set my functions as shown in the code example below.

3.) Comparison operators include implicit IFs, so A > B is reads as If A>B then True Else False.The comparison is made, then RangeExists is set to the comparison's return.

4.) Better? not really for the code example you're using, that is to check a list of possible names against actual names. The only reason I can think of to do that is because I intend to programatically add named ranges, and then I would have to check the Name's RefersTo formula to see if the Range Name I wanted to use was referring to the Range I wanted.

EDIT.) As long as the Range is in the workbook the code is in, you don't need the WorkBook Object.

[VBA]Sub CheckRanges()
Dim vNames As Variant
Dim v As Variant

vNames = Array("ABC", "DEF", "GHI", "JKL", "MNO")
For Each v In vNames
Debug.Print v, RangeExists(CStr(v)) ' v is a variant, s is a string.
'CStr() enforces correct conversion. Variables are RAM memory structures
'And once in a great while VBA will convert them incorrectly.
'Try not using CStr and see what happens.
Next
End Sub[/VBA]
[VBA]Function RangeExists(s As String) As Boolean
'A Range Name is a Defined Name,is an Object. A Range is a collection of
'Ranges?!?!?
'An existing Range holds at least one Range so its Count is greater than 1.

'The test below will return an error if the Range does not exist,
'sending code flow to Nope:. If the Range does exist, the function
'is set True and code flow exits the function.
On Error GoTo Nope
RangeExists = Range(s).Count > 0
Exit Function

'If the Code flow arrived here, the Range does not exist, and I have set
'the Function = False before the End.
Nope:
RangeExists = False
End Function[/VBA]

[mikerickson]

This named range tester allows you to specify the scope of the sought name.

[VBA]Sub test()
MsgBox NamedRangeExists("test")
MsgBox NamedRangeExists("test", Workbooks("Workbook1.xlsm"))
MsgBox NamedRangeExists("test", Workbooks("Workbook1.xlsm").Worksheets("Sheet1"))
End Sub

Function NamedRangeExists(strTestName As String, Optional nameScope As Object) As Boolean
If nameScope Is Nothing Then Set nameScope = ThisWorkbook
On Error Resume Next
NamedRangeExists = (nameScope.Names(strTestName).Name = strTestName)
On Error GoTo 0
End Function[/VBA]

[SamT]

1.) A Range Name is is held in a workbooks' and a worksheets' collection Names. Included in those collections are Sheet names, chart names, Print Area names, custom names, range names and Defined Names. Defined Names can refer to Ranges, Formulas, Constants, and other Names.

Since there is no way to know if v is in fact the Name of a Range, RangeExists checks to see if it is a Collection. Another method would be to check if it Is Nothing. All such checks use the Error return method, hence the On Error Resume Next.

You can index a Collection by its position in the collection, (a Long,) or in some collections, by the name, (a String.) therefore Range(s) is referencing by name.

2.) When a Boolean is Declared, (RangeExists() As Boolean) its initial state is False. Explicityl initializing it to False is redundant, but if it helps you remember, then it's good practice. For a beginner. I still prefer to explicitly set my functions as shown in the code example below.

3.) Comparison operators include implicit IFs, so A > B is reads as If A>B then True Else False.The comparison is made, then RangeExists is set to the comparison's return.

4.) Better? not really for the code example you're using, that is to check a list of possible names against actual names. The only reason I can think of to do that is because I intend to programatically add named ranges, and then I would have to check the Name's RefersTo formula to see if the Range Name I wanted to use was referring to the Range I wanted.

EDIT.) As long as the Range is in the workbook the code is in, you don't need the WorkBook Object.

[VBA]Sub CheckRanges()
Dim vNames As Variant
Dim v As Variant

vNames = Array("ABC", "DEF", "GHI", "JKL", "MNO")
For Each v In vNames
Debug.Print v, RangeExists(CStr(v)) ' v is a variant, s is a string.
'CStr() enforces correct conversion. Variables are RAM memory structures
'And once in a great while VBA will convert them incorrectly.
'Try not using CStr and see what happens.
Next
End Sub[/VBA]
[VBA]Function RangeExists(s As String) As Boolean
'A Range Name is a Defined Name,is an Object. A Range is a collection of
'Ranges?!?!?
'An existing Range holds at least one Range so its Count is greater than 1.

'The test below will return an error if the Range does not exist,
'sending code flow to Nope:. If the Range does exist, the function
'is set True and code flow exits the function.
On Error GoTo Nope
RangeExists = Range(s).Count > 0
Exit Function

'If the Code flow arrived here, the Range does not exist, and I have set
'the Function = False before the End.
Nope:
RangeExists = False
End Function[/VBA]

[p45cal]

The function does not robustly check whether a Named Range (aka defined name holding a range) exists or not. If I supply:
vNames = Array("C3")
C3 is not a named range but the function returns True.
It's a range address, but not a Named range (unless you view cell addresses as named ranges which is not unreasonable, but you won't find them in Name Manager)
Range(s).Count counts the number of cells in a range - I suppose you could call a range a collection of single-cell ranges.

[snb]

[VBA]
Sub M_snb()
On Error Resume Next

For Each it In Array("ABC", "DEF", "GHI", "JKL", "MNO")
ThisWorkbook.Names(it).Visible = False
Debug.Print it, Err.Number = 0
Err.Clear
Next
End Sub
[/VBA]

[mdmackillop]

Don't forget dynamic ranges (=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)).
The range may exist but also be of zero size. Mike's code will find it, but counting cells will not.

[Paul_Hossler]

I've always checked to see if a Named Range exist this way. Haven't had any problems .... yet

[vba]
Option Explicit
Sub CheckRanges()
Dim vNames As Variant, v As Variant

vNames = Array("ABC", "DEF", "GHI", "JKL", "MNO", "ASFD", "DYNAM")
For Each v In vNames
Debug.Print v, NamedRangeExists(CStr(v))
Next
End Sub

Function NamedRangeExists(s As String) As Boolean
Dim i As Long

i = 0
On Error Resume Next
i = ThisWorkbook.Names(s).Index
On Error GoTo 0

NamedRangeExists = (i <> 0)
End Function
[/vba]

Paul

[snb]

no need for a redundant variable:
[VBA]Sub M_snb()
On Error Resume Next

For Each it In Array("ABC", "DEF", "GHI", "JKL", "MNO")
ThisWorkbook.Names(it).Visible = ThisWorkbook.Names(it).Visible
Debug.Print it, Err.Number = 0
Err.Clear
Next
End Sub[/VBA]

[SamT]

All defined names are formulas.

I tested all the suggestions and none of them, except the collection test, can differentiate between a named range and a named formula. They all, except the Count test, find Dr. Mack's zero sized range. It too, is a formula.

Even a test that first finds a defined name, then checks its Count will miss zero sized ranges.

My question is: Why check a list of random names? The coder should already have a very good idea of what the Defined Names are.

[mikerickson]

To restrict to checking if a Name refers to a range

[vba]
On Error Resume Next
NamedRangeExists = Not (nameSource.Names(strTestName).RefersToRange Is Nothing)
On Error Goto 0[/vba]

But that returns false for Names that use relative sheet addressing like

Name: myName RefersTo: =!$A$1

[Aflatoon]

In my experience those are unreliable anyway and should be avoided.

[Simple_One]

Excellent information one and all, I appreciate both the direct responses and the discussion, very helpful for me when it comes to learning this stuff.

I discovered the issue with it treating cell references ("A1") as a valid named range (in the sense that a named range should be one that is found in the manager), I ended up writing another sub for future use, though in this particular instance, that 'issue' with it is actually fine for my purposes.

Cheers