How to efficiently code to access global named ranges in VBA

Question

I am looking for alternative, general coding methods for dealing with global named ranges, in VBA. I'm hoping for answers, here, with some new generalized suggestions and approaches.

I suggest a few methods I've used, but the methods don't avoid all problems -- I would like: ease of coding and spreadsheet drafting; tolerant of spreadsheet changes, and; ease of lookup/reference months later.

As I draft a spreadsheet that will later use VBA, I create named ranges (typically global names) in spreadsheet formulas. The ranges are useful there on the sheets, and useful as a reference from VBA. Typically, I do not add/change the names collection in VBA; I merely reference the collection.

When coding VBA, I access named ranges created in the workbook. If I cut/paste named cells/ranges, editing the workbook and sheets, the VBA still works.

Yet, Global Names, created in a worksheet environment, don't meet all three requirements in the VBA environment -- especially when modifying code or modifying the worksheets.

In My Perfect World:

wb.range("myGlobalRangeName")

My Perfect World hopes that a workbook's global references created on a spreadsheet are global -- but without clarification VBA expects that reference to be on the ActiveWorkBook and ActiveWorkSheet.

Thought One: I know that Range("myGlobalRangeName") accesses that range, and

Thus, I often use this fragment

wb.Worksheets("SheetOne").Range("myGlobalRangeName")

maybe constructed inside With Blocks, specifying the worksheet even though the range is a global reference.

Moving named cells to other sheets breaks this reference (even though the Name is Global!). I have to backtrack through all the code looking for misplaced references; or I can execute the code and hope to catch the errors...

Thought Two: I can write, instead, something like this, to access the name collection for the workbook:

wb.Names("myGlobalRangeName").RefersToRange

but having to append RefersToRange is .... well ... annoying. It misses the simplicity of the perfect world.

Thought Three: I create a distinct worksheet with all the values I want to trap in the other sheets, and I create ranges on that distinct sheet, only. Cell references in the workbook and accessed with VBA both work. That way, the VBA begins with

Dim wsNames as spreadsheet
set wsNames = wb.worksheets("SheetWithNames")

and the name references always used wb.wsNames, and looked like this:

with wb
    .... .wsNames.range("myGlobalRangeName") ....
end with

or other useful variations.

Yet this, too, can get messy -- I have to backtrack to see where the real data is when I later amend the spreadsheet or the VBA. Sometimes, that method works, particularly if I tenaciously name ranges on that sheet only for VBA consumption, use really memorable names, and remember the locations, and remember I did all this months later...

Conclusion: Am I missing something? Are there other, maybe easier, general coding methods other than using

• RefersToRange with the name collection, or
• wb.worksheets("SheetName").Range("myGlobalRange") references which explicitly identify the [current] sheet, or
• placing RangeNames and referral formulas on a separate sheet, with VBA range references as wsNames.Range("myGlobalRange").

I don't want to type so much; or create procedure variables to trap values before using them in assignments. The mess gets...well...harder to read and worse to track if I am assigning one cell's value to another in another workbook, and one or both use global ranges.

Answer 1

I've generally gotten what I needed with the Workbook Names collection, which can store formulas and ranges. Both can be either relative to the worksheet that is active, or absolute.

The big difference is between:

Call Thisworkbook.Names.Add(Name:="Bob", RefersTo:=Range("Sheet2!$F$19"))

and

Call Thisworkbook.Names.Add(Name:="Doug", RefersTo:="Sheet2!$F$19")

If that Cell F19 on Sheet2 is moved, then Bob will refer to the new location, anywhere in the workbook; move it to new sheets, Bob will follow along. But Doug will refer to Sheet2, F19, forever, no matter what.

The reference then is just [Bob] or Range("Bob") and will refer to another worksheet than the activesheet if need be.

StackExchange noted a related question that was very interesting:

Excel VBA: Workbook-scoped, worksheet dependent named formula/named range (result changes depending on the active worksheet)

...this is where a workbook-level name can hold a formula, and the formula can either have one of its values be the same through the workbook, or relate to a local value, per sheet. And indeed, you can mix the two in one formula.

Answer 2

You could store your name ranges into an Enum, then wrap a class around accessing them. It would be a bit of work setting up, however, you could automate the creation of this class which would be specific to an existing workbook with some VBA IDE programming if you want.

I didn't do that part, but I'm sharing the approach that might help.

Add this to a Class, name this NameRangeHelper

'Update these to correspond to the named ranges in your workbook, or
'those range you want access to with this approach
Public Enum NamedRanges
    Example1
    Example2
    Example3
End Enum

Private pNamedRanges As Object

Private Sub Class_Initialize()
    Dim NamedRange  As Name
    Dim NamedRanges As Names

    Set NamedRanges = ThisWorkbook.Names
    Set pNamedRanges = CreateObject("Scripting.Dictionary")

    For Each NamedRange In NamedRanges
        If Not pNamedRanges.Exists(NamedRange.Name) Then
            If TypeName(NamedRange.RefersToRange) = "Range" Then pNamedRanges.Add NamedRange.Name, NamedRange.RefersToRange
        End If
    Next

End Sub

Private Sub Class_Terminate()
    Set pNamedRanges = Nothing
End Sub

Public Function GetRange(RangeName As NamedRanges) As Excel.Range
    Dim RangeStringName As String
    RangeStringName = GetEnumName(RangeName)
    If pNamedRanges.Exists(RangeStringName) Then Set GetRange = pNamedRanges(RangeStringName)
End Function

Private Function GetEnumName(RangeName As NamedRanges) As String
    Select Case RangeName
        Case NamedRanges.Example1
            GetEnumName = "Example1"
        Case NamedRanges.Example2
            GetEnumName = "Example2"
        Case NamedRanges.Example3
            GetEnumName = "Example3"
        Case Else
            GetEnumName = vbNullString
    End Select
End Function

Public Function GetSheetFromRange(RangeName As NamedRanges) As Excel.Worksheet
    Set GetSheetFromRange = GetRange(RangeName).Parent
End Function

Public Function GetWorkbookFromRange(RangeName As NamedRanges) As Excel.Workbook
    Set GetWorkbookFromRange = GetRange(RangeName).Parent.Parent
End Function

Here is the client code, with an example of accessing a Range with a defined Enum.

 Sub ExampleNamedRangeHelper()
    Dim rngHelper As NamedRangeHelper: Set rngHelper = New NamedRangeHelper
    Dim rng       As Range
    Set rng = rngHelper.GetRange(Example1)
    Debug.Print rng.Address, rngHelper.GetSheetFromRange(Example1).Name, rngHelper.GetWorkbookFromRange(Example1).Name
End Sub

Using this approach, your names are in an Enum and you get the Intellisense to make picking/remembering the range names easier.