Excel VBA字典写入单元格首次执行异常问题排查与解决

问题原因 #

这个问题的核心是晚期绑定创建Scripting.Dictionary时，首次枚举Keys集合存在COM对象初始化延迟，导致循环仅执行一次；第二次执行时COM对象已完成初始化，因此能正常遍历所有键值对。此外，Excel的单元格写入缓存机制也可能加剧首次执行的异常。

可行解决方案 #

方案1：批量写入（推荐，效率更高）

跳过遍历枚举，直接将字典的键和值转为数组批量写入工作表：

Sub escreverDicionario()
    Dim dict As Object
    Dim wsdash As Worksheet
    Dim linha_inicial As Long
    
    Set wsdash = ThisWorkbook.Sheets("dash")
    Set dict = CreateObject("Scripting.Dictionary")
    
    dict.Add "nome1", "valor1"
    dict.Add "nome2", "valor2"
    dict.Add "nome3", "valor3"
    
    linha_inicial = 1
    
    ' 批量写入键和值，避免枚举延迟问题
    If dict.Count > 0 Then
        wsdash.Cells(linha_inicial, 20).Resize(dict.Count, 1).Value = Application.Transpose(dict.Keys)
        wsdash.Cells(linha_inicial, 21).Resize(dict.Count, 1).Value = Application.Transpose(dict.Items)
    End If
End Sub

方案2：转数组后遍历

先将Keys集合转为本地数组，再循环数组，避开直接枚举COM对象集合的问题：

Sub escreverDicionario()
    Dim dict As Object
    Dim wsdash As Worksheet
    Dim linha_inicial As Long
    Dim chaves As Variant
    Dim i As Integer
    
    Set wsdash = ThisWorkbook.Sheets("dash")
    Set dict = CreateObject("Scripting.Dictionary")
    
    dict.Add "nome1", "valor1"
    dict.Add "nome2", "valor2"
    dict.Add "nome3", "valor3"
    
    linha_inicial = 1
    chaves = dict.Keys ' 将Keys转为本地数组
    
    For i = LBound(chaves) To UBound(chaves)
        wsdash.Cells(linha_inicial, 20).Value = chaves(i)
        wsdash.Cells(linha_inicial, 21).Value = dict(chaves(i))
        linha_inicial = linha_inicial + 1
    Next i
End Sub

方案3：使用早期绑定

通过引用Microsoft Scripting Runtime库，避免晚期绑定的初始化延迟：

1. 打开VBA编辑器，点击「工具」→「引用」，勾选Microsoft Scripting Runtime
2. 修改代码：

Sub escreverDicionario()
    Dim dict As New Dictionary ' 早期绑定声明
    Dim wsdash As Worksheet
    Dim linha_inicial As Long
    Dim chave As Variant
    
    Set wsdash = ThisWorkbook.Sheets("dash")
    
    dict.Add "nome1", "valor1"
    dict.Add "nome2", "valor2"
    dict.Add "nome3", "valor3"
    
    linha_inicial = 1
    
    For Each chave In dict.Keys
        wsdash.Cells(linha_inicial, 20).Value = chave
        wsdash.Cells(linha_inicial, 21).Value = dict(chave)
        linha_inicial = linha_inicial + 1
    Next chave
End Sub

内容的提问来源于stack exchange，提问作者João Paulo