VBA distinguish between worksheet and workbook names

[baitmaster]

I have a workbook, and I want to refer to a number of named ranges within it using VBA. My Users occasionally clone some of these names by accident, so duplicates appear on individual worksheets. My code is then crashing because it can't correctly refer to the names

Example, I have a name mdlStart that is workbook level and has been cloned on the "Export" worksheet, and this version refers incorrectly to another workbook
- When looping through all names I now have the names mdlStart and Export!mdlStart
- When asking the question ?names("mdlStart").name I get the [unexpected] answer Export!mdlStart

When my code refers to names("mdlStart").refersToRange I'm getting Run-time error 1004, because the Export name is looking at another workbook, but I'm not interested in the Export version, I want the workbook version and I'm unclear why VBA is attempting to use that one, shouldn't it default to the workbook level? I'm not even on that worksheet

How do I get VBA to default to the workbook level names and ignore worksheet level names unless specifically referenced? Surely it should already do that so what's gone wrong?

Thanks

 

[RoryA]

If the local name is local to the active sheet, then attempting to access the global name will actually access the local version. You could temporarily insert a sheet at the start of the workbook, then process the names before deleting the temp sheet.

 

[mikerickson]

Perhaps the .Parent property would help

Dim someName as Name

Set someName = Names("test")

If someName.Parent.Name = ThisWorkbook.Name Then
    MsgBox someName.Name & " is workbook level"
Else
    MsgBox someName.Name & " is scoped to the sheet " & oneName.Parent.Name
End If

 

[baitmaster]

Rory - I'm nowhere near that worksheet so I don't get it. I'm actually referring to the workbook as part of the code that's crashing too: Set getRetailRange = wbRetailModel.Names(strName).RefersToRange

Mike - good idea, I'll play with that and see how I get on. Unsure how it will help though given that I already refer to the workbook...

 

[baitmaster]

I've been unable to resolve this, so I've added the following test for duplicated names, not perfect but it meets my needs. Thanks for the ideas

Function testForDuplicatedNames(wb As Workbook) As Boolean

' create other objects & variables
Dim n As Name, arrSplit As Variant, strName As String
Dim dictNames As Scripting.Dictionary: Set dictNames = New Scripting.Dictionary
Dim blDuplication As Boolean


' check each actual name, having removed any worksheet name
For Each n In wb.Names
    arrSplit = Split(n.Name, "!")                   ' worksheet-level names are appended "sheetname!..."
    strName = arrSplit(UBound(arrSplit))            ' look at only elements after !
    dictNames(strName) = dictNames(strName) + 1     ' add element count to dictionary
Next n


' test for duplicated items
Dim i As Integer
For i = 1 To dictNames.Count
    If dictNames.Items(i - 1) > 1 Then
        blDuplication = True
        Debug.Print dictNames.Keys(i - 1), dictNames.Items(i - 1)
    End If
Next i


' report results
If blDuplication Then
    MsgBox "ERROR: duplicated names found in file [" & wb.Name & "], see VB Immediate window for details", vbCritical
    testForDuplicatedNames = True
End If


End Function

 

[mikerickson]

I'm a bit curious about how your users are duplicating a workbook level name at the worksheet level. Usually creating a worksheet level name (outside the VBEditor) requires some special handling and those who know how to do that would, I hope, be cautious enough to avoid duplicating an existing name.

 

[baitmaster]

Hi Mike, on this occasion it happened when an entire worksheet was replaced with a new version. I'm not entirely sure how but they probably cloned the worksheet, and the cloned name then pointed at an old version of the file and persisted when brought back in to the main model... It was one of the senior finance guys so I have to work around them rather than just laying down the law on how they do things