为何VBA字典使用动态字符串作为键时突然抛出运行时错误457？

错误原因 #

1. 全局变量未自动清空：你定义的infantry_dic等是Public全局字典，Excel不会在每次打开工作簿时自动重置这些变量。首次打开备份文件时字典为空，Add操作正常；但后续运行（比如关闭再打开、重新执行代码）时，字典里仍保留之前添加的键，再次执行Add就会触发"键已存在"的错误。
2. 循环变量修改导致逻辑混乱：在处理步兵、骑兵等模块的外层循环中，你直接修改了循环变量i = j，这可能导致某些行被重复遍历，生成重复的cell_str（如"C30"这类单元格地址），进而触发重复添加键的错误。

解决方案 #

1. 每次执行前清空全局字典和集合 #

在Workbook_Open开头添加重置代码，确保每次执行时字典和集合都是空的：

Private Sub Workbook_Open()
    ' 重置全局集合与字典
    Set leaders_col = New Collection
    Set cavalry_dic = New Scripting.Dictionary
    Set infantry_dic = New Scripting.Dictionary
    Set artillery_dic = New Scripting.Dictionary
    Set defenses_col = New Collection

    ' 原有代码...
End Sub

2. 添加键前检查是否已存在 #

在调用Add方法前，先判断字典中是否已有该键，避免重复添加：
以infantry_dic为例，替换原有的Add行：

If Not infantry_dic.Exists(cell_str) Then
    infantry_dic.Add cell_str, subCell_str
Else
    ' 可选：如果需要更新已有键对应的值，取消下面注释
    ' infantry_dic(cell_str) = subCell_str
End If

同理对cavalry_dic、artillery_dic做相同修改。

3. 避免修改外层循环变量 #

外层循环的i被内层循环修改（i = j）可能导致遍历逻辑混乱，建议改用Do循环或其他方式控制遍历位置，避免重复处理同一行。

修改后的完整代码示例 #

Option Explicit
Public leaders_col As New Collection
Public cavalry_dic As New Scripting.Dictionary
Public infantry_dic As New Scripting.Dictionary
Public artillery_dic As New Scripting.Dictionary
Public defenses_col As New Collection

Private Sub Workbook_Open()
    ' 重置全局集合与字典
    Set leaders_col = New Collection
    Set cavalry_dic = New Scripting.Dictionary
    Set infantry_dic = New Scripting.Dictionary
    Set artillery_dic = New Scripting.Dictionary
    Set defenses_col = New Collection

    Dim i As Integer
    Dim j As Integer
    Dim cell_str As String
    Dim subCell_str As String
    
    For i = 7 To 11
        cell_str = "C" & CStr(i)
        leaders_col.Add cell_str
    Next i
    For i = 17 To 26
        subCell_str = ""
        If IsEmpty(Range("F" & i)) Then
            cell_str = "C" & CStr(i)
            For j = i + 1 To i + 10
                If Not IsEmpty(Range("F" & j)) And IsEmpty(Range("B" & j)) Then
                    If j = i + 1 Then
                        subCell_str = "D" & CStr(j)
                    Else
                        subCell_str = subCell_str & ",D" & CStr(j)
                    End If
                Else
                    Exit For
                End If
                i = j
            Next j
        Else
            cell_str = "C" & CStr(i)
        End If        
        ' 检查骑兵字典键是否存在
        If Not cavalry_dic.Exists(cell_str) Then
            cavalry_dic.Add cell_str, subCell_str
        End If
    Next i
    For i = 30 To 53
        subCell_str = ""
        If IsEmpty(Range("F" & i)) Then
            cell_str = "C" & CStr(i)
            For j = i + 1 To i + 10
                If Not IsEmpty(Range("F" & j)) And IsEmpty(Range("B" & j)) Then
                    If j = i + 1 Then
                        subCell_str = "D" & CStr(j)
                    Else
                        subCell_str = subCell_str & ",D" & CStr(j)
                    End If
                Else
                    Exit For
                End If
                i = j
            Next j
        Else
            cell_str = "C" & CStr(i)
        End If      
        ' 检查步兵字典键是否存在
        If Not infantry_dic.Exists(cell_str) Then
            infantry_dic.Add cell_str, subCell_str
        End If
    Next i
    For i = 57 To 70
        subCell_str = ""
        If IsEmpty(Range("F" & i)) Then
            'Add the unit as a main unit
            cell_str = "C" & CStr(i)
            For j = i + 1 To i + 10
                If Not IsEmpty(Range("F" & j)) And IsEmpty(Range("B" & j)) Then
                    If j = i + 1 Then
                        subCell_str = "D" & CStr(j)
                    Else
                        subCell_str = subCell_str & ",D" & CStr(j)
                    End If
                Else
                    Exit For
                End If
                i = j
            Next j
        Else
            cell_str = "C" & CStr(i)
        End If      
        ' 检查炮兵字典键是否存在
        If Not artillery_dic.Exists(cell_str) Then
            artillery_dic.Add cell_str, subCell_str
        End If
    Next i
    For i = 76 To 80
        cell_str = "C" & CStr(i)
        defenses_col.Add cell_str
    Next i  
End Sub

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