Thread: Accessing Named Ranges on different worksheets

Background:
I have named ranges on multiple sheets. I wish to access the content of these ranges but also want to do so in a way that makes the code run as fast as possible.

Problem:
I know that you must tell VBA on which sheet the range is located before using the Range() command to access it. However, I remember reading that it is generally not a good idea to use the following approach:

Worksheets("Someworksheet").Select
a = Range("Somerangename").value

Because this can make the code run more slowly as the application flips back and forth between all the various worksheets (even with Application.ScreenUpdates = False)

So instead I am trying approach:

Dim IERNamedItem As VariableName
Dim IERNameList As Range
Dim i as Integer

for i = 1 to 3
IERNamedItem.Name = Worksheets("Tiers").IERNameList(i + 1).Value
Next i

In this case, IERNamedItem is a custom defined class with a property called "Name".

When I try this I get a run-time error 438: The object doesn't support this property or method. But when I try the approach of first selecting the Worksheet and then accessing the range, it works OK. What am I doing wrong?

gladst_j,

Since it seems like what you want to do is create variables that reference named range values, you can use the below code. It creates a two dimensional array. The first dimension is the index of the named range. The second dimension contains the values for each cell in the named range.

As an example:
Named range "test1" is Sheet1!A1
Named range "test2" is Sheet2!B2:B4

The resulting array would contain the values for all of the cells in those named ranges.
To get test1, it would be arrNameVal(1,1) since its the 1st named range and has only 1 cell.
To get test2 cell B3, it would be arrNameVal(2,2) since its the 2nd named range and B3 is the 2nd cell.

Sub LoadNamedRangeValuesIntoArrayMacro_for_gladst_j()
    
    Dim arrNameVal() As Variant: ReDim arrNameVal(1 To ActiveWorkbook.Names.Count, 1 To 1)
    Dim arrIndex As Long, ctr As Long
    Dim NamedRng As Name
    Dim RngCell As Range
    
    For Each NamedRng In ActiveWorkbook.Names
        ctr = 0
        For Each RngCell In Sheets(Mid(NamedRng.RefersTo, 2, InStr(NamedRng.RefersTo, "!") - 2)).Range(Mid(NamedRng.RefersTo, InStr(NamedRng.RefersTo, "!") + 1, Len(NamedRng.RefersTo)))
            ctr = ctr + 1
            If ctr > arrIndex Then arrIndex = arrIndex + 1
            ReDim Preserve arrNameVal(1 To ActiveWorkbook.Names.Count, 1 To arrIndex)
            arrNameVal(NamedRng.Index, ctr) = RngCell.Value
        Next RngCell
    Next NamedRng
    
    Dim n As Long, v As Long
    For n = 1 To UBound(arrNameVal, 1)
        For v = 1 To UBound(arrNameVal, 2)
            MsgBox arrNameVal(n, v)
        Next v
    Next n
    
End Sub

Hope that helps,
~tigeravatar

I've cut out a too much of the code in an attempt to make it easy to understand!

The values of the Range IERNameList are read in from a worksheet; each entry in the range is a string.

Sorry but I don't follow when you say "If it were an array full of range objects, then you wouldn't need to specify the sheet here, since you do that when you populate the array". Could you elaborate please

Thanks