For Each loop on selected Arrays in VBA

Question

This for each loop will check out all the named range in my active sheet and do something.

Sub Test1
For Each namedRanges In ActiveWorkbook.Names
    If namedRanges.RefersToRange.Parent.Name = ActiveSheet.Name Then MsgBox namedRanges.Name
Next namedRanges
End Sub

however, I just want to call a certain Names for them to do something and they are static. How do I do that?

I tried declaring the Named ranges I want but I don't think I'm doing it right.

Sub Test3()
Dim nameArr(1 To 3) As Integer
Dim vari As Variant
nameArr("Page1") = 1: nameArr("Page2") = 2: nameArr("Page3") = 3
Dim idx As Long
    For idx = LBound(nameArr) To UBound(nameArr)
        vari = nameArr(idx)
        MsgBox vari
    Next idx
End Sub

I made a variable nameArr as my static array here with the purpose for them to be the only one to do something within the for each loop.

Answer 1

Loop Over a List of Names

Sub LoopNames()
    
    Const PROC_TITLE As String = "Loop Over a List of Names"
    
    Dim SelectedNames As Variant:
    SelectedNames = Array("Page1", "Page2", "Page3")
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim nm As Name, rg As Range, SelectedName As Variant, IsNameRange As Boolean
    
    For Each SelectedName In SelectedNames
        
        ' Attempt to reference the name.
        On Error Resume Next
            Set nm = wb.Names(SelectedName)
        On Error GoTo 0
        If nm Is Nothing Then
            MsgBox "The name """ & SelectedName & """ doesn't exist!", _
                vbCritical, PROC_TITLE
        Else
            ' Attempt to reference the range.
            On Error Resume Next
                Set rg = nm.RefersToRange
            On Error GoTo 0
            If rg Is Nothing Then
                MsgBox "The existing name """ & nm.Name _
                    & """ doesn't refer to a range!", vbCritical, PROC_TITLE
            Else
                IsNameRange = True ' the name refers to a range
            End If
        End If
        
        If IsNameRange Then
           ' Do something with the range or the name or 'their' worksheet, e.g.:
            MsgBox "Sheet: " & vbTab & rg.Worksheet.Name & vbLf _
                & "Name: " & vbTab & nm.Name & vbLf _
                & "Range: " & vbTab & rg.Address(0, 0), _
                vbInformation, PROC_TITLE
           
           ' Your code for each name (range, worksheet)...
        
        End If
        
        ' Reset for the next iteration.
        IsNameRange = False
        Set rg = Nothing
        Set nm = Nothing
    
    Next SelectedName
    
    MsgBox "List of names processed: " & vbLf & vbLf _
        & Join(SelectedNames, vbLf), vbInformation, PROC_TITLE
    
End Sub

Answer 2

Please try.

Sub Test3()
    Dim nameArr, vari, refRange As Range
    Dim idx As Long, oName As Name
    ' Name range list
    nameArr = Array("Page1", "Page2", "Page3")
    For idx = LBound(nameArr) To UBound(nameArr)
        ' Get the named range, the return is a Name object or nothing
        On Error Resume Next
        Set oName = ThisWorkbook.Names(nameArr(idx))
        On Error GoTo 0
        If oName Is Nothing Then
            Debug.Print nameArr(idx) & " does not exist"
        Else
            ' Get the RefersToRange, the return is a Range object or nothing
            On Error Resume Next
            Set refRange = oName.RefersToRange
            On Error GoTo 0
            If Not refRange Is Nothing Then
                ' check name's scope
                If refRange.Parent.Name = ActiveSheet.Name Then
                    Debug.Print nameArr(idx) & " refer to " & refRange.Address(0, 0)
                End If
                Set refRange = Nothing
            End If
        End If
        Set oName = Nothing
    Next idx
End Sub

Microsoft reference document:

Array function

UBound function