VBA删除重复查询及连接时遭遇‘Assignment to constant not permitted’错误的排查与解决

我之前写过一个宏用来关闭特定连接的「全部刷新」功能，运行完全正常，但把类似的逻辑套用在删除查询和连接操作上时，却触发了Assignment to constant not permitted错误，下面给大家拆解问题和解决方案：

出现错误的代码 #

这是我一开始写的有问题的代码，试图通过赋值的方式执行删除操作：

Sub DeleteConnections()
    Dim cn As WorkbookConnection
    Dim qr As WorkbookQuery
    For Each cn In ActiveWorkbook.Connections
        cn.Delete = CBool(cn.Name Like "BPTable(*" Or cn.Name Like "BPResourceChart(*" Or cn.Name Like "BPFlowSteps(*" _
        Or cn.Name Like "BPDayChart(*" Or cn.Name Like "BPResource(*" Or cn.Name Like "BPTable2(*" _
        Or cn.Name Like "BPHistoric(*" Or cn.Name Like "BPHistoryHours(*")
    Next
    For Each qr In ActiveWorkbook.Queries
        qr.Delete = CBool(qr.Name Like "BPTable(*" Or qr.Name Like "BPResourceChart(*" Or qr.Name Like "BPFlowSteps(*" _
        Or qr.Name Like "BPDayChart(*" Or qr.Name Like "BPResource(*" Or qr.Name Like "BPTable2(*" _
        Or qr.Name Like "BPHistoric(*" Or qr.Name Like "BPHistoryHours(*")
    Next
End Sub

参考的正常运行宏 #

之前用来关闭特定连接「全部刷新」功能的宏是这样的，当时逻辑没问题是因为操作的是属性：

Sub RefreshAllBlock()
    Dim conn As Object
    For Each conn In ActiveWorkbook.Connections
        conn.RefreshWithRefreshAll = Not CBool(conn.Name Like "*BP*" Or conn.Name Like "*ProductionSupportTickets*")
    Next
End Sub

错误原因分析 #

问题出在对VBA对象成员的理解上：

• RefreshWithRefreshAll是WorkbookConnection的属性，可以通过赋值来修改它的状态
• 而Delete是WorkbookConnection和WorkbookQuery的方法，它是用来执行删除操作的动作，不能像属性那样给它赋值布尔值，这就是触发「Assignment to constant not permitted」错误的核心原因

修正后的完整代码 #

感谢BigBen提供的修正思路，调整为用If判断符合条件后直接调用Delete方法：

Sub DeleteConnections()
    Dim cn As WorkbookConnection
    Dim qr As WorkbookQuery
    For Each cn In ActiveWorkbook.Connections
        If cn.Name Like "*BPTable (*" Or cn.Name Like "*BPResourceChart (*" Or cn.Name Like "*BPFlowSteps (*" _
        Or cn.Name Like "*BPDayChart (*" Or cn.Name Like "*BPResource (*" Or cn.Name Like "*BPTable2 (*" _
        Or cn.Name Like "*BPHistoric (*" Or cn.Name Like "*BPHistoryHours (*" Then
            cn.Delete
        End If
    Next
    For Each qr In ActiveWorkbook.Queries
        If qr.Name Like "BPTable (*" Or qr.Name Like "BPResourceChart (*" Or qr.Name Like "BPFlowSteps (*" _
        Or qr.Name Like "BPDayChart (*" Or qr.Name Like "BPResource (*" Or qr.Name Like "BPTable2 (*" _
        Or qr.Name Like "BPHistoric (*" Or qr.Name Like "BPHistoryHours (*" Then
            qr.Delete
        End If
    Next
End Sub

内容的提问来源于stack exchange，提问作者JarvisButler290