Problem validating names

[Jon von der Heyden]

I'm trying to validate that a given string is acceptable for a name. I know I could write code to attempt to disallow specific characters but I thought this approach would be better:

[color=darkblue]Public[/color] [color=darkblue]Property[/color] [color=darkblue]Get[/color] ValidName([color=darkblue]ByVal[/color] strName [color=darkblue]As[/color] [color=darkblue]String[/color]) [color=darkblue]As[/color] [color=darkblue]Boolean[/color]
    [color=darkblue]Dim[/color] nme [color=darkblue]As[/color] Name
    
    ValidName = [color=darkblue]True[/color]
    
    [color=darkblue]If[/color] Len(strName) = 0 [color=darkblue]Then[/color]
        ValidName = [color=darkblue]False[/color]
        [color=darkblue]GoTo[/color] Finally
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
    [color=darkblue]With[/color] Me
        [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
            [color=darkblue]If[/color] .NameScope = NSworkbook [color=darkblue]Then[/color]
                [color=darkblue]Set[/color] nme = ActiveWorkbook.Names(strName)
                [color=darkblue]If[/color] [color=darkblue]Not[/color] nme [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
                    [color=darkblue]If[/color] nme.Parent [color=darkblue]Is[/color] ActiveWorkbook [color=darkblue]Then[/color]
                        ValidName = [color=darkblue]False[/color]
                    [color=darkblue]End[/color] [color=darkblue]If[/color]
                [color=darkblue]End[/color] [color=darkblue]If[/color]
            [color=darkblue]ElseIf[/color] .NameScope = NSworksheet [color=darkblue]Then[/color]
                [color=darkblue]Set[/color] nme = ActiveSheet.Names(strName)
                [color=darkblue]If[/color] [color=darkblue]Not[/color] nme [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
                    [color=darkblue]If[/color] nme.Parent [color=darkblue]Is[/color] ActiveSheet [color=darkblue]Then[/color]
                        ValidName = [color=darkblue]False[/color]
                    [color=darkblue]End[/color] [color=darkblue]If[/color]
                [color=darkblue]End[/color] [color=darkblue]If[/color]
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] 0
        
        [color=darkblue]If[/color] ValidName = [color=darkblue]False[/color] [color=darkblue]Then[/color] [color=darkblue]GoTo[/color] Finally
        
        [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
            [color=darkblue]If[/color] .NameScope = NSworkbook [color=darkblue]Then[/color]
                [color=darkblue]Set[/color] nme = ActiveWorkbook.Names.Add(Name:=strName, RefersTo:="Delete Me")
            [color=darkblue]ElseIf[/color] .NameScope = NSworksheet [color=darkblue]Then[/color]
                [color=darkblue]Set[/color] nme = ActiveSheet.Names.Add(Name:=strName, RefersTo:="Delete Me")
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] 0
        
        ValidName = [color=darkblue]Not[/color] nme [color=darkblue]Is[/color] [color=darkblue]Nothing[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    [color=darkblue]If[/color] [color=darkblue]Not[/color] nme [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
        nme.Delete
    [color=darkblue]End[/color] [color=darkblue]If[/color]
Finally:
    [color=darkblue]Set[/color] nme = [color=darkblue]Nothing[/color]
[color=darkblue]End[/color] [color=darkblue]Property[/color]

The problem I am facing has to do with the scope. Say the following names exist:

Book - scope is Workbook
Sheet - scope is Worksheet (Sheet1)

If a new name is selected, called 'Sheet', and the scope is set to Workbook, then it should be acceptable. But I notice that my existing name 'Sheet' becomes overwritten, despite the fact that it is explicitly scoped to workbook.

I checked by manually adding names. I can create the following 3 names:
Book - scope is Workbook
Sheet - scope is Worksheet (Sheet1)
Sheet - scope is Workbook

Recording the set-up of these three names generates the following:

[color=darkblue]Sub[/color] Macro2()
    ActiveWorkbook.Names.Add Name:="Book", RefersToR1C1:="=Sheet1!R8C15"
    ActiveWorkbook.Worksheets("Sheet1").Names.Add Name:="Sheet", RefersToR1C1:= _
        "=Sheet1!R8C15"
    ActiveWorkbook.Names.Add Name:="Sheet", RefersToR1C1:="=Sheet1!R8C15"
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

Yet if done progamatically only two names exist. The 2nd instance of creating 'Sheet' overwrites the first.

Can anyone shed light on how to manage the scope of names please?

 

[jasonb75]

Book - scope is Workbook
Sheet - scope is Worksheet (Sheet1)
Sheet - scope is Workbook

Recording the set-up of these three names generates the following:

Code:


Sub Macro2()
ActiveWorkbook.Names.Add Name:="Book", RefersToR1C1:="=Sheet1!R8C15" ActiveWorkbook.Worksheets("Sheet1").Names.Add Name:="Sheet", RefersToR1C1:= _ "=Sheet1!R8C15" ActiveWorkbook.Names.Add Name:="Sheet", RefersToR1C1:="=Sheet1!R8C15"
End Sub
</PRE>
Yet if done progamatically only two names exist. The 2nd instance of creating 'Sheet' overwrites the first.

Jon, thinking logically, I would say that what is happening is correct. Consider this scenanio.

Sheet - scope is Worksheet (Sheet1) Refers to Sheet1!A2:A20
Sheet - scope is Workbook Refers to Sheet2!B2:B30

Now use the range (Sheet) in a formula, if the formula is not in Sheet1 then it should use the second range, but if the formula is in Sheet1 then both ranges are valid to use which could cause all manner of problems as both the sheet and workbook scope would be valid.

Apologies if I'm on the wrong track but that's what I'm seeing from reading your post.

 

[Jon von der Heyden]

Hi Jason

Changing the names slightly to avoid confusion...

Say we have two names:

Test - workbook scope; refersto: Sheet2!$A$1
Test - Sheet1 scope; refers to: Sheet1!$A$1

I can call the workbook scoped name with "=Test".
I can call the sheet scoped name with "=Sheet1!Test"

So I think we should always be able to have two names with the same name, just as long as they don't have the same scope/parent. We merely need to qualify the sheet if calling a sheet scoped name. Not so?

 

[jasonb75]

Jon, it's not an issue I've had previously, whenever I use names I always keep the scope to workbook.

Extract from microsoft online help

http://office.microsoft.com/en-us/excel-help/define-and-use-names-in-formulas-HA010147120.aspx

A name must always be unique within its scope. Excel prevents you from defining a name that is not unique within its scope. However you can use the same name in different scopes. For example, you can define a name, such as GrossProfit that is scoped to Sheet1, Sheet2, and Sheet3 in the same workbook. Although each name is the same, each name is unique within its scope. You might do this to ensure that a formula that uses the name, GrossProfit, is always referencing the same cells at the local worksheet level.

You can even define the same name, GrossProfit, for the global workbook level, but again the scope is unique. In this case, however, there can be a name conflict. To resolve this conflict, by default Excel uses the name that is defined for the worksheet because the local worksheet level takes precedence over the global workbook level. If you want to override the precedence and you want to use the workbook name, you can disambiguate the name by prefixing the workbook name as the following example shows:

WorkbookFile!GrossProfit

You can override the local worksheet level for all worksheets in the workbook, with the exception of the first worksheet, which always uses the local name if there is a name conflict and cannot be overridden.

How do you interpret the last paragraph? Maybe that is the cause of the problem.

 

[Jon von der Heyden]

I'm much too thick to make sense of that last paragraph. I'm experimenting but I don't know what is meant by it.

 

[Jon von der Heyden]

If you already have a workbook scoped name then there's no problem adding a sheet scoped name with the same name.

The problem arises where you have a sheet scoped name and then try to create one with the same name scoped for workbook.

At the moment the only solution I can think of is:

1. Check if the sheet scope name exists, if it does then collect it's property values.
2. Delete the sheet scoped name.
3. Add the workbook scoped name.
4. Recreate the sheet scoped name.

I've researched my books and trawled the web and I can't find any suggestions...

 

[jasonb75]

Jon, a little experimenting seems to have found something, the problem seems to be, not with sheet1, but with Sheets(1) maybe inserting a dummy tab will cure the problem.

Only just found this so it could be a false solution that just worked with the couple of tests I've done, going to test a some more and see what happens.

 

[RoryA]

The last paragraph means that if you try to use the workbook level name on the first sheet, it won't work - you will get the sheet level one.
FWIW, it's regarded by many as a bad idea to have both workbook and worksheet level names with the same name.

 

[Jon von der Heyden]

FWIW, it's regarded by many as a bad idea to have both workbook and worksheet level names with the same name.

Yep! And I do avoid using the same, but at the same time I don't want to prohibit the creation of the same.