Excel VBA使用范围属性设置命名单元格公式

正如我在评论中所发布的那样，您处于永无止境的循环中，因为您在工作表更改事件中更改了工作表。您需要跟踪变更呼叫。可以通过创建变量来跟踪调用更改的时间来减轻这种情况，这也假设名称范围是1个单元格。

Public bRunning As Boolean 'keeps track of when we are making the change

Private Sub Worksheet_Activate()
    bRunning = False 'set to false when the sheet is activated
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If bRunning = False Then 'check to see if this is the first change
    bRunning = True 'set the variable letting us know we are making the change.
    With ActiveSheet

    .Unprotect Password:="somepw"
        If Range("d17").Value = "Yes" Then
        .Range("D22:D78").Locked = False
        .Range("D22:D78").Interior.Color = RGB(115, 246, 42)
        .Range("Inc_06PCTotRev").Formula = "=SUM($D$22:$D$25)"
        ElseIf WorksheetFunction.CountA(Range("d22:D78")) <> 0 Then
        If .Range("D22").Locked = True Then
            With Range("D22:D78")
            .Locked = False
            .ClearContents
            .Interior.Color = RGB(217, 217, 217)
            End With
        Else:  .Range("D22:D78").ClearContents
        End If
        Else: .Range("D22:D78").Interior.Color = RGB(217, 217, 217)
          .Range("D22:D78").Locked = True
        End If
    .Protect Password:="somepw"
    End With
    Else
    bRunning = False 'reset the variable as we are done making changes
    End If
End Sub