对于 VBA 中选定数组上的每个循环-解网

问：

对于每个循环，这将检出我的活动工作表中的所有命名范围并执行某些操作。

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

但是，我只想叫某个名字让他们做某事，他们是静态的。我该怎么做？

我尝试声明我想要的命名范围，但我认为我做得不对。

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

我在这里制作了一个变量 nameArr 作为我的静态数组，目的是让它们成为每个循环中唯一一个执行某些操作的数组。

每个命名范围 Excel VBA 循环

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 参考文档：

数组函数

UBound 函数

2赞 VBasic2008 10/10/2023 #2

遍历名称列表

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

上一个：VBA：如何.结束（xlup）。行在合并区域上的行为（已编辑）

下一个：MkDir 变量失败

对于 VBA 中选定数组上的每个循环

For Each loop on selected Arrays in VBA

评论

遍历名称列表