vba - 创建新的命名范围时触发宏？

Question

基本上我试图在某处列出工作簿中的所有命名范围。但我只想在创建新名称或删除现有命名范围时发生这种情况。任何方式我都可以捕捉到这个事件。或者仅在发生这种情况时才以某种方式触发宏？

Answer 1

这可能是最接近捕获“更改命名范围事件”的方法。每次在工作表中进行选择更改时，它都会检测到命名范围的更改，但如果您只是在“公式”下的名称管理器中进行更改，它将不会检测到更改。

1)
将此插入 本工作簿模块 :

Private Sub Workbook_Open()
    Call updateNamedRangesVar
End Sub

2) 将此插入 工作表模块您希望在其中检测命名范围更改。 (如有必要，插入所有工作表模块):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Call detectNamedRangeChange
End Sub

3) 最后，将其插入 常规模块 :

    Option Explicit

    Public pubNamedRanges As Variant

Sub updateNamedRangesVar()

    Dim i As Integer, namCount As Integer
    namCount = ThisWorkbook.Names.count

    If Not IsEmpty(pubNamedRanges) Then Erase pubNamedRanges

    If namCount > 0 Then
        ' take snapshot of named ranges
        ReDim pubNamedRanges(1 To namCount) As Variant
        For i = 1 To namCount
            pubNamedRanges(i) = ThisWorkbook.Names(i)
        Next i
    End If
End Sub

Sub detectNamedRangeChange()

    Dim changeBool As Boolean
    Dim i As Integer, namRngCount As Integer, namRngCountNew As Integer

    On Error Resume Next
    namRngCount = UBound(pubNamedRanges)
    On Error GoTo 0
    namRngCountNew = ThisWorkbook.Names.count

    If namRngCount <> namRngCountNew Then
        MsgBox "Change of number of named ranges from" & vbNewLine & _
            namRngCount & " to " & namRngCountNew
        changeBool = True
    Else
        If namRngCountNew > 0 Then
            With ThisWorkbook
            For i = 1 To namRngCountNew
                If CStr(.Names(i)) <> CStr(pubNamedRanges(i)) Then
                    MsgBox "Named range changed from " & vbNewLine & _
                        pubNamedRanges(i) & vbNewLine & " to " & vbNewLine & _
                        .Names(i)
                    changeBool = True
                    Exit For
                End If
            Next i
            End With
        Else
            MsgBox "Number of named ranges is now zero"
            changeBool = True
        End If
    End If

    If changeBool Then Call updateNamedRangesVar

End Sub

注意必须在 subs 范围之外声明的公共(public)变量。